How to call a function inside another function in VBA?

Question

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 ?

VBasic2008 · Accepted Answer · 2022-02-10 03:06:14Z

2

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

edited Feb 10, 2022 at 3:06

answered Feb 10, 2022 at 2:58

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tinazmu Over a year ago

@braX, you are advised to use this example by @VBasic2008 for better practice: add Option Explicit, and declare (DIM) your variables with data types as you would expect them. You might like to declare them as variant if you expect to return different types (for example, if you want to return an error condition as #NA).

BZngr · Accepted Answer · 2022-02-10 00:49:51Z

1

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

answered Feb 10, 2022 at 0:49

BZngr

6816 silver badges7 bronze badges

Comments

tinazmu · Accepted Answer · 2022-02-10 02:51:14Z

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

Collectives™ on Stack Overflow

How to call a function inside another function in VBA?

3 Answers 3

A Function Calling a Function

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

A Function Calling a Function

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related