Multiply two ranges/arrays cell by cell

Question

I'm trying to write an Excel UDF (User Defined Function) that takes two ranges and multiplies the first cell in range1 with the first cell in range2, second cell in range1 by second cell in range2, and so on, then stores the result in an array.

Imagine array1 in cells A1:A4 {1,0,1,2} and array2 in cells B1:B4 {1,1,0,1}. My function VECTORMULT(A1:A4, B1:B4) would return {1,0,0,2}.

Function VECTORMULT(array1 As Range, array2 As Range) As Variant
'takes 2 ranges and multiplies cell1 by cell1, cell2 by cell2, etc _
and stores it in a vector array

Dim Result() As Variant
Dim largerArray As Range
Dim smallerArray As Range
Dim i As Integer

'determine the smaller range to determine UBound in Result() array
If array1.Cells.Count >= array2.Cells.Count Then
    Set largerArray = array1
    Set smallerArray = array2
Else
    Set largerArray = array2
    Set smallerArray = array1
End If

ReDim Result(1 To smallerArray.Cells.Count)

'THIS IS THE PART THAT FAILS
For i = 1 To smallerArray.Cells.Count
    Result(i) = largerArray.Item(i).value * smallerArray.Item(i).value
Next i
VECTORMULT = Result
End Function

I had envisioned writing a more general function that accepted unlimited ParamArray Args() and parsed each Arg as an array, BUT I can't even solve this seemingly simple cell iterator function. I would think VBA could handle stepping through a range in some default manner like

Range(someRange).Item(i)

but it doesn't... For what it's worth, I DO seem to get correct values when I substitute correct Row/Column indeces for the Item function, like below; but then Result only works on 1 cell (instead of an array). I need to figure out how to pass "i".

'substitute Item(1,1) for Item(i) and it DOES work
For i = 1 To smallerArray.Cells.Count
    Result(i) = largerArray.Item(1,1).value * smallerArray.Item(1,1).value
Next i

Why do you say it fails? Does it error out? Or it just don't produce your expected result? BTW, it maybe a typo, but your function is VECTORMULTrng but you are assigning Result to VECTORMULT. — L42
Thank you, fixed! That was the result of trying different versions of the function. Fixing this in my own code also caused the function to return correct values for my last snippet of code, so this was fixed as well. The main problem (stepping through a range and returning an array) still stands. — smokysunday
To answer you first question - the function as presented in the main code snippet returns a #VALUE error. — smokysunday
So you are trying it in a Cell in your worksheet using it as formula? It works fine in my end, your first code. — L42
Correct. I plan to use it in conjunction with other formulas that take arrays as their argument. — smokysunday

Unknown Unknown · Accepted Answer · 2014-10-10T02:20:55

The INDEX function performs the task of delivering an array of modified information by blanking out the row_num and column_num parameters in its array form, e.g. INDEX((A1:A4)*(B1:B4),,). The size of the ranges has to match but should unless you are referencing named ranges that could dynamically change their shape. Examples (using your sample data):

=SUM(INDEX((A1:A4)*(B1:B4),,))        '◄ 3
=MIN(INDEX((A1:A4)*(B1:B4),,))        '◄ 0
=MAX(INDEX((A1:A4)*(B1:B4),,))        '◄ 2
=AVERAGE(INDEX((A1:A4)*(B1:B4),,))    '◄ 0.75

FWIW, I use this form of INDEX to provide many standard formulas when it seems that only an array formula would work. It is processed as an array but does not require Ctrl+Shift+Enter.

MINIF, MAXIF and MODEIF with Standard Formulas

For Excel 2010 and higher, look into the new AGGREGATE function for additional functionality.

Multiply two ranges/arrays cell by cell

2 Answers