VBA Macro Matrix Multiplication with a scalar

Closed
Report
-
 MANMANI -
Hello,

I've been struggling with what should be a simple operation using Excel VBA and was hoping someone could please help me out. Basically I have 2 ranges declared as:

Dim matrixR As Range
Dim matrixW As Range

and initiated as:

Set matrixR = Range(Range("A9").End(xlDown), Range("A9").End(xlToRight))
Set matrixW = Range(Range("A9").End(xlDown).Offset(4, 0), Range("A9").End(xlToRight).End(xlDown).Offset(3 + NumConcepts, 0))

The size of the ranges depends on how many concepts the user inputs (i.e. 5 nodes = 5X5 matrix).

MatrixR is populated whereas matrixW is the one I want to fill. I'm trying to multiply every value in matrixR with a constant and set it equal to matrixW.

matrixW.Value = matrixR.value * 0.9

gives me an error. I have also tried various loops and defining a FormulaArray to no avail.

I'd really appreciate any help.

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
Even though I have done matric multiplication manually during my young days as a student of Mathematics it is long ago I have forgotten, If I remember correctly if you have matrix(trivial example) as below :
In excel take it as A1,B1, A2 ,B2.
A B
2 3
4 5

Now you want to make a scalar multiplication of a matrix for e.g by 6 it is
Is it
12 18
24 30
Or is it sum of these four figures(sorry I have forgotten)

I am only loudly thinking
Why not try this macro

Sub test()
Dim rng As Range, c As Range
Dim scalar As Double
scalar = 0.9
Set rng = Range(Range("a1"), Range("a1").End(xlDown).End(xlToRight))
For Each c In rng
c.Value = c * scalar
Next c

End Sub

If you want total you ca use this macro
Sub test1()
Dim rng As Range, c As Range
Dim scalar As Double, total As Double

scalar = 0.9
Set rng = Range(Range("a1"), Range("a1").End(xlDown).End(xlToRight))
For Each c In rng
c.Value = c * scalar

Next c
total = WorksheetFunction.Sum(rng)
MsgBox total
End Sub

Will this of any use? Probably not!

if it is multiplication of matrix over matrix there is a function in excel 2007 caled
MMULT
This formula is to be invoked by control+shift+enter see help under "matrix"
4
Thanks for the reply, sorry I forgot to mention I am using the office 2007 version of excel. The link you have provided is useful. However, it does not really apply to my problem as I need to implement multiplication of a matrix with a scalar using VB.

In my workbook, the user enters a number depending on the available data for the NonLinear hebbian learning algorithm. So if the user enters 20, the matrix created is 20x20. The user then inputs values using predefined drop down menus.

One step of the computation method requires me to multiply the whole matrix by a scalar (0.9) . So as you can see, because the size of the matrix is dynamic, I cannot just define a formula like: = 0.9*(A20:C20) because the size of the matrix changes from case to case.

Right now what I have down is using Range().End(xlDown) & .End(xlRight), I activate the range and set it as matrixR.

however, VB does not allow me to do something like matrixW = matrixR * 0.9 OR matrixW.Value = matrixR.Value * 0.9 OR matrixW.FormulaArray = matrixR * 0.9

The only solution I came up with was to create a new sheet, create a new matrixC by multiplying matrixR with 0.9 using a for each loop and then setting matrixW.Value = matrixC.Value, and then delete the new sheet. However, this doesn't seem very efficient and I was hoping there was some way I could do the direct multiplication without the creation of an interim matrix.

Perhaps I am missing something, my knowledge of Excel and Visual Basic is quite basic.
2
Thanks for the replies venkat. I ended up figuring out a difference method to solve the problem.

Moderators feel free to delete or close this thread.

Thanks
1
What was your solution? I'm having a similar problem and convinced there must be a neater way than looping through all the cells in range, or is this a very efficient way to do it (i have screen updating switched off) it's just the code looks like a lot.
0
scalar*(array) then shift control enter. make sure u select the rigth number of cells corresponding to your matrix. example of an array is A3:C3
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
It would be helpful if member give the vesion of office they are hving
In excel 2007 it is easier.
see this webpage

http://zorak.monmouth.edu/~dwacha/MatrixOperations.Excel2007.pdf
0