VBA Macro Matrix Multiplication with a scalarClosed

Question

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.

venkat1926 · Accepted Answer

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"

venkat1926 · Answer

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

VBAConfused · Answer

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.

VBAConfused · Answer

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

VBA Macro Matrix Multiplication with a scalar

4 responses

Similar discussions

Subscribe To Our Newsletter!