CODEWITHSUNDEEP
arraysvbaexcel-2010
Fabien TheSolution
Fabien TheSolution

Reputation: 5050

Converting a VBA array into a "column" array

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 ?

enter image description here

Upvotes: 0

Views: 674

Answers (2)

Slai
Slai

Reputation: 22896

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

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Use Tranpose to switch it:

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

Upvotes: 2

Related Questions