Array Formulas in VBA
 

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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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



"Bill Martin" wrote in message ...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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
--------------------------------------------------------------
"Jim Cone" wrote in message
...
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



"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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

"Bill Martin" wrote in message ...
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
--------------------------------------------------------------
"Jim Cone" wrote in message
...
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



"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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" wrote in message
...
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

"Bill Martin" wrote in message
...
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
--------------------------------------------------------------
"Jim Cone" wrote in message
...
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



"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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


"Bill Martin" wrote in message
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" wrote in message
...
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

"Bill Martin" wrote in message
...
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
--------------------------------------------------------------
"Jim Cone" wrote in message
...
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



"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

"=MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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 E3:P3 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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Bill Martin" wrote in message
...
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" wrote in message
...
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

"Bill Martin" wrote in message
...
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
--------------------------------------------------------------
"Jim Cone" wrote in message
...
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



"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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" wrote in message
...
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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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
----------------------------
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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

Bill

-----------
"Bill Martin" wrote in message
...
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
----------------------------
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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!E3:P3)]
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Bob Phillips" wrote in message
...
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" wrote in message
...
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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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
------------------------------------
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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
--------------------------
"Jim Cone" wrote in message
...
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


"Bill Martin" wrote in message
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" wrote in message
...
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

"Bill Martin" wrote in message
...
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
--------------------------------------------------------------
"Jim Cone" wrote in message
...
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



"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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

Array Formulas in VBA
 

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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Bill Martin" wrote in message
...
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
------------------------------------
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
---------------------------------

"Bob Phillips" wrote in message
...
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" wrote in message
...
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
------------------------------------

"Bob Phillips" wrote in message
...
Try evaluating it

Evaluate("MMULT(D6:D105,TRANSPOSE(Sheet2!E3:P3))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bill Martin" wrote in message
...
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(D6:D105,TRANSPOSE(Sheet2!E3:P3))"

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