Microsoft Excel - Activate sheets by codename from the Personal.xlsb

Asked By Rowland Hamilton on 28-Oct-14 07:56 PM

How do I fix this?

So, I got it to work yesterday but today, seems like my excel settings are different maybe?
Both are in the Personal.xlsb and I am launching the code from a quickbutton in my quick access menu.

run time error 91, object variable or with block variable not set on MsgBox wsPivot.Name
watch on wsPivot.name shows the function did not retrieve the codename.

If I end debugger and launch the code again from VBE or the quick launch button, it works, ad infinitum. It just does not work the first time. If I step back to the function and step through it, it also works, so tough to debug when it works on debug, just not on initial run.

Here is the test code:

Sub

Test_Won()

Dim wsPivot As

Excel.Worksheet

Set wsPivot = GetWorksheetFromCodeName("Sheet1"

)

MsgBox wsPivot.Name

End Sub

The function is

Public Function GetWorksheetFromCodeName(ByVal CodeName As String) As

Worksheet

' Return the worksheet with the requested code

name.

Dim FocusSheet As

Object

For Each FocusSheet In

ActiveWorkbook.Worksheets

If FocusSheet.CodeName = CodeName

Then

Set GetWorksheetFromCodeName =

FocusSheet

Exit Function

End

If

Next

FocusSheet

End

Function

Rowland Hamilton replied to Rowland Hamilton on 29-Oct-14 01:26 AM

Oddly enough, it will work, but only if I launch the code from the VB Editor window, or step into it, right before it decides to use the Codenames the way I want it to. To test the theory, I added debug.prints for my Immediate window:

Code:

Public Function GetWorksheetFromCodeName(ByVal CodeName As String) As Worksheet
' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   MsgBox "Running Test Code in " & ActiveWorkbook.Name
   Debug.Print "Activeworkbook name is: " & ActiveWorkbook.Name
   'Stop
   
   For Each FocusSheet In ActiveWorkbook.Worksheets
   Debug.Print "First-FocusSheet.CodeName =" & FocusSheet.CodeName
   Stop
      If FocusSheet.CodeName = CodeName Then
      Debug.Print "Match-FocusSheet.CodeName =" & FocusSheet.CodeName
      Debug.Print "Match-CodeName =" & CodeName
      'Stop
         Set GetWorksheetFromCodeName = FocusSheet
         Debug.Print "Match-GetWorksheetFromCodeName = " & GetWorksheetFromCodeName.Name
         'Stop
         Exit Function
      End If
Debug.Print "Mismatch-FocusSheet.CodeName =" & FocusSheet.CodeName
Debug.Print "Mismatch-CodeName =" & CodeName
Stop
   Next FocusSheet
Debug.Print "Next FocusSheet.CodeName =" & FocusSheet.CodeName
Debug.Print "Next CodeName =" & CodeName
End Function

Rowland Hamilton replied to Rowland Hamilton on 29-Oct-14 01:27 AM

Code:
Sub Test_Won()
Dim wbREAL As Workbook
Dim wsREAL As Worksheet

Set wbREAL = ActiveWorkbook
'Workbooks("PERSONAL.XLSB").Activate
MsgBox "Running Test Code in " & wbREAL.Name
wbREAL.Activate
Set wsREAL = GetWorksheetFromCodeName("Sheet1")
MsgBox wsREAL.Name & " in " & wsREAL.Parent.Name
End Sub

Rowland Hamilton replied to Rowland Hamilton on 29-Oct-14 01:34 AM

The results of the debug.prints in the Immediate window are:

Activeworkbook name is: Book1
First-FocusSheet.CodeName =
Match-FocusSheet.CodeName =Sheet1
Match-CodeName =Sheet1
Match-GetWorksheetFromCodeName = Sheet1

When I open a fresh instance of excel and launch this code outside of the VBE, and Stop it at the bold stop, I notice that the FocusSheet.CodeName is not populated before stopping the code, but right after stopping the code, I can let the macro play or step through it, and the very next debug.print picks up the FocusSheet.CodeName, eventhough the only codes in between are debug.prints and stop. The only difference is that I have activated the VBE window by making it stop there and somehow, it is able to understand how to work from the PERSONAL.XLSB and pull codenames from the ActiveWorkbook and I didn't explicitly name the Actveworkbook in the code.

WHY? How come it can't work outside of the VBE the first time?

(Incidently, once it runs through the VBE window once, I can now launch it outside of VBE and it will work and on any new workbook I open, as long as I don't close Excel and start over again, then it breaks like it does, until I go into the VBE window again. IF the values I need are not captured before I enter the VBE window, then the code won't work, I have to get into it before the ciritical moment when it captures the codename,l in this case).

Harry Boughen replied to Rowland Hamilton on 30-Oct-14 03:40 AM

Hello Rowland,

Try changing this line.
Dim FocusSheet As Worksheet

Regards

Harry