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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
How is that function returning an array?

The return data type is String.:eek:
 

blairintern

New Member
Joined
Jul 16, 2007
Messages
21
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?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,316
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

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
Top