How to call a function inside another function in VBA?-CodePudding

I'm trying to call a function inside another function but the result I'm getting is 0

Function DepositRate(Deposit):
    If Deposit > 100000 Then
        Rtaxa = 0.078
    ElseIf Deposit <= 100000 And Deposit > 10000 Then
        Rtaxa = 0.073
    ElseIf Deposit <= 10000 And Deposit > 1000 Then
        Rtaxa = 0.063
    ElseIf Deposit <= 1000 And Deposit > 0 Then
        Rtaxa = 0.055
    Else
        Rtaxa = "Negative Value"
    End If
    
    DepositRate = Rtaxa
        
End Function

Function NewDFV(Value, Year):
    Call DepositRate(Value)
    ValorFuturo = Deposit * (1   Rtaxa) ^ (Year)
    NewDFV = ValorFuturo
    
End Function

So, Where I'm doing wrong here ?

CodePudding user response：

Unless Rtaxa is declared as a module or global variable, you will need to assign the return value of DepositRate to Rtaxa within function NewDFV

Function NewDFV(Value, Year)
    Dim Rtaxa As Variant
    Rtaxa  = DepositRate(Value)
    ValorFuturo = Deposit * (1   Rtaxa) ^ (Year)
    NewDFV = ValorFuturo
    
End Function

CodePudding user response：

You are calling the function with a =NewDVF(D1;D2) it should be =NewDVF(D1, D2) (must be comma, not semicolon)

Also you are calling the DepositRate function with CALL (normally reserved for Subs), and discarding its result; you should assign it to something.

Also you should use the Value (not the Deposit) to make the final calculation.

Function DepositRate(Deposit):
    If Deposit > 100000 Then
        Rtaxa = 0.078
    ElseIf Deposit <= 100000 And Deposit > 10000 Then
        Rtaxa = 0.073
    ElseIf Deposit <= 10000 And Deposit > 1000 Then
        Rtaxa = 0.063
    ElseIf Deposit <= 1000 And Deposit > 0 Then
        Rtaxa = 0.055
    Else
        Rtaxa = "Negative Value"
    End If
    
    DepositRate = Rtaxa
        
End Function

Function NewDFV(Value, Year):
    Rtaxa = DepositRate(Value)
    ValorFuturo = Value * (1   Rtaxa) ^ (Year)
    NewDFV = ValorFuturo
    
End Function

CodePudding user response：

A Function Calling a Function

Note that both functions can be used as UDF's. If you don't want the first function to appear in the functions list, use Private Function GetDepositrate(...)... while keeping both functions in the same module. The function will still be available just not listed.

Option Explicit

Function GetDepositrate(ByVal Deposit As Double) As Double
    
    Dim RTaxa As Double
    
    If Deposit > 100000 Then
        RTaxa = 0.078
    ElseIf Deposit > 10000 Then
        RTaxa = 0.073
    ElseIf Deposit > 1000 Then
        RTaxa = 0.063
    ElseIf Deposit > 0 Then
        RTaxa = 0.055
    Else
        ' Negative Value - do nothing
    End If
    
    GetDepositrate = RTaxa

End Function

Function NewDFV(ByVal Deposit As Double, ByVal Year As Long) As Double
    
    Dim RTaxa As Double: RTaxa = GetDepositrate(Deposit)
    
    NewDFV = Deposit * (1   RTaxa) ^ (Year)

End Function