Multiplying arrays with scalars and adding in VBA

Question

I have two columns of data in an excel sheet that are equal in size and both only contain numbers.

I'm trying to write a macros which will put both of these sets into arrays, then perform a calculation on them. Specifically ArrayA + 3*ArrayB then put the result back into the worksheet in a new column. Below is the code I have so far.

Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC As Variant

ArrayA = Worksheets("Sheet1").Range("A1:A5")
ArrayB = Worksheets("Sheet1").Range("B1:B5")

'this is where things go bad
ArrayC = ArrayA + 3 * ArrayB

can you post what the error message is? I'm also wondering if you need to do Worksheets("Sheet1").Range("A1:A5").Value to specify the value of the range — sheldonhull, Oct 19 '12 at 17:11
You cannot multiply arrays like that in VBA. Are you just looking to calculate C=A+3B for each pair of values in arrays A and B? If so then you'd need to loop through the arrays and perform that calculation. — Tim Williams, Oct 19 '12 at 17:28

score 3 · Answer 1 · edited Oct 19 '12 at 20:46

YOu need to first make an array of values. Then be sure that you'r elooping htrough the arrays.

Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC() As Variant

ArrayA = Worksheets("Sheet1").Range("A1:A5").Value
ArrayB = Worksheets("Sheet1").Range("B1:B5").Value

'this is where things go bad
'ArrayC = ArrayA + 3 * ArrayB

'manually make array C the same size as array A
ReDim ArrayC(LBound(ArrayA, 1) To UBound(ArrayA, 1), 1 To 1)

Dim i As Long 'variable to count which section of the array we are on

' loop through each spot int he array and perform the math
For i = LBound(ArrayA, 1) To UBound(ArrayA, 1)
    ArrayC(i, 1) = ArrayA(i, 1) + 3 * ArrayB(i, 1)
Next i

Worksheets("Sheet1").Range("C1:C5").Value = ArrayC

Note The above code is untested so I may have a typo in it somewhere. Also, you may need to declare ArrayC's data type instead of variant when using redim. I am unable to test at the moment as i'm answering from my mobile phone.

When you read a range into an array, it creates a two dimensional array even if the range has only one row or column. — Dick Kusleika, Oct 19 '12 at 20:47
Thanks for the edit, I only ever read in using 2 dimensions so I wasnt aware that 1 wouldn't work. — danielpiestrak, Oct 19 '12 at 21:30

Multiplying arrays with scalars and adding in VBA

1 Answers1

Linked