VBA - string variable changing by itself when function called???

[Johnny C]

I've got a variable called strShtName, declared as a string.

It's set once at the top of a loop.

Way down in the loop, a function is called

If TblShapeExists(strShtName) Is Nothing Then

Half way through the function, the value of strShtName is changed on the line in red below. There's a watch on the variable set to break when the value of strShtName changes. if I move the yellow cursor back up a line and F8 to step through it changes again,

The function works fine, but why is it altering the value of the parameter used to call it?
Even weirder, it worked fine, it's worked fine for the last year. I changed code somewhere else in the loop and it started doing this.

This is the function.

Function TblShapeExists(strTblName$) As Shape

If InStr(1, strTblName, "table:") > 0 Then
    If InStr(1, strTblName, ",") > 0 Then strTblName = Left(strTblName, InStr(1, strTblName, ",") - 1)
    strTblName = Replace(strTblName, " table: ", "")
    If InStr(1, strTblName, " ") > 0 Then strTblName = Left(strTblName, InStr(1, strTblName, " ") - 1)
End If

[COLOR="#FF0000"]strTblName = "Table: " & Trim(strTblName)[/COLOR]
On Error Resume Next
Set TblShapeExists = ActiveSheet.Shapes(strTblName)
On Error GoTo 0

End Function

TIA

 

[Rick Rothstein]

Way down in the loop, a function is called

If TblShapeExists(strShtName) Is Nothing Then

Half way through the function, the value of strShtName is changed on the line in red below. There's a watch on the variable set to break when the value of strShtName changes. if I move the yellow cursor back up a line and F8 to step through it changes again,

This is the function.

Function TblShapeExists([B][COLOR="#FF0000"]ByVal [/COLOR][/B]strTblName$) As Shape

The problem is that arguments are passed ByRef (by reference) by default. If you add what I show in red above, a copy of the argument will be passed into the function and the function will not be able to change it like it is doing now.

 

[Johnny C]

Doh! Thanks Rick, lazy schoolboy error! I don't usually write functions like this, they're byref so I dont specify.

I'm still puzzled as to why it suddenly stopped working. It's buried in about 100 rows of code parsing cell formulae to fish out table references, there must be something that didn't work but now does. That's my Monday sorted I guess.