Re: 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" <jim.coneXXX@xxxxxxxxxx> wrote in message news:OIHDbAzXGHA.3448@xxxxxxxxxxxxxxxxxxxxxxx
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" <Wylie@xxxxxxxxxxxxx> wrote in message
The code is in a normal module, and I do have test data on the work*** 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" <jim.coneXXX@xxxxxxxxxx> wrote in message
news:%23GbFCRyXGHA.456@xxxxxxxxxxxxxxxxxxxxxxx
Bill,
Did you enter data in the two ranges on the active ***?
Is the code in a standard module not a module behind a ***?
Jim Cone

"Bill Martin" <Wylie@xxxxxxxxxxxxx> wrote in message
news:uJBjOJyXGHA.1200@xxxxxxxxxxxxxxxxxxxxxxx
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" <jim.coneXXX@xxxxxxxxxx> wrote in message
news:Ov8plsxXGHA.3448@xxxxxxxxxxxxxxxxxxxxxxx
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 work*** if desired
'ActiveCell.Resize(lngR, lngC).Value = varArray

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

'Get third row
varRow = Application.Index(varArray, 3, 0)
'Place on work*** 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" <Wylie@xxxxxxxxxxxxx> wrote in message
news:Oc3S$CxXGHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
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
spread*** 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
spread***.

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 work*** array functions though.
Thanks.
Bill




.

Loading