CODEWITHSUNDEEP
excelvba
Fintz
Fintz

Reputation: 11

How to index the value in array?

Trying to debug. My best guess is the array indexing is a problem.

Public Function CFR(df(), x As Double, t As Integer) As Double
    Dim i, y As Integer
    Dim discount()
    Dim nom, denom As Double
    ReDim discount(t) As Double

    y = 1
    For i = UBound(df, 1) + 1 To t
        discount(i) = df(UBound(df, 1)) * (x ^ y)
        y = y + 1
    Next I

    nom = (1 - df(UBound(df)) * x ^ (t - UBound(df)))
    denom = Application.WorksheetFunction.Sum(df) + Application.WorksheetFunction.Sum(discount)
    CFR = nom / denim
End Function

Upvotes: 0

Views: 55

Answers (1)

HTH
HTH

Reputation: 2031

you should really use Option Explicit:

Option Explicit

Public Function CFR(df(), x As Double, t As Integer) As Double
    Dim i As Long, y As Integer
    Dim discount() As Double
    Dim nom, denom As Double
    ReDim discount(t) As Double

    y = 1
    For i = UBound(df, 1) + 1 To t
        discount(i) = df(UBound(df, 1)) * (x ^ y)
        y = y + 1
    Next i

    nom = (1 - df(UBound(df)) * x ^ (t - UBound(df)))
    denom = Application.WorksheetFunction.Sum(df) + Application.WorksheetFunction.Sum(discount)

    CFR = nom / denom

End Function

The issues were

1) denim instad of denom

which the use of Option Explicit would found you out immediatley

2) Dim discount() Since VBA assumes implicit Variant type for all not explicitly declared variables, and that makes it collidw with subsequent ReDim discount(t) As Double since the Redim() statement cannot change the type of the array

3) point 2 explanation is relevant for a minor issue (not blocking the code in this case):

Dim i, y As Integer

is actually read as:

Dim i As Variant, y As Integer

If you want i to be of integer type you have to code:

Dim i As Integer, y As Integer

Upvotes: 1

Related Questions