Function is changing a variable in the calling subroutine

musicgold

Board Regular
Joined
Jan 9, 2008
Messages
175
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?


Code:
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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:

Rich (BB code):
Public Function Clean_formula(ByVal TempStr2 As String)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,407
Messages
5,414,223
Members
403,520
Latest member
Pineappleman

This Week's Hot Topics

Top