Converting a VBA array into a "column" array

Question

As I'm now aware, array in Excel have two kinds of representation :

{1,2,3,4} also known as "one-dimensional horizontal array"

and

{1;2;3;4} also known as "one-dimensional vertical array"

I created a VBA function that return an array but when I use it in a function that need arrays (SUMPRODUCT), Excel display it with commas. So it always interpret it as an horizontal array. It's fine if I want to have the product with another horizontal array but it doesn't work when I try with a vertical array.

VBA :

Function MyRange()
    Dim output(2)
    output(0) = 1
    output(1) = 1
    output(2) = 1
    MyRange = output
End Function

Excel

=SUMPRODUCT(MyRange();{1,1,1}) works
=SUMPRODUCT(MyRange();{1;1;1}) is not working

My question ?

How I can have Excel to show {1;1;1} instead of {1,1,1} in the 1st array ?

@ScottCraner If you want to create an answer. I will accept it. Many thanks ! — Fabien TheSolution
– Fabien TheSolution, Commented Jan 4, 2017 at 21:32
or maybe INDEX({1;1;1},0,1) or MyRange = Application.Transpose(output) in VBA — Slai
– Slai, Commented Jan 4, 2017 at 21:57
@Slai Nice catch with TRANSPOSE directly in the VBA. It works ! — Fabien TheSolution
– Fabien TheSolution, Commented Jan 4, 2017 at 22:57

Scott Craner · Accepted Answer · 2017-01-04 21:43:39Z

2

Use Tranpose to switch it:

=SUMPRODUCT(MyRange();TRANSPOSE({1;1;1}))

edited Jan 4, 2017 at 21:43

answered Jan 4, 2017 at 21:34

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

4 Comments

Fabien TheSolution Over a year ago

Really weird. It looks perfectly in Function Wizard but Excel still return #VALUE! when I click OK !

Fabien TheSolution Over a year ago

I put the TRANSPOSE on the other part =SUMPRODUCT(MyRange();TRANSPOSE({1;1;1})) and it's working now ! Many thanks !

Fabien TheSolution Over a year ago

Finally, and unfortunately for me, TRANSPOSE seems to work only on "hardcoded" array. If I use another EXCEL function instead like TRANSPOSE(--(WEEKDAY(D3:D1257,3)<5)) or --(TRANSPOSE(WEEKDAY(D3:D1257,3))<5), the wizard show it correctly but not in the cell :(

Fabien TheSolution Over a year ago

I follow @Slai suggestion and putting TRANSPOSE directly in the VBA do the trick :) TRANSPOSE was the right direction. Thanks !

Slai · Accepted Answer · 2017-01-05 00:48:24Z

1

Another a bit faster option is to use a "vertical" array (not tested):

Function MyRange()
    Dim output(1 To 3, 1 To 1)
    output(1, 1) = 1
    output(2, 1) = 2
    output(3, 1) = 3
    MyRange = output
End Function

or a bit slower (also not tested):

Function MyRange()
    MyRange = [{1;2;1}]
    MyRange(3, 1) = 3
End Function

answered Jan 5, 2017 at 0:48

Slai

23k5 gold badges49 silver badges55 bronze badges

Collectives™ on Stack Overflow

Converting a VBA array into a "column" array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related