pass excel array constant to VBA function

Question

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'

Data is not an array, but a Range object. Range.Value is an array only if the range is for more than a single cell. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 19, 2019 at 17:13

Ron Rosenfeld · Accepted Answer · 2019-06-19 18:42:35Z

5

And another method:

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

answered Jun 19, 2019 at 18:42

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BigBen · Accepted Answer · 2019-06-19 17:08:02Z

3

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

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.

answered Jun 19, 2019 at 17:08

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

2 Comments

Mathieu Guindon Over a year ago

I think that hard-coded comma needs to be Application.International(xlListSeparator).

BigBen Over a year ago

@MathieuGuindon I agree, add that to the list of things that "can be made more robust" :)

YasserKhalil · Accepted Answer · 2019-06-19 17:09:52Z

3

May be

Function MyFunc(data As Range)
Dim x

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

answered Jun 19, 2019 at 17:09

YasserKhalil

9,60813 gold badges62 silver badges135 bronze badges

1 Comment

Mathieu Guindon Over a year ago

I think that hard-coded comma needs to be Application.International(xlListSeparator).

Collectives™ on Stack Overflow

pass excel array constant to VBA function

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related