How to call Auxiliary Functions

[Anthon]

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!

[Kenneth Hobs]

Welcome to the forum!

When posting code, please paste between code tags. Click the # icon on the toolbar to insert the tags.

I highly recommend that you use Option Explicit and require Variable Declaration. You can set those as defaults in VBE's Options. Then use Dim to declare all of your variables.

Of course you made a typo in the xlToRight. You used one (1) rather than "L". Using Debug menu's Compile is a good idea before a Run. I put that on my VBE toolbar.

You should declare what type of value your Functions should return.

If needed, you can attach a simple file which helps us help you. Sample data is good to include and show manually computed expected results as well. Click Go Advanced button in lower right of a reply, and click the paperclip icon on toolbar to Browse and Upload file.

e.g.

Option Explicit
'Will not be a UDF:
Function nNPV(Rate, R) As Double 'Both inputs are type, Variant
nNPV = R(1) + Application.WorksheetFunction.NPV(Rate, R.Range("B1", R.End(xlToRight)))
'R.Range(CellTopLeft, CellBottomRight)
'R.End(Direction): Directions are x1Down, x1ToLeft, x1ToRight, x1Up
End Function


Function ComputePV(cf() As Variant) As Double 'Auxiliary function
Dim Temp As Double, i As Long
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

[Paul_Hossler]

1. Using the [#] icon to add CODE tags

2. Using Option Explicit to ensure all variables are properly Dim-ed

3. Using explicitly typed function return variables

4. Not sure about your nNPV logic since it doesn't match the WS function so I changed it

5. In ComputePV you have to pass cf() not just cf to get it to compile





Option Explicit
Function nNPV(Rate As Double, R As Range) As Double
Dim ary As Variant
Dim r1 As Range
Set r1 = Range(R.Cells(1, 1), R.Cells(1, 1).End(xlToRight))
ary = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Trans pose(r1.Rows(1)))

nNPV = Application.WorksheetFunction.NPV(Rate, ary)
End Function

Function NewDynPV(R As Range) As Variant
Dim n As Integer 'Number of periods
Dim cf() As Double
Dim i As Long

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

Function ComputePV(cf() As Double) As Double 'Auxiliary function
Dim Temp As Double
Dim i As Long

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) As Long '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