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

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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.

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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Way down in the loop, a function is called
Code:
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.

Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top