Calling Private Worksheet level module from standard module doesn't always work after changing sheet's CodeName

[starl]

I'm testing out some logic, so the coding is rather subpar.
I have 2 sheets, each with the same Private sub that just does some simple math using a module level private variable.
In a standard module, I wrote the following test and it worked properly (calling the Private subs and debug.print the results)

Sub TestCallPrivateSub()
Dim Progname As String
Progname = ActiveSheet.CodeName & ".TestVariable"
Application.Run Progname
Application.Run "Sheet5.testvariable"
End Sub

Next test - the workbook will have multiple sheets and the Private sub will only be on some sheets. So I change the CodeName of those sheets to start with 'Data' (eg, Data1, Data2).
I then wrote the following:

Sub TestCallPrivateSub2()
Dim Progname As String
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
   If Left(wks.CodeName, 4) = "Data" Then
      wks.Activate
      Progname = wks.CodeName & ".TestVariable"
      Application.Run Progname
   End If
Next wks
End Sub

I added the .Activate because I thought that was the reason this wasn't working properly. What works: it calls and runs the code on the first Data sheet (Data1.TestVariable). But when it loop and runs the send sheet (Data2.TestVariable), I get an error: Cannot run the macro 'Data2.TestVariable'. The macro may not be available....
Obviously it's calling the correct codename and macro and it ran fine with the original codename. If I leave the codename as is and run the first test - it fails. If I change the codename back, it works fine.
I am at a loss as to why I can edit the codename on one sheet, but not the other.
Also tried this Application.Run "data2.testvariable" and got the same error.
Have confirmed the codename

?activesheet.codename
Data2

just fyi, here's one of the private subs

Private p_testvariable As Long

Private Sub TestVariable()
p_testvariable = p_testvariable + 1
Debug.Print p_testvariable
End Sub

I wanted to keep the various private variables alone in each sheet module. The other way I can do this would be a class holding the variables which is saved to a collection, but I'm afraid it could slow the program down looking things up. Ultimately, when a sheets Calculate_Event is triggered, the sheet uses its variables to do some logic. I have no idea how many sheets I may end up with.

 

[RoryA]

Works fine here, as expected. Why are they private if you want to call them from elsewhere though?

 

[Domenic]

Here's what I've observed . . .

Scenario 1

1) Opened a new workbook containing a single worksheet.
2) Added TestVariable() to the code module for Sheet1.
3) Added TestCallPrivateSub() to a regular module.
4) Ran TestCallPrivateSub() successfully.
5) Manually changed the code name for Sheet1.
6) Changed the code name referenced in TestCallPrivateSub() accordingly.
7) Ran TestCallPrivateSub() successfully.

I tried this a number of times, without any problems.

Scenario 2

1) Opened a new workbook, and added 4 additional worksheets for a total of 5 worksheets.
2) Added TestVariable() to the code module for Sheet5.
3) Added TestCallPrivateSub() to a regular module.
4) Ran TestCallPrivateSub() successfully.
5) Manually changed the code name for Sheet5.
6) Changed the code name referenced in TestCallPrivateSub() accordingly.
7) Ran TestCallPrivateSub(), however this time I got the same error.
8) Saved the workbook, ran it again, and still got the same error.
9) Closed the workbook, re-opened it, and then ran it successfully.

I tried this a number of times with different sheets, all with the same result. So it looks like the error can be avoided by saving, closing, and re-opening the workbook after changing the code name for the sheet.

 

[RoryA]

Hi Domenic,

I can replicate your scenario 2. Making the routine public and calling it as a method of the worksheet seems to work though.

 

[Domenic]

Hi Rory,

I tried making the routine public, but it seems to do the same thing as scenario 2. Although, there were a few times where I could change the code name and it would run successfully without having to close and re-open the workbook.

Cheers!

 

[starl]

Making the routine public and calling it as a method of the worksheet seems to work though.

I need to initialize and modify variables specific to each sheet - hence making the variables Private. To do that, I have a Sub on the sheet that's Private because each sheet has the same Sub. Making them Private was the only way I knew to work around the unique name issue.
Or is there another way (other than creating a collection with a class object for its value)?

Domenic - thanks for figuring out a workaround.. shall we call this a bug?

 

[Domenic]

Domenic - thanks for figuring out a workaround.. shall we call this a bug?

Hmmm... That's a good question. I don't know. But at least I know what to do if I come across this issue.

 

[Jon von der Heyden]

I did a quick test, and I get exactly the same behaviour you describe, Tracy. Then I tried CallByName, but I can't get that to run a worksheet procedure either. So no idea, but I figure if it works for some and not others, we have a bug!

 

[RoryA]

Making them Private was the only way I knew to work around the unique name issue.

There is no issue with the names as they are methods of the individual sheet class. They can all have a routine with the same name.

 

[starl]

There is no issue with the names as they are methods of the individual sheet class. They can all have a routine with the same name.

Sorry for the delay, but I needed to test this out and (no surprise), you're right, @RoryA .. which if I had really thought about it, makes perfect sense since the same Event on multiple sheets has the same name. I just never really thought about it since code in standard modules more than sheet modules. I even have had the same function in 2 class modules (I have a perfectly good reason!) Even after all these years, still learning!