CodeName assignment for the new Worksheet.

[AN]

Working on a project I found following fact:
1. CodeName for the added Worksheet is not available/assigned immediately at
run-time.
2. Executing the same piece of the code when VBA IDE is open gives different
result (which is understandable).

Is there any explanation to the finding?

Thanks,
AN

Code to test:

Sub AddWorksheets()
Dim WBook As Workbook
Dim WSheet As Worksheet
Dim i As Integer

' Open Log File
Open ThisWorkbook.Path & "\" & "AddWorksheets_" & Format(Now,
"yyyy.mm.dd_hh.mm.ss") & ".txt" For Output As #3
Print #3, "******************************************"
Print #3, Now

' create new workbook
' and save it and close
Set WBook = Workbooks.Add
WBook.SaveAs Filename:=ThisWorkbook.Path & "\External.xlsm",
FileFormat:=xlOpenXMLWorkbookMacroEnabled, ReadOnlyRecommended:=True
WBook.Close False
Set WBook = Nothing

Application.DisplayAlerts = False
' open saved
Set WBook = Workbooks.Open(Filename:=ThisWorkbook.Path & "\External.xlsm")
Application.DisplayAlerts = True
' add to log
Print #3, WBook.Name & " Add Worksheets..."
' add few worksheets to the new workbook
WBook.Worksheets.Add After:=WBook.Worksheets(Worksheets.Count), Count:=3
' add count to log
Print #3, "WBook.Worksheets.Count: " & WBook.Worksheets.Count
' Adding Index, CodeName and Name of each Worksheet to log
Print #3, "WBook contains:"
For Each WSheet In WBook.Worksheets
Print #3, vbTab & "Index: " & WSheet.Index & " WSheet.CodeName:
" & WSheet.CodeName & " WSheet.Name: " & WSheet.Name
Next WSheet
' close log
Close #3

WBook.Close False
Set WBook = Nothing
Kill ThisWorkbook.Path & "\External.xlsm"
End Sub

Results:
1. At run-time:
******************************************
12/04/2009 10:07:44 PM
External.xlsm Add Worksheets...
WBook.Worksheets.Count: 6
WBook contains:
Index: 1 WSheet.CodeName: WSheet.Name: Sheet1
Index: 2 WSheet.CodeName: WSheet.Name: Sheet2
Index: 3 WSheet.CodeName: WSheet.Name: Sheet3
Index: 4 WSheet.CodeName: WSheet.Name: Sheet4
Index: 5 WSheet.CodeName: WSheet.Name: Sheet5
Index: 6 WSheet.CodeName: WSheet.Name: Sheet6

The CodeName is absent.

2. With VBA IDE open.
******************************************
12/04/2009 10:02:36 PM
External.xlsm Add Worksheets...
WBook.Worksheets.Count: 6
WBook contains:
Index: 1 WSheet.CodeName: Sheet1 WSheet.Name: Sheet1
Index: 2 WSheet.CodeName: Sheet2 WSheet.Name: Sheet2
Index: 3 WSheet.CodeName: Sheet3 WSheet.Name: Sheet3
Index: 4 WSheet.CodeName: Sheet4 WSheet.Name: Sheet4
Index: 5 WSheet.CodeName: Sheet5 WSheet.Name: Sheet5
Index: 6 WSheet.CodeName: Sheet6 WSheet.Name: Sheet6

The CodeName is present.

 

[Peter T]

When a new sheet is added it's codename will only get assigned if the VBE
has been open at any stage in with the workbook, or the project has been
compiled by other means, or as the file is saved (need to close & reopen).

There are various approaches to workaround but the main problem with all
approaches is if "Trust access to the VBA project object model" is not
enabled. Perhaps the easiest way (though I'd try others first) is to
open/close the VBE. No problem to open, but without the trust setting will
need either API methods to close the VBE. For simplicity could try SendKeys
in pre Vista, or alternative SendKeys for Vista+ or yet other ways. Here's
a very brief demo, flashVBE(), which omits many things I'd include in a full
app (error handling, verify which window is active, etc).


Sub AddWorksheets()
Dim WBook As Workbook
Dim WSheet As Worksheet
Dim i As Long
Dim ws As Worksheet
Dim sCodeName As String, sShtName As String
Dim ext As String

ext = ".xls"
If Application.Version >= 12 Then ext = ext & "m"

Set ws = ThisWorkbook.Worksheets(1)
lastRow = Cells(60000, 3).End(xlUp).Row + 1

' create new workbook
' and save it and close
Set WBook = Workbooks.Add

If Application.Version >= 12 Then
WBook.SaveAs Filename:=ThisWorkbook.Path & "\External" & ext, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled,
ReadOnlyRecommended:=True
Else
WBook.SaveAs Filename:=ThisWorkbook.Path & "\External" & ext
End If
WBook.Close False
Set WBook = Nothing

Application.DisplayAlerts = False
' open saved
Set WBook = Workbooks.Open(Filename:=ThisWorkbook.Path & "\External" &
ext)
Application.DisplayAlerts = True
' add to log
ws.Cells(lastRow, 1) = WBook.Name & " Add Worksheets..."
' add few worksheets to the new workbook
WBook.Worksheets.Add After:=WBook.Worksheets(Worksheets.Count), Count:=3
' add count to log
lastRow = lastRow + 1
ws.Cells(lastRow, 2) = "WBook.Worksheets.Count: " &
WBook.Worksheets.Count
' Adding Index, CodeName and Name of each Worksheet to log
lastRow = lastRow + 1
Cells(lastRow, 2) = "WBook contains:"
For Each WSheet In WBook.Worksheets
sCodeName = ""
sCodeName = WSheet.CodeName
If Len(sCodeName) = 0 Then flashVBE
ws.Cells(lastRow, 3) = vbTab & "Index: " & _
WSheet.Index & " WSheet.CodeName: " &
sCodeName & _
" WSheet.Name: " & WSheet.Name
lastRow = lastRow + 1
Next WSheet

WBook.Close False
Set WBook = Nothing
Kill ThisWorkbook.Path & "\External" & ext
End Sub

Sub flashVBE()
Dim ctrl As CommandBarControl
Set ctrl = Application.CommandBars.FindControl(ID:=1695)
ctrl.Execute
Set ctrl = Nothing
On Error Resume Next
Set ctrl = Application.VBE.CommandBars.FindControl(ID:=752)
On Error GoTo 0
If Not ctrl Is Nothing Then
ctrl.Execute
Else
Application.SendKeys ("%q") ' won't work in Vista
'AppActivate Application.Caption
End If
End Sub

Regards,
Peter T

 

[AN]

Thank you, Peter.

In my project I used Name (which is the same as CodeName just after
Worksheets.Add) to work around the problem but, it has own negative effects I
have to take care of.

I would consider the solution you have offered as the "last resort", as you
mentioned.

I encountered this behavior for the first time and was unsure of its origin.
So, your reply keeps me sane .

Thanks again,



Thanks again,
Alexander.


My

 

[Chip Pearson]

My guess is that the code name is accessible only after the VBA code
has been compiled. Since compilation doesn't occur during the
execution of a procedure, the code name isn't there.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[AN]

Thank you Chip for your suggestion!

Regards,
Alexander.