CODEWITHSUNDEEP
vbaexcel
smokysunday
smokysunday

Reputation: 13

Multiply two ranges/arrays cell by cell

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

Upvotes: 1

Views: 9119

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 3

pnuts
pnuts

Reputation: 59475

How you plan to use this in conjunction with other formulae may make a big difference but: 

 =A1:A4*B1:B4

entered as an array formula will return {1,0,0,2} - though not visible until say copied down. But the elements of the array may be accessed independently, say:

=INDEX(A1:A4*B1:B4,4)

(also an array formula) to return 2.

Upvotes: 1

Related Questions