CODEWITHSUNDEEP
excelvba
user3582076
user3582076

Reputation: 1343

pass excel array constant to VBA function

I have a cell whose value is an array constant, for example cell A1 is set to ={1,2,3,4,5,6}

I then defined a function in VBA:

Function MyFunc(Data)
MyFunc = Data.Rows.Count
End Function

I want the function to return the length of the array (6) when I can =MyFunc(A1), but using the debugger, I find the 'Data' variable my function receives only contains the first element of this array. Data.Rows.Count evaluates to 1 and TypeName(Data) evaluates to 'Range'

Upvotes: 3

Views: 252

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

And another method:

Function myFunc(Data)
    myFunc = UBound(Evaluate(Data.Formula))
End Function

Upvotes: 5

YasserKhalil
YasserKhalil

Reputation: 9548

May be

Function MyFunc(data As Range)
Dim x

x = Split(Mid(data.Formula, 3), ",")
MyFunc = UBound(x) + 1
End Function

Upvotes: 3

BigBen
BigBen

Reputation: 50008

I'm not sure what your use case is, but I think you can just use Split here.

Function MyFunc(ByVal Data As Range) As Long
    Dim x
    x = Split(Data.Formula, ",")
    MyFunc = UBound(x) + 1
End Function

enter image description here

Obviously this can be made more robust (e.g. handle a multi-cell input), but I think it gets you going in the right direction.

Upvotes: 3

Related Questions