 Thanks:  0 Likes:  0

# Thread: VBA Array Basics

1. ## VBA Array Basics

Lets say I have this....

PHP Code:
``` Dim B as Rangeset B = activesheet.range("A1:C3")Dim a(3) As Doublea(1)= 1.2a(2)= 3.6a(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  Reply With Quote

2. ## Re: VBA Array Basics

Should A be 3x3 as well?  Reply With Quote

3. ## Re: VBA Array Basics

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  Reply With Quote

4. ## Re: VBA Array Basics

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```  Reply With Quote

5. ## Re: VBA Array Basics

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)```  Reply With Quote

6. ## Re: VBA Array Basics

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.

PHP Code:
``` Sub Arry_Code()Dim i As IntegerDim j As IntegerDim N As IntegerDim B As RangeSet B = ActiveSheet.Range("A8:C10")N = InputBox("Please enter an integer number of simulations", "Simulation Size")Dim a(1 To 3) As DoubleDim c(1 To 10000000) As DoubleDim d(1 To 10000000) As DoubleDim e(1 To 10000000) As DoubleDim f(1 To 10000000) As DoubleFor 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)    NextDim O As RangeSet 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  ```  Reply With Quote

7. ## Re: VBA Array Basics

Is this the problem?

Dim c(1 To 10000000) As Double

Try just

Dim c  Reply With Quote

8. ## Re: VBA Array Basics

Thanks that fixed that problem, but now the problem is with this part of the code:

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

9. ## Re: VBA Array Basics

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"

PHP Code:
``` Sub Arry_Code()Dim i As IntegerDim j As IntegerDim N As IntegerDim B As RangeSet B = ActiveSheet.Range("A8:C10")N = InputBox("Please enter an integer number of simulations", "Simulation Size")Dim a(1 To 3) As DoubleDim cDim gDim d(1 To 10000000) As DoubleDim e(1 To 10000000) As DoubleDim f(1 To 10000000) As DoubleFor 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)    NextDim O As RangeSet 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  ```  Reply With Quote

10. ## Re: VBA Array Basics

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  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•