Function is changing a variable in the calling subroutine

[musicgold]

Hi,

I am noticing an unusual behavior that a called function changes a variable of the calling subroutine.

As shown below, subroutine Change_rows() is calling Clean_formula(). What I am noticing is that when the control comes back to Change_rows() the value in formula1 is the same as that in Tempstr2.

I monitored the variables and it appears that the value in formula1 changes as soon as TempStr2 changes inside the function.

Why is this happening? How can I avoid it?

Public Sub Change_rows()

...

  formula1 = oLine.Cells(1, 3).formula  

  TempStr = Clean_formula([COLOR="#0000FF"]formula1[/COLOR]) 

...
End Sub


Public Function Clean_formula(TempStr2 As String)
' this function removes the / and * signs in a formula

Dim Signs As Integer

    Signs = 0
      Signs = InStr(1, TempStr2, "/")            ' remove any division or multiplcation terms in the formula
      If (Signs > 0) Then TempStr2 = Left$(TempStr2, Signs - 1)
      
      Signs = 0
      Signs = InStr(1, TempStr2, "*")               ' remove any division or multiplcation terms in the formula
      If (Signs > 0) Then TempStr2 = Left$(TempStr2, Signs - 1)
      
       TempStr2 = Replace(TempStr2, "'", "")
      
      Clean_formula = [COLOR="#FF0000"]TempStr2[/COLOR]

End Function

 

[RoryA]

It's changing because you are passing the variable ByRef, so the changes in the function are reflected in the actual variable. A simple fix is to pass it ByVal:

Public Function Clean_formula(ByVal TempStr2 As String)