How to write VBA user defined function to take array argument from IF with ctrl+shift+enter?

Question

I created a function called test

Function test(a() As Variant)
    Debug.Print "Type: "; TypeName(a)
    Debug.Print "lb: "; LBound(a)
    Debug.Print "ub: "; UBound(a)
    test = a(UBound(a))
End Function

It is okay when I type

=test({4,5,6,8,9})

in a cell. It returns 9.

Let's have some data on the worksheet.

I type the below formula with ctrl+shift+enter in a cell.

{=test(IF(A1:A5=1,B1:B5))}

I think an array is passed into test but it doesn't work. It can return LBound(a) and UBound(a) as usual but test = a(UBound(a)) fails.

What is the problem? How can I write a write VBA user defined function to take array argument from IF.

=test{(IF(A1:A5=1,B1:B5))} Try this

Stupid_Intern
– Stupid_Intern

2015-12-18 04:44:20 +00:00
Commented Dec 18, 2015 at 4:44 — Stupid_Intern
– Stupid_Intern, Commented Dec 18, 2015 at 4:44

chris neilsen · Accepted Answer · 2015-12-18 06:46:16Z

2

There is a fundimental difference between the two examples. =test({4,5,6,8,9}) passes a 1-D array, and {=test(IF(A1:A5=1,B1:B5))} passes a 2-D array. Your code is set up to process only 1-D arrays, so the second example fails.

How to fix this will depend entirely on what you actually want the UDF to do. For the example, You could coerce the input to 1-D using Transpose, but that's probably to limited for the general case. Might be better to test in parameter to determine its dimension and process accordingly

answered Dec 18, 2015 at 6:46

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

2 Comments

Timespace Over a year ago

Just a follow-up question, how can I know this is a 2-D array? and what index is used in this array?

chris neilsen Over a year ago

There are lots of good array functions on cpearson.com including finding the dimension of an array. To make it work for just 2-D try test = a(UBound(a,1))

Collectives™ on Stack Overflow

How to write VBA user defined function to take array argument from IF with ctrl+shift+enter?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related