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

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

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

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

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

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

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

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

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

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

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

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