Array Formulas in VBA

[Bill Martin]

Is there some way to use an array formula within VBA? For example, I find
that I can use a MMULT array formula in VBA with the result posted to a
spreadsheet by using:

Range("AD6:AD105").Select
Selection.FormulaArray = "=MMULT(D6105,TRANSPOSE(Sheet2!E33))"

But it would be much better for me if I could put the resulting vector
directly into a VBA array for use rather than back out onto the spreadsheet.

I can do this with scalar functions that take an array input and produce a
single result such as:

dim Vector(10) as single
... fill the vector ...
X = WorksheetFunction.Max(Vector)

I haven't been able to figure out how to do the analogous thing with built
in worksheet array functions though.

Thanks.

Bill

 

[Bob Phillips]

Try evaluating it

Evaluate("MMULT(D6105,TRANSPOSE(Sheet2!E33))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jim Cone]

Bill,
Maybe you can use some of this...
Note that each variant contains an array.
'---
Sub MatrixNumbers()
Dim varArray As Variant
Dim varCol As Variant
Dim varRow As Variant
Dim lngC As Long
Dim lngR As Long

varArray = Application.MMult(Range("B5:B9"), Range("C5:E5"))
lngC = UBound(varArray, 2)
lngR = UBound(varArray, 1)

'Place on worksheet if desired
'ActiveCell.Resize(lngR, lngC).Value = varArray

'Get second column
varCol = Application.Index(varArray, 0, 2)
'Place on worksheet if desired
'ActiveCell.Resize(lngR).Value = varCol

'Get third row
varRow = Application.Index(varArray, 3, 0)
'Place on worksheet if desired
'ActiveCell.Offset(0, 1).Resize(, lngC).Value = varRow
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Is there some way to use an array formula within VBA? For example, I find
that I can use a MMULT array formula in VBA with the result posted to a
spreadsheet by using:

Range("AD6:AD105").Select
Selection.FormulaArray = "=MMULT(D6105,TRANSPOSE(Sheet2!E33))"

But it would be much better for me if I could put the resulting vector
directly into a VBA array for use rather than back out onto the spreadsheet.

I can do this with scalar functions that take an array input and produce a
single result such as:

dim Vector(10) as single
... fill the vector ...
X = WorksheetFunction.Max(Vector)

I haven't been able to figure out how to do the analogous thing with built
in worksheet array functions though.
Thanks.
Bill

 

[Bill Martin]

I can't get your example to run Jim. When I copy/paste it directly into a
module and try to execute it, the code compiles properly, but execution will
stop with a "Type Mismatch" error at the line:

lngC = UBOUND(varArray,2)

I tried touching various things, but any time the varArray is in the right
hand of a formula I get the same error.

Bill

 

[Bill Martin]

Pardon my denseness, but I don't see where the result of the MMULT operation
ends up. Sticking your code into a toy test module it will compile and
execute without complaint, but I don't see where to find the result.

I tried using:

dim Matrix(100,100)
Matrix = Evaluate(....)

.... but that fails with an error message "Can't assign to array". Where do
I find the result using your method?

Thanks.

Bill
------------------------------------

 

[Jim Cone]

Bill,
Did you enter data in the two ranges on the active sheet?
Is the code in a standard module not a module behind a sheet?
Jim Cone

I can't get your example to run Jim. When I copy/paste it directly into a
module and try to execute it, the code compiles properly, but execution will
stop with a "Type Mismatch" error at the line:

lngC = UBOUND(varArray,2)

I tried touching various things, but any time the varArray is in the right
hand of a formula I get the same error.

Bill

 

[Bob Phillips]

Don't dimension the array, let evaluate do it dynamically

dim Matrix
Matrix = Evaluate(....)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bill Martin]

The code is in a normal module, and I do have test data on the worksheet set
up as a small toy problem.

On the theory that perhaps the MMULT usage was too complicated to debug
with, I commented it out and tried just manually inserting a few values into
the array:

varArray(1, 1) = 1
varArray(1, 2) = 2
varArray(1, 3) = 3
varArray(2, 3) = 6

This doesn't work either, and gives a "Type Mismatch" error when it hits the
first assignment statement. I've never used dynamically assigned arrays in
VBA, and I guess I don't understand. I've also tried removing from the
module the compiler control statements:

Option Explicit
Option Base 1

but it doesn't seem to make any difference.

Bill
----------------------

 

[Jim Cone]

Bill,

It works for me.
If I don't enter data in ranges "B5:B9" and "C5:E5" then I get the
Type Mismatch error that you described.

When using a Variant as an array, you have to declare the size
of the array before using it... ReDim varArray(1 to 5, 1 to 3)
Then you can assign values to it.

Out of ideas here.
Jim Cone


The code is in a normal module, and I do have test data on the worksheet set
up as a small toy problem.
On the theory that perhaps the MMULT usage was too complicated to debug
with, I commented it out and tried just manually inserting a few values into
the array:

varArray(1, 1) = 1
varArray(1, 2) = 2
varArray(1, 3) = 3
varArray(2, 3) = 6

This doesn't work either, and gives a "Type Mismatch" error when it hits the
first assignment statement. I've never used dynamically assigned arrays in
VBA, and I guess I don't understand. I've also tried removing from the
module the compiler control statements:
Option Explicit
Option Base 1
but it doesn't seem to make any difference.
Bill

 

[Bill Martin]

I've never used dynamic arrays in VBA and I guess I'm overlooking something
simple. When I do it as you've described it compiles and runs without
error. But I have no way to look at the result that's presumably in the
Matrix(..) array. Whatever I do to look at the result gives me a "Type
mismatch" error. I can't even ask it what size the array is with UBound or
I get the same error.

So I went to Walkenbach's book to look up dynamic arrays. He seems to say
one has to ReDim the array to put actual bounds on it before you can use it.
So blundering ahead, after the EVALUATE statement I tried:

ReDim Preserve Matrix(10,10)

but that also just gives a "Type Mismatch" error. I'm missing something
elementary here...

Bill
---------------------------------

 

[Dana DeLouis]

"=MMULT(D6105,TRANSPOSE(Sheet2!E33))"

Hi. I may be wrong, but from help on MMult...
'The number of columns in array1 must be the same as the number of rows in
array2...

The number of columns in the first array is '1.
However, when E33 is Transposed, the number of rows is the second array
are more than '1.

Not sure, but this smaller example works fine for me:

Sub Demo()
Dim v1, v2, Arr

v1 = [A1:B2]
v2 = [D1:E2]
Arr = WorksheetFunction.MMult(v1, v2)
End Sub

 

[Bob Phillips]

Bill,

I used this and it worked

Dim xyz

xyz = Evaluate("MMULT(A13:A15,A13:C13)")
Debug.Print LBound(xyz), xyz(1, 3)


Note Dana's comments about array sizes.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bill Martin]

Apparently while you were typing I was also thinking along the same lines.
I find that if I run the following code:

dim Matrix
Matrix = Evaluate("MMult(A5:C10,transpose(A1:C1))")
Range("A30:A35") = Matrix
I = UBound(varArray, 1)
MsgBox I

What happens is that the program does indeed put the proper vector result
out onto the spreadsheet. However I still can't touch the data from within
VBA. The UBound statement fails with the "Type Mismatch" error. Anything I
can think of to reach into Matrix to look at an element fails. Yet
apparently the correct result is in there because it puts it out onto the
sheet if I ask it to.

Bill
----------------------------

 

[Bill Martin]

Ignore my previous post. I'm getting closer and will come back "soon" (?)
with an answer or question...

Bill

-----------

 

[Dana DeLouis]

Hi Bob. If the Op doesn't Transpose the second array, then it works ok for
me also.

Sub Demo()
Dim v
v = [MMULT(Sheet1!AD6:AD105,Sheet2!E33)]
End Sub

 

[Bill Martin]

Ok -- I can make it work. There's a nasty quirk to it though that I don't
understand which of course is why I had so much trouble with it. By random
chance, the problem that I was working was to multiply a rectangular matrix
by a vector, producing a vector result with 6 elements. (And I did have the
dimensions and transposing and all correct, though thanks for suggesting it
Dana.)

The thing is that after the Matrix = Evaluate(...) function I would go in
and look for an element of the six element vector that should have been
generated. For example I would look for the third element as Matrix(3) and
would get the error.

For reasons I don't quite understand though, I noticed in the Locals debug
window that Matrix(3) for example could be expanded and it truly is named
Matrix (3,1). The one dimensional result vector is sitting in a two
dimensional array, but with only the first dimension used -- if you can
follow that explanation.

Anyhow, if I address the result vector as a two dimensional array, with the
second dimension always =1 then I'm in high clover. Now hopefully I can get
this approach to work in my complex and wonderful problem as well as it does
in my toy example.

To complete the experiment I also changed my arrays around so it would
produce a 2x6 array as an answer. In that case VBA generated a 2x6 array
for an answer just as one would hope. Only the 1 dimensional vector seems
to produce the weird case with an extra dimension.

Thanks folks.

Bill
------------------------------------

 

[Bill Martin]

I finally figured it out Jim. There's a "feature" in VBA (or only in MMULT
in particular?) that screws up the one particular case I happened to be
using as an example. I posted the gory details in the other half of this
thread time stamped 4:53pm if you're interested.

Thanks for your assistance.

Bill
--------------------------

 

[Dana DeLouis]

Hi. If you don't want a single array with two dimensions, perhaps a
workaround...

Sub Demo()
Dim v

'// A 3 x 1 Matrix...
v = [MMULT(A1:C3,E1:E3)]

'// A 3 Element vector...
v = WorksheetFunction.Transpose(v)

'// Or perhaps all at once...
v = [Transpose(MMULT(A1:C3,E1:E3))]
End Sub