How do I pass a variable from one function to another function?

Question

I am trying to pass a variable from one function to another function.

My first thought is to use the ByRef declaration which I hear will pass arguments by reference in Visual Basic. I am unclear, however, on how to use it. Right now I have the following code.

            Function number(x As Double) As Double

                Dim z As Double
                z = 10.5
                number = x

            End Function

            Function triple(ByRef z As Double) As Double

                z = z * 3
                triple = z

            End Function

Lets say A1=2.5. If I say B1=number(A1) then the answer is 2.5 which I expected it to be. I then say B2=triple(B1) but then the answer is 7.5 instead of 31.5. I am not sure why it's taking the value of A1 instead of taking the value of the variable z from the function number.

Here's a screenshot of the worksheet

Thanks in advance.

Answer 1

The z inside the number function is gone by the time the function returns. The triple function just tipples the number passed, 2.5 times 3 which is what you are seeing. The tipple function has no idea of z. One way to share variables is to declare them outside of the functions. Dim z as double outside the function. But then if you pass z as the value of b1 which is 2.5 then you will get the same 7.5 just call triple, don't pass the value of b1

Dim z As Double

    Function tripple(x As Double)
        ' Use the value calculated from first function
        tripple = z * 3

    End Function
    Function number(x As Double)

        ' Set the global variable
        ' May be some calculations are done and z is set
        z = 10.5

        'Return x as in original code
        number = x
    End Function

Answer 2

I have to admit, I'm totally confused by what you are trying to do. My initial thought would be to do something like this, which is similar to what @dgorti suggested, but reverting to the input value of the function if z has never been declared.

VBA, unfortunately, does not support nullable Doubles, to the best of my knowledge, nor does it allow initialization of global variables. Either of these capabilities might preclude the need for the zDeclared variable below:

Option Explicit

Public z As Double
Public zDeclared As Boolean

Function number(x As Double) As Double
    z = 10.5
    zDeclared = True
    number = x
End Function

Function triple(x As Double) As Double

  If zDeclared Then
    triple = z * 3
  Else
    triple = x * 3
  End If

End Function

CAVEAT: This may not do what you expect. For example, if you call number and then call triple, the variable z will have been initialized. If you delete the function that calls number, you might expect triple to revert to the input value -- but it won't. What's done is done at this point.

And, in the way of explanation, the difference between the default ByVal in a parameter versus the optional ByRef is that ByVal creates a new instance of the parameter value, whereas ByRef uses the existing variable and therefore retains any changes to it. Maybe you understood that already, but I thought it worth clarifying.

Sub IncrementByVal(ByVal inp As Integer)
  inp = inp + 1
End Sub

Sub IncrementByRef(ByRef inp As Integer)
  inp = inp + 1
End Sub

Sub Test()
  Dim X As Integer
  X = 1
  IncrementByVal X  ' x is still 1  - the increment was for a locally scoped variable only
  IncrementByRef X  ' x is now 2
End Sub