Array use in Function (Expected Array Compiler Error)

blairintern

New Member
Joined
Jul 16, 2007
Messages
21
I have a function in which I return an array--however, when i go to set the completed array from within the function ("original" in my example) i receive the "Compile Error: Expected Array"

The array IssueMaturities is not declared anywhere else other than the function statement.
The array "original" is not declared anywhere else other than the function statement.

Here is the code that results in the error.
Code:
Function IssueMaturities(original() As String) As String
    Dim index As Integer
    Dim tempValue As String
    index = 0
    Do
        original(index) = Format(original(index), "YYYY")
        tempValue = original(index) 'no messing with original
        If tempValue < Format(Now, "yyyy") Then 'if year has passed, then...
            tempValue = original(index + 1) ' set this value to the following
        End If
            original(index) = tempValue 'replace index value with next value
            index = index + 1 'increment index
    Loop Until index = 31
    Do
        tempValue = Format(original(index), "YYYY") ' no messing with original
        If tempValue = original(index - 1) Then ' if current value is equal to next
            ReDim Preserve original(0 To index - 1) 'then shrink array by 1
        End If
    Loop Until index >= UBound(original) ' continue until the (new) end has been reached
    ReDim IssueMaturities(LBound(original) To UBound(original))
    IssueMaturities() = original()
End Function
the ERROR occurs on the ReDim statement near the end of the function. I am attempting to resize the IssueMaturity array to reflect the size of the "original" array.

I'm really confused as to what I'm missing...
Thanks in advance!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
How is that function returning an array?

The return data type is String.:eek:
 
Upvote 0
Uhg. So the function statement should look like the following?

Code:
Function IssueMaturities(original() As String) As String()

In order to return an array of strings?
 
Upvote 0
I didn't really read "inside the function", but it should return an array
Code:
Function IssueMaturities(original() As String) As Variant
    Dim index As Integer
    Dim tempValue As String
    index = 0
    Do
        original(index) = Format(original(index), "YYYY")
        tempValue = original(index) 'no messing with original
        If tempValue < Format(Now, "yyyy") Then 'if year has passed, then...
            tempValue = original(index + 1) ' set this value to the following
        End If
            original(index) = tempValue 'replace index value with next value
            index = index + 1 'increment index
    Loop Until index = 31
    Do
        tempValue = Format(original(index), "YYYY") ' no messing with original
        If tempValue = original(index - 1) Then ' if current value is equal to next
            ReDim Preserve original(0 To index - 1) 'then shrink array by 1
        End If
    Loop Until index >= UBound(original) ' continue until the (new) end has been reached
    IssueMaturities = original()
End Function
 
Upvote 0
I run into the same problem. Declaring a function as variant is the only way I know of setting it equal to an array.

My solution is to use declare a dummy outputArray variable and set the funtion equal to it as the last line.

Code:
Function myArray(n As Integer) As Variant
Dim outputArray() As Long
    Rem some code
myArray = outputArray
End Function
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top