Excel VBA Dynamic Formula Arrays - Programmers Heaven

Howdy, Stranger!

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

Categories

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

ActiveSheet.Range("A1:A5").Select

ActiveSheet.Range("a1:a5").Activate

Selection.FormulaArray = "=ArrayCassie()"

Exit Sub

error1:

MsgBox Error$

End Sub



Function CallCassie()

On Error GoTo error2

Application.Run ("Cassie")

Range("a1:a5").Calculate

CallCassie = "Cassie Completed"

Exit Function

error2:

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

error3:

MsgBox Error$

End Function




Sign In or Register to comment.