Arrays in VBA -- PV cash flows

[jdmc45]

Hi all,

I have an issue with creating a simple function in VBA. The help isn't of much use. I know I am simply missing some key concept in VBA which I'm sure is easy.

I am trying to create a function to calculate the present value of future cash flows, simply CF(t)/(1+r)^(t) for all t. The arrays are the issue. I have many more problems in this format (with vectors and matrices) so if I figure out this one it will carry forward to the others.

Please look at the code below and see if you can help me. Thanks.

Function PV_discrete(Dim CFtimes(1 To n) As Integer, Dim CFamounts(1, X) As Integer, Dim r As Double)
Dim t() As Integer
PV_discrete = 0
For t = 0 To Length(CFamounts(1, X))
PV_discrete = CFamounts(t) / (1 + r) ^ (CFtimes(t))
Next t
PV_discrete
End Function

 

[xenou]

Hi,
You don't put Dim statements inside function argument declarations. I haven't even thought about your actual algorithm here for the function, but just looking at the syntax there's a problem. You pass the variables only.

Try:

Sub TestPV_discrete()
Dim CFTimes() As Integer
Dim CFAmounts(1,1) As Integer
Dim r As Double
Dim x As Double

    CFTimes = Array(1,2,3)
    CFAmounts = (1,100)
    r = .1
    x = PV_Discrete(CFTimes, CFAmounts, r)

End Sub
'------------------------------------------------------------------------------------------
Function PV_discrete(CFtimes() As Integer, CFamounts() As Integer, Dim r As Double)
Dim t() As Integer
    PV_discrete = 0
    For t = 0 To Length(CFamounts(1, X))
        PV_discrete = CFamounts(t) / (1 + r) ^ (CFtimes(t))
    Next t
End Function

I don't mean to offend but you need some basic introduction to VB as you seem to be just guessing here (maybe you come from a programming language that has different roots such as smalltalk or scheme or javascript). Get a basic intro to VB or VBA. For Excel (particularly) you could probably read Excel VBA Programming for Dummies in a day or two (by Walkenbach -- no dummy). Otherwise, any intro to VB or VB.Net will get you up to speed.

ξ

 

[jdmc45]

Hi,
You don't put Dim statements inside function argument declarations. I haven't even thought about your actual algorithm here for the function, but just looking at the syntax there's a problem. You pass the variables only.

Try:

Sub TestPV_discrete()
Dim CFTimes() As Integer
Dim CFAmounts(1,1) As Integer
Dim r As Double
Dim x As Double
 
    CFTimes = Array(1,2,3)
    CFAmounts = (1,100)
    r = .1
    x = PV_Discrete(CFTimes, CFAmounts, r)
 
End Sub
'------------------------------------------------------------------------------------------
Function PV_discrete(CFtimes() As Integer, CFamounts() As Integer, Dim r As Double)
Dim t() As Integer
    PV_discrete = 0
    For t = 0 To Length(CFamounts(1, X))
        PV_discrete = CFamounts(t) / (1 + r) ^ (CFtimes(t))
    Next t
End Function

I don't mean to offend but you need some basic introduction to VB as you seem to be just guessing here (maybe you come from a programming language that has different roots such as smalltalk or scheme or javascript). Get a basic intro to VB or VBA. For Excel (particularly) you could probably read Excel VBA Programming for Dummies in a day or two (by Walkenbach -- no dummy). Otherwise, any intro to VB or VB.Net will get you up to speed.

ξ

Yeh no problem. I have basic VBA experience but arrays are more tricky. I am reading something on them now, just want to be pointed in the right direction.
I ask because if I can get this example out I can get further problems out. That code doesn't make sense, array for cashflow times and amounts need to be same length. to input the data would I go something like:

CFAmounts = Worksheets("Inputs").Range("B1:B10")

instead of specifying every single value of the array

 

[xenou]

It's a bit odd at first but you can get values from a worksheet in an array in one shot using Variants:

Dim myArray As Variant
myArray = Worksheet("Sheet1").Range("B1:B10")[COLOR=Blue].Value[/COLOR] '//an array of values

This is very different from:

Dim myArray As Variant
myArray = Worksheet("Sheet1").Range("B1:B10") '//a range object

However, all arrays populated this way from worksheets are 1-based and two-dimensional (rows/columns).

Take it one step at a time.

 

[shg]

Xenou, those both work the same. You'd need Set for an object.

 

[xenou]

Yes, thanks!

Set myArray = ...

Returns a range object.

 

[jdmc45]

I think I'm closer. I receive a subscript out of range error on the function calculation

Sub TestPV_discrete()
    
    Dim CFtimes As Variant
    Dim CFamounts As Variant
    Dim r As Double
    Dim x As Double
    CFtimes = Worksheets("Bond").Range("A2:A101")
    CFamounts = Worksheets("Bond").Range("B2:B101")
    r = Worksheets("Bond").Range("C2")
    
    x = PV_discrete(CFtimes, CFamounts, r)
    
    MsgBox ("Answer is " & "x")
    
End Sub
Function PV_discrete(CFtimes As Variant, CFamounts As Variant, r As Double) As Double
    Dim t As Integer
    Dim FinalTime As Variant
    PV_discrete = 0
    FinalTime = Cells(Rows.Count, 2).End(xlUp).Row
    For t = 0 To FinalTime
        PV_discrete = CFamounts(t) / (1 + r) ^ (CFtimes(t))
    Next t
End Function

Can someone take a quick look? Cheers

 

[xenou]

FinalTime is 0 to 101
The arrays have 100 elements (I think).

Probably better to use the ubound() function:

For t = LBound(CFTimes) To Ubound(CFTimes)

But for testing I'd use a smaller range and feel free to post some sample data to go with it!
You seem to be a fast learner anyway.

 

[jdmc45]

It still returns the subscript out of range error on line

PV_discrete = CFamounts(t) / (1 + r) ^ (CFtimes(t))

I have made the following variation

Sub TestPV_discrete()
    
    Dim CFtimes As Variant
    Dim CFamounts As Variant
    Dim r As Double
    Dim x As Double
    CFtimes = Worksheets("Bond").Range("A2:A101")
    CFamounts = Worksheets("Bond").Range("B2:B101")
    r = Worksheets("Bond").Range("C2")
    
    x = PV_discrete2(CFtimes, CFamounts, r)
    
    MsgBox ("Answer is " & "x")
    
End Sub
 
Function PV_discrete2(CFtimes As Variant, CFamounts As Variant, r As Double) As Double
    
Dim t As Integer
Dim cell As Object
If WorksheetFunction.Count(CFtimes) <> WorksheetFunction.Count(CFamounts) Then PV_discrete2 = 0 Else
    For t = 0 To UBound(CFtimes)
        For Each cell In Range("A2").Offset(t, 0)
            PV_discrete2 = CFamounts(t) / (1 + r) ^ (CFtimes(t))
    Next t
    End If
End Function

but now receive a different error, "Invalid variable control next reference" on line

Next t

I can't be far off! Any help is much appreciated. cheers

 

[jdmc45]

Now I'm trying using the function as a variant, and using the named ranges to calculate the function:

Function PV_discrete(CFtimes As Variant, CFamounts As Variant, r As Double) As Variant
    
Dim t As Integer
If WorksheetFunction.Count(CFtimes) <> WorksheetFunction.Count(CFamounts) Then PV_discrete = 0 Else
    For t = LBound(CFtimes) To UBound(CFtimes)
        PV_discrete = Worksheets("Bond").Range("B" & t) / (1 + Worksheets("Bond").Range("C2")) ^ (Worksheets("Bond").Range("A" & t))
    Next t
End Function

but with a type mismatch error. Any suggestions please...?