Search code examples
excelvbafunctionreturn

VBA - Pass arguments from one function to another function without returning to first function


Any methods where I can pass variables from one function to another without returning to first function Say:

Function F2(Optional endAll As Boolean = False)

Cells(R1,C1)= Evaluate(jGetFormula)

If endAll Then End


End Sub

Function f1()
jGetFormula = " = " & "A1" & "*" & 10 & "^" & 3 & "*" A2 & "/" & 148 
R1 = Selection.Row
C1 = Selection.Column
Call F2(True)

End Function

here function f1() creates excel formula and get corresponding row and column number of cells where =f1() entered in cell and passing these to another function and end without returning back to f1(). The function f2() takes arguments from f1() and converts the string to formula and replaces =f1() by the formula as =A1 * 10^3 * A2 / 148 in the cell where earlier =f2() was written.


Solution

  • Function ffeeee2(jR1 As Long, jC1 As Long, jGetFormula As String, Optional endAll As Boolean = True)
            MsgBox jGetFormula
            MsgBox "function f1 entered in cell (" & jR1 & "," & jC1 & ")"
            MsgBox "This ends f2"
            If endAll Then Exit Function
        End Function
    
        Function ffeeee1()
            Dim jR1 As Long
            Dim jC1 As Long
            Dim jGetFormula As String
            jR1 = Selection.Row
            jC1 = Selection.Column
            jGetFormula = "Function value from f1 (row number + column number =" & jR1 + jC1 & ")"
            Call ffeeee2(jR1, jC1, jGetFormula, True)
        End Function