CODEWITHSUNDEEP
excelvba
Hyp0thesis
Hyp0thesis

Reputation: 48

Performing a Sumproduct without using the built-in function in Excel VBA

I've simplified my problem for the purposes of asking this question. Imagine I have the following data in Excel:

Cashflows | DiscountFactor

11,851 | 0.9901

96,401 | 0.9679

80,412 | 0.9494

I want to perform a SUMPRODUCT on these two columns in VBA but without using the built in SUMPRODUCT Function. This is because my project is going to get more complicated than that - but I just need to figure this out to begin with. My code so far:

Sub DiscountedCashflows()

Dim CashFlows(1 To 3) As Variant
Dim DiscountFactors(1 To 3) As Variant
Dim CashFlowsElms 'Elements in the cashflow array
Dim DiscountFactorElms 'Elements in the DiscountFactors array

Sheet1.Select

'Populating Cashflows array
Dim Counter1 As Long
For Counter1 = LBound(CashFlows) To UBound(CashFlows)
    CashFlows(Counter1) = Range("A1").Offset(Counter1, 0).Value
Next Counter1

'Populating DiscountFactors array
Dim Counter2 As Long
For Counter2 = LBound(DiscountFactors) To UBound(DiscountFactors)
    DiscountFactors(Counter2) = Range("B1").Offset(Counter2, 0).Value
Next Counter2

'Loop through the elements in the first array
For Each CashFlowsElms In CashFlows
    'Loop through the elements in the second array
    For Each DiscountFactorElms In DiscountFactors
        x = x + 1
        'Multiply the two array elements together
        Cells(x, 1) = CashFlowElms * DiscountFactorElms
    Next DiscountFactorElms
Next CashFlowsElms

MsgBox "Answer is..."

Erase CashFlows
Erase DiscountFactors

End Sub

How do I get the code to output the correct answer?

To give some context I'll be expanding this to work for dynamic arrays and I'll eventually turn it into a user defined function.

Any help is appreciated.

Upvotes: 0

Views: 511

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

The main problem is you only want to loop once and refer to the same row. To do that you would use a simple for loop and use the counter as the index of both arrays.

There is no reason to load the arrays with a loop, as you can assign the values directly.

Sub DiscountedCashflows()

Dim CashFlows() As Variant
Dim DiscountFactors() As Variant
Dim i As Long
Dim temp As Double
With Worksheets("Sheet16")
    'Populating Cashflows array
    CashFlows = .Range("A2:A4").Value
    'Populating DiscountFactors array
    DiscountFactors = .Range("B2:B4").Value

    'Loop through the elements in the first array
    For i = LBound(CashFlows, 1) To UBound(CashFlows, 1)
        temp = temp + (CashFlows(i, 1) * DiscountFactors(i, 1))
    Next i
End With

MsgBox "Answer is..." & temp


End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

Function MySumProduct() As Double
    Dim CashFlows As Variant
    Dim DiscountFactors As Variant

    With Sheet1
        CashFlows = Application.Transpose(.Range("A1", .Range("A1").End(xlDown)).Value)
        DiscountFactors = Application.Transpose(.Range("B1", .Range("B1").End(xlDown)).Value)
    End With

    For i = LBound(CashFlows) to UBound(CashFlows)
        MySumProduct= MySumProduct + CashFlow(i) * DiscountFactor(i)
    Next i
End Function

Upvotes: 0

Related Questions