Anthon
07-26-2016, 02:45 AM
Hi all,
I have encountered 2 separate problems with my VBA codes. Just to let you know I am just a beginner so these problems may seem trivial. I am using Excel 2013 for all scripts.
1) Here I am writing a code to determine NPV and I can't seem to get the function to work. The script as I have typed them is below:
Function nNPV(Rate, R)
nNPV = R(1) + Application.WorksheetFunction.NPV(Rate, R.Range("B1", R.End(x1ToRight)))
'R.Range(CellTopLeft, CellBottomRight)
'R.End(Direction): Directions are x1Down, x1ToLeft, x1ToRight, x1Up
End Function
When testing out the code on Excel worksheet I get the error #VALUE!
2) For the second problem I have written 2 auxiliary functions and a main function to call the auxiliary functions. The scripts for all the auxiliary and main functions are below:
Function ComputePV(cf()) 'Auxiliary function
Temp = 0
For i = LBound(cf) To UBound(cf) 'From time 0 to time n
Temp = Temp + cf(i) / 1.05 ^ i
Next i
ComputePV = Temp
End Function
Function GetN(R As Range) 'Auxiliary Function to get number of elements in R
If R.Columns.Count = 1 Then
GetN = R.Rows.Count
ElseIf R.Rows.Count = 1 Then
GetN = R.Columns.Count
Else
GetN = 0
End If
End Function
Function NewDynPV(R As Range)
Dim n As Integer 'Number of periods
Dim cf() As Double
n = GetN(R)
If (n = 0) Then
NewDynPV = n
Exit Function
End If
ReDim cf(1 To n)
For i = 1 To n
cf(i) = R(i)
Next i
NewDynPV = ComputePV(cf)
End Function
Compute(cf) above is what VBA indicates to be the error saying: Type Mismatch: Array or user-defined type is expected. So I was wondering how I can call my auxiliary cf function in the main function.
Thanks a lot for the help guys!
I have encountered 2 separate problems with my VBA codes. Just to let you know I am just a beginner so these problems may seem trivial. I am using Excel 2013 for all scripts.
1) Here I am writing a code to determine NPV and I can't seem to get the function to work. The script as I have typed them is below:
Function nNPV(Rate, R)
nNPV = R(1) + Application.WorksheetFunction.NPV(Rate, R.Range("B1", R.End(x1ToRight)))
'R.Range(CellTopLeft, CellBottomRight)
'R.End(Direction): Directions are x1Down, x1ToLeft, x1ToRight, x1Up
End Function
When testing out the code on Excel worksheet I get the error #VALUE!
2) For the second problem I have written 2 auxiliary functions and a main function to call the auxiliary functions. The scripts for all the auxiliary and main functions are below:
Function ComputePV(cf()) 'Auxiliary function
Temp = 0
For i = LBound(cf) To UBound(cf) 'From time 0 to time n
Temp = Temp + cf(i) / 1.05 ^ i
Next i
ComputePV = Temp
End Function
Function GetN(R As Range) 'Auxiliary Function to get number of elements in R
If R.Columns.Count = 1 Then
GetN = R.Rows.Count
ElseIf R.Rows.Count = 1 Then
GetN = R.Columns.Count
Else
GetN = 0
End If
End Function
Function NewDynPV(R As Range)
Dim n As Integer 'Number of periods
Dim cf() As Double
n = GetN(R)
If (n = 0) Then
NewDynPV = n
Exit Function
End If
ReDim cf(1 To n)
For i = 1 To n
cf(i) = R(i)
Next i
NewDynPV = ComputePV(cf)
End Function
Compute(cf) above is what VBA indicates to be the error saying: Type Mismatch: Array or user-defined type is expected. So I was wondering how I can call my auxiliary cf function in the main function.
Thanks a lot for the help guys!