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.Transpose(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