VBA Array Basics

tscbill5

Board Regular
Joined
Apr 4, 2008
Messages
113
Lets say I have this....


Code:
Dim B as Range

set B = activesheet.range("A1:C3")


Dim a(3) As Double

a(1)= 1.2
a(2)= 3.6
a(3)= .27
Note: B is a 3x3 Matrix of values in your sheet

How would I perform matrix multiplication of B * array "a"?

My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.

Thanks
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

tscbill5

Board Regular
Joined
Apr 4, 2008
Messages
113
No. You can multiply a 3x3 Matrix by a 3x1 matrix and the result is another 3x1 matrix.

If A were also 3x3 then you would have 3x3 times 3x3 resulting in a 3x3.


I don't really need help with the linear algebra just using arrays in VBA
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Example:

Code:
Sub Test()
    Dim A(1 To 3) As Double
    Dim B As Range
    Dim C
    A(1) = 1.2
    A(2) = 3.6
    A(3) = 0.27
    Set B = Range("A1:C3")
    With Application.WorksheetFunction
        C = .MMult(B.Value, .Transpose(A))
    End With
    Range("E1:E3").Value = C
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,793
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Or you can declare a as a 3 row array:
Code:
Dim B As Range, var
Set B = ActiveSheet.Range("A1:C3")

Dim a(1 To 3, 1 To 1) As Double
a(1, 1) = 1.2
a(2, 1) = 3.6
a(3, 1) = 0.27
var = Application.WorksheetFunction.MMult(B, a)
 

tscbill5

Board Regular
Joined
Apr 4, 2008
Messages
113
Thank you guys. I am so close. Here is what I have....

The problem is an error I'm getting trying to set c = to the matrix multiplication.


Code:
Sub Arry_Code()
Dim i As Integer
Dim j As Integer
Dim N As Integer
Dim B As Range

Set B = ActiveSheet.Range("A8:C10")


N = InputBox("Please enter an integer number of simulations", "Simulation Size")

Dim a(1 To 3) As Double
Dim c(1 To 10000000) As Double
Dim d(1 To 10000000) As Double
Dim e(1 To 10000000) As Double
Dim f(1 To 10000000) As Double


For i = 1 To N

    For j = 1 To 3

        a(j) = (Rnd() - 0.5) * Sqr(12)

    Next
   
    With Application.WorksheetFunction
       c = .MMult(B.Value, .Transpose(a))
    End With

    d(i) = c(1)
    e(i) = c(2)
    f(i) = c(3)
    
Next

Dim O As Range

Set O = ActiveSheet.Range("E3:G5")

O(1, 1).Value = WorksheetFunction.Covar(d, d)
O(2, 1).Value = WorksheetFunction.Covar(e, d)
O(2, 2).Value = WorksheetFunction.Covar(e, e)
O(3, 1).Value = WorksheetFunction.Covar(f, d)
O(3, 2).Value = WorksheetFunction.Covar(f, e)
O(3, 3).Value = WorksheetFunction.Covar(f, f)
 
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is this the problem?

Dim c(1 To 10000000) As Double

Try just

Dim c
 

tscbill5

Board Regular
Joined
Apr 4, 2008
Messages
113
Thanks that fixed that problem, but now the problem is with this part of the code:


Code:
d(i) = c(1)
e(i) = c(2)
f(i) = c(3)
The error is "subscript out of range".
 

tscbill5

Board Regular
Joined
Apr 4, 2008
Messages
113
ok I fixed that. Here is what I have:

The problem is with the setting the cells equal to the covar at the very end. its a "type mismatch"

Code:
Sub Arry_Code()
Dim i As Integer
Dim j As Integer
Dim N As Integer
Dim B As Range

Set B = ActiveSheet.Range("A8:C10")


N = InputBox("Please enter an integer number of simulations", "Simulation Size")

Dim a(1 To 3) As Double
Dim c
Dim g
Dim d(1 To 10000000) As Double
Dim e(1 To 10000000) As Double
Dim f(1 To 10000000) As Double


For i = 1 To N

    For j = 1 To 3

        a(j) = (Rnd() - 0.5) * Sqr(12)

    Next
   
    With Application.WorksheetFunction
       c = .MMult(B.Value, .Transpose(a))
       g = .Transpose(c)
    End With

    d(i) = g(1)
    e(i) = g(2)
    f(i) = g(3)
    
Next

Dim O As Range

Set O = ActiveSheet.Range("E3:G5")

O(1, 1).Value = Application.WorksheetFunction.Covar(d, d)
O(2, 1).Value = Application.WorksheetFunction.Covar(e, d)
O(2, 2).Value = Application.WorksheetFunction.Covar(e, e)
O(3, 1).Value = Application.WorksheetFunction.Covar(f, d)
O(3, 2).Value = Application.WorksheetFunction.Covar(f, e)
O(3, 3).Value = Application.WorksheetFunction.Covar(f, f)
 
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,771
Hi tscbill5

Some remarks:

1 - you declare some variables as Integer. You dimension, however, the arrays with 1 to 10,000,000. The maximum value of an integer is 32K. You should change those variables to Long.

2 - I believe covar expects a 2D array. You can use the .Transpose to convert your arrays to 2D or declare them as 2D in the first place

3 - I don't think you can use a worksheet function from vba with arrays with more than 64K. I think you must write your CoVar function. In that case you can make it accept 1D arrays and you can leave the dimensioning of the arrays as they are now
 

Watch MrExcel Video

Forum statistics

Threads
1,095,147
Messages
5,442,650
Members
405,191
Latest member
wedloski

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top