Excel VBA Dynamic Formula Arrays

I am trying to write an Excel function that returns a dynamic sized array. I wrote the three procedures below for test. If I run Cassie as a macro the dynamic array works. If I try to insert the function Call Cassie in to the sheet it does not work. Does anyone have an example of returning a dynamic sized array? I need to return a variable size array to user without him knowing how many cells to include into the formula array.

Sub Cassie()

On Error GoTo error1



Selection.FormulaArray = "=ArrayCassie()"

Exit Sub


MsgBox Error$

End Sub

Function CallCassie()

On Error GoTo error2

Application.Run ("Cassie")


CallCassie = "Cassie Completed"

Exit Function


MsgBox Error$

End Function

Function ArrayCassie()

Dim arr(1 To 5) As Variant

On Error GoTo error3

arr(1) = "A"

arr(2) = "B"

arr(3) = "C"

arr(4) = "D"

arr(5) = "E"

ArrayCassie = arr

Exit Function


MsgBox Error$

End Function

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!