SumProduct basics

[MLT]

I'm trying to get started with User-Defined Functions. For the sake
of learning, I want to create a UDF that acts same as the built-in
SumProduct function. So, the two arguments would be two ranges,
range1 and range2. The first cell.value in range1 would be multiplied
with the first cell.value in range2 and so on - and then all added
together at the end. I thought I'd start by using a for loop to go
through each cell in range1 but now I'm not sure what is the best/most
efficient method to pair the values in range1 with their corresponding
values in range2. How should this be done?

 

[Dave Peterson]

First, this would work if the ranges passed are identical in size and each
consists of a single row (or column).

Maybe better: At least one dimension of each range has to be 1--you can't have
23 rows by 2 columns.

(This isn't a limitation for =sumproduct(), though.)

The first thing I would try is to determine the dimensions of the second passed
range.

If it's a vertical range, you could just loop through the first range and use
..offset(x,0) to process the second.

If it's horizontal, you could loop through the first and use .offset(0,x) for
the second.


So assuming the easiest scenario...

Option Explicit
Function mySP(rng1 As Range, rng2 As Range) As Variant

Dim Rng2IsVertical As Boolean
Dim myCell As Range
Dim myTotal As Double
Dim myOffset As Long
Dim Rng2Cell As Range

If rng1.Areas.Count > 1 _
Or rng2.Areas.Count > 1 Then
mySP = "Only single areas for each range!"
Exit Function
End If

If rng1.Columns.Count > 1 _
And rng1.Rows.Count > 1 Then
mySP = "First range is too big!"
Exit Function
End If

If rng2.Columns.Count > 1 _
And rng2.Rows.Count > 1 Then
mySP = "Second range is too big!"
Exit Function
End If

If rng1.Cells.Count = rng2.Cells.Count Then
'ok, keep going
Else
mySP = "Cell count doesn't match"
Exit Function
End If

If rng2.Rows.Count > 1 Then
Rng2IsVertical = True 'like A1:A10
Else
Rng2IsVertical = False 'like A1:K1
End If

myTotal = 0

myOffset = 0 'start in the first cell of rng2
For Each myCell In rng1.Cells
If Application.IsNumber(myCell.Value) Then
If Rng2IsVertical Then
Set Rng2Cell = rng2.Cells(1).Offset(myOffset, 0)
Else
Set Rng2Cell = rng2.Cells(1).Offset(0, myOffset)
End If

If Application.IsNumber(Rng2Cell.Value) Then
myTotal = myTotal + (myCell.Value * Rng2Cell.Value)
End If
'get ready for the next cell
myOffset = myOffset + 1
End If
Next myCell

mySP = myTotal

End Function

 

[MLT]

Great! Now how about this: I have a user-defined one-dimensional
range (say A1:A100). How do I ask for the value of the nth element in
that range?

 

[Dave Peterson]

=index(a1:a100,n)
in code:

dim myVal as variant
dim n as long
n = 66
myval = application.index(worksheets("Somesheetname").range("a1:a100"), n)