# VBA Array Basics

#### tscbill5

##### Board Regular
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### WaxonWaxov

##### Board Regular
Should A be 3x3 as well?

#### tscbill5

##### Board Regular
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
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

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
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

Is this the problem?

Dim c(1 To 10000000) As Double

Try just

Dim c

#### tscbill5

##### Board Regular
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
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
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

Replies
3
Views
428
Replies
11
Views
386
Replies
2
Views
42
Replies
3
Views
183
Replies
4
Views
174

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,490
Messages
5,764,666
Members
425,227
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back