Search : in
By :

VBA Macro Matrix Multiplication with a scalar

Last answer on Aug 16, 2009 9:28:30 pm BST VBAConfused, on Aug 6, 2009 3:54:37 pm BST 
 Report this message to moderators

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.

Configuration: Windows Vista
Firefox 3.0.13

Best answers for « VBA Macro Matrix Multiplication with a scalar » in :
[VBA] Deleting a word in a range of cell Show[VBA] Deleting a word in a range of cell In the case you want to delete a word in a sentence, just create a small macro that removes the word. But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or...
[VBA: VB6] Using excel from another application Show[VBA: VB6] Using excel from another application Here is a little routine to call Excel from VB6 or another Office application. Paste in a general module (eg Module1) In VBA>> Insert>> Module and paste in the window ... In VB6>>...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Download Super Macro ShowSuper macro is a free software which allows to create macro under Windows in order to activate diverse automatic actions. Apart being free, this software is easy to use and requires no knowledge in programming. You just simply click buttons, then...
Download Intel Matrix Storage Manager ShowThe driver Intel ® Matrix Storage Manager supplies a high capacity RAID support high and tolerates mistakes (RAID-5 and RAID-10) for ATA Serial disks of machines on base of chipsets Intel ® P965, 975X, 955X and 945. It also supplies the support...
Download Intel Matrix Storage Manager ShowThe driver Intel ® Matrix Storage Manager supplies a support RAID(TREK) of high-capacity and fault-tolerant (RAID-5 and RAID-10) for Serial ATA disks of machines on base of chipsets Intel ® P965, 975X, 955X and 945. It also supplies the support for...
Ask your question Reply

1

venkat1926, on Aug 7, 2009 2:29:45 am BST

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

   
Reply to venkat1926

2

VBAConfused, on Aug 9, 2009 7:40:15 pm BST

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.

   
Reply to VBAConfused

3

venkat1926, on Aug 10, 2009 2:09:47 am BST
  • +1

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"

   
Reply to venkat1926

4

 VBAConfused, on Aug 16, 2009 9:28:30 pm BST

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

   
Reply to VBAConfused
Ask your question Reply
They need your help