Array function in Excel VBA

Question

I have a function in VBA that generates an array of strings. It works fine when called from another VBA function, but not when called from the worksheet.

Here's how it should be used:

select A1:A3
write in the formula bar =Test(), then hit Ctrl-Shift-Enter to make it an array function
A1 should contain A, A2 should contain B, and A3 should contain C

When I actually try this, it puts A in all three cells of the array. How can I get the data returned by Test into the different cells of the array?

For those who'd like to see it, here's the code of the function. Keep in mind, the function works fine when called from other functions.

Function Test() As String()
    Dim a(1 To 3) As String
    a(1) = "A"
    a(2) = "B"
    a(3) = "C"
    Test = a
End Function

Mike T · Accepted Answer · 2012-04-24 01:57:01Z

9

You function works fine if you transpose the data. To do this, you need Variant() instead of String():

Function Test() As Variant()
    Dim a(1 To 3) As Variant
    a(1) = "A"
    a(2) = "B"
    a(3) = "C"
    Test = Application.Transpose(a)
End Function

answered Apr 24, 2012 at 1:57

Mike T

44.3k18 gold badges166 silver badges213 bronze badges

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

4 Comments

Joe Over a year ago

I had no idea that the default direction of an array was to the right, rather than down.

Mike T Over a year ago

Unfortunatelly, there doesn't appear to be a simple automatic way for Excel to fill the array either horizontal or vertical. There could be some tricks out there, however.

Mike T Over a year ago

Yup, as @andy holaday points out, Chip Pearson has also figured out the horizontal/vertical dilemma.

brettdj Over a year ago

Rather than use Transpose in the UDF you could use Transpose in you array formula when you want a vertical entry set, or not if you want it horizintal

Community · Accepted Answer · 2018-05-08 21:48:18Z

4

You might want to check Chip Pearson's advice on returning an array from a UDF.

This is my first response here. I hope this is not inappropriate.

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Apr 24, 2012 at 2:00

andy holaday

2,3021 gold badge17 silver badges25 bronze badges

1 Comment

Doug Glancy Over a year ago

It's never wrong to refer to Chip Pearson's site. At least I hope not, as I do it all the time! +1

Charles Williams · Accepted Answer · 2012-04-24 07:33:01Z

3

I find it simplest to always work with 2-dimensional arrays, then you don't need transpose etc

Function Test() As String()
    Dim a(1 To 3, 1) As String
    a(1, 1) = "A"
    a(2, 1) = "B"
    a(3, 1) = "C"
    Test = a
End Function

answered Apr 24, 2012 at 7:33

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Comments

Joe · Accepted Answer · 2012-04-24 01:57:00Z

0

Just figured out the problem -- the first dimension of an array going from VBA to Excel is horizontal, not vertical. To see the output of Test, put the array function =Test() in A1:C1 (not A1:A3).

answered Apr 24, 2012 at 1:57

Joe

3,8447 gold badges38 silver badges55 bronze badges

1 Comment

Doug Jenkins Over a year ago

Reading this reply in combination with the one from Charles Williams above is a bit confusing, although both are correct. A 1D VBA array is passed to Excel as a single row (as seen in the original Test UDF). A 2D VBA array is passed with the first dimension in a column (as seen in the Test UDF posted by Charles).

Collectives™ on Stack Overflow

Array function in Excel VBA

4 Answers 4

4 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related