Matrices in VBA / Excel

A

Antoine Cellerier

Hello,

I have something like:

Dim a As Range
Set a = (some range containing only doubles)

Dim b As Range
Set b = (some range the same size than a's)

b.value = a + a

When watching "a+a" i get a <Type mismatch> error. It works fine with
WorksheetFunctions like MInverse or MMult. I can't get the addition to work.

Any help would be greatly appreciated.

Many thanks,
 
D

Dave Peterson

If you really want to add the values in range A to the values in range B, how
about:

Select range A
edit|copy
select range B
edit|paste special|check Add

You could record a macro if you need the code.
 
A

Antoine Cellerier

Dave said:
You could record a macro if you need the code.
Click to expand...

The issue with the generated code is that it really is "range-centric".

I'm considering a formula which is:
rErWeights.Value = f.MMult(f.MInverse(f.MInverse(c) + f.MMult(rT,
f.MMult(f.MInverse(rViewsVariance), v))), f.MMult(f.MInverse(c), rPi) +
f.MMult(f.MMult(rT, f.MInverse(rViewsVariance)), v))

where f is Application.WorksheetFunction

Using the code generated by the macro recorder would need that i create
a temporary range on some other worksheet, which i'd rather avoid doing.
Composition of other functions like MMult and MInverse work great. It's
just this stupid '+' that doesn't want to behave correctly.
 
D

Dave Peterson

I was confused since you dimmed b as range and then used b.value.

But if you want to use an array, you could just loop through the elements:

Option Explicit
Sub testme()

Dim ValA As Variant
Dim ValB As Variant
Dim ValC As Variant

Dim iRow As Long
Dim iCol As Long

With ActiveSheet
ValA = .Range("a1:d12").Value
ValB = .Range("f1:i12").Value
ReDim ValC(1 To UBound(ValA, 1), 1 To UBound(ValA, 2))

For iRow = 1 To UBound(ValA, 1)
For iCol = 1 To UBound(ValA, 2)
ValC(iRow, iCol) = ValA(iRow, iCol) + ValB(iRow, iCol)
Next iCol
Next iRow
End With

End Sub
 
A

Antoine Cellerier

Thanks. That works great.

In case anyone cares, i used:

' Can't get '+' to work on matrices/arrays in VBA code. So here comes
the homemade function
Function MAdd(A As Variant, B As Variant) As Variant
Dim C As Variant
ReDim C(1 To UBound(A, 1), 1 To UBound(A, 2))
Dim i As Integer
Dim j As Integer
For i = 1 To UBound(A, 1)
For j = 1 To UBound(A, 2)
C(i, j) = A(i, j) + B(i, j)
Next j
Next i
MAdd = C
End Function
 
D

Dana DeLouis

Not sure if you would find any ideas here helpful, so I'll just throw this
out...

Sub Demo()
Dim v, Array1, Array2

Array1 = [A1:B12]
Array2 = [D1:E12]

v = MAdd(Array1, Array2)
[G1].Resize(12, 2) = v
End Sub


Function MAdd(v1, v2) As Variant
ActiveWorkbook.Names.Add "v1_", v1
ActiveWorkbook.Names.Add "v2_", v2

MAdd = [v1_+v2_]

'// My Standard internal names, or just discard...
ActiveWorkbook.Names("v1_").Delete
ActiveWorkbook.Names("v2_").Delete
End Function

HTH. :>)
 
Top