Changing VBE WS-component name in code craches excel 97

[Nick Dreyer]

Changing the VBE component name of any worksheet, as illustrated
in the sample code below predictably crashes my installation of excel
97.
I can do it manually without a problem, but . . .

Anyboy seen something like this before? If not try it . . . this looks
like an MS bug to me.
I'm running on Windows XP Professional Version 5.1 (Build
2600.xpsp2.050301-1526: Service Pack 1)
523 MB Ram

(Of course you have to add a reference to: "Microsoft Visual Basic for
Applications Extensibility")
________________________________________________

Sub MakeACorruptWorkbook()
'
' Warning: Run it and you *will* crash this excel 97 session !!!
'
Dim WSname As String
Workbooks.Add
ComponentofWS(ActiveWorkbook.Worksheets(1)).Name = "Sheet2"
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
WSname = ActiveWorkbook.FullName
ActiveWorkbook.Close
Workbooks.Open FileName:=WSname ' What bomb just went off ???
End Sub

Function ComponentofWS(ws As Worksheet) As VBComponent
Dim ThisProperty As Property
Dim SourceComponent As VBComponent
Dim SourceComponents As VBComponents
Set SourceComponents = ws.Parent.VBProject.VBComponents
For Each SourceComponent In SourceComponents
If SourceComponent.Type = vbext_ct_Document Then
For Each ThisProperty In SourceComponent.Properties
If ThisProperty.Name = "Name" Then
If ThisProperty.Value = ws.Name Then
Set ComponentofWS = SourceComponent
Exit Function
End If
End If
Next
End If
Next
End Function

 

[Peter T]

Don't do that, it's a known issue.

http://tinyurl.com/bhxts

Your code failed initially (without problem) trying to rename codename of
"Sheet1" to Sheet2, which of course already exists as the codename of
"Sheet2".

Following does the same as the intention of your dangerous code, hopefully
more safely (didn't crash my XL97).

Sub RenameCodeName()
Dim WSname As String
Workbooks.Add

ActiveWorkbook.VBProject.VBComponents("Sheet1") _
.Properties("_CodeName").Value = "NewName"

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
WSname = ActiveWorkbook.FullName
ActiveWorkbook.Close
Workbooks.Open FileName:=WSname
End Sub

Ought really loop VBComponents and check "NewName" does not already exist as
the codename of some other sheet, though I guess not an issue with a brand
new wb.

Regards,
Peter T

 

[Nick Dreyer]

Peter:

Thanks a bunch for pointing me to a confirmation of the bug existence
and the easy work-around.

I forgot that typically a new workbook already has a sheet2, since I set my
#-of-sheets-in-new-workbook to 1. That name conflict could have been a
separate problem when someone else (like maybe you?) ran the code, but you did
find the real source of my problem, which definitely manifests itself even if
you make a worksheet codename assignment to a not-already-existing codename.

I had not heard of using the "_codename" component property in this way, so
that revelation in-and-of-itself is of great help.

Basically, I've picked up my knowledge of VBE component manipulation by a
combination of common sense and trial and/or mostly error, quite a chore,
given that it appears that the object collections reindex themselves
constantly unless screen-updating is off.

Can you tell me of a good reference document on working with the VBProject
class?

Again, hanks-a-million, NickOn Sat, 3 Dec 2005 12:16:19 -0000, in
microsoft.public.excel.programming you wroth:

Nick

 

[Peter T]

Hi Nick,

Can you tell me of a good reference document on working with the VBProject
class?

My introduction was from Chip Pearson
http://www.cpearson.com/excel/vbe.htm

There's also an extensive MSDN topic.

Like you much of what I know has come from poking around, particularly
putting a break after setting a ref and looking in Locals. Looking at all
those "items" is a bit of a pain so to get an idea of what home in on -

Sub VBproperties()

Dim oVBproj As Object
Dim oVBcomp As Object
Dim oProp As Object
Dim r As Long, c As Long, k As Long

Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
Set oVBproj = ActiveWorkbook.VBProject
With ws
For Each oVBcomp In oVBproj.vbcomponents
r = r + 1
.Cells(r, 1) = oVBcomp.Type
.Cells(r, 2) = oVBcomp.Name
k = 0
For Each oProp In oVBcomp.Properties
k = k + 1
r = r + 1
On Error Resume Next ' only need once at the top without
goto 0 below
.Cells(r, 3) = k
.Cells(r, 4) = oProp.Name
.Cells(r, 5) = oProp.Value ' errors if n/a
On Error GoTo 0
Next
Next
End With
End Sub

Amongst that lot is "_codename"

Towards the end of this long thread -
http://tinyurl.com/dvb8m
it became apparent that writing to the property is more reliable than
writing directly. In that particular case no serious damage but I was
surprised, and hence the reason I looked for a similar solution with your
issue.

Having posted I did a quick search on "_codename" and found this issue has
already been covered by (you guessed it) Chip Pearson -
http://tinyurl.com/dmmdf
Had I seen that I would have simply referred you there and avoided crashing
not only Excel but my system! Chip also mentioned he includes this method on
his site, though with a quick search I couldn't find it there.

FWIW, you may notice in the demo above I use late binding declaring
everything "As Object", ie no ref' to Extensibility. I loose intellisence
and need to replace named constants with values (vbext_ct_Document =100).
However code works in both XL97 and later versions which use different
libraries. Also, if user's security settings (xl 2002/3) don't allow
access -
"Set oVBproj = .VBProject"
throws an error and can advise user to change security settings.

Regards,
Peter T

 

[Nick Dreyer]

Thanks for your further detailed replies with broad outlook. Your notes on
writing code for future releases will come in handy, as I no doubt will find
myself having to go there too some day.

Sorry my code crashed your machine, as well as excel. I hope with no
permanent damage. It's hard to write code that breaks things "perfectly" and
to the point. In this case I likely added unnecessary corruption to the real
bug by assigning the sheet2 name to an already existing component by that name
- given that the default number of new sheets in a new workbook is greater
than 1!

All the best!

|\|.

 

[Peter T]

No problem about crashing my system - I had been well warned! Actually
system only crashed after crashing Excel 2 or 3 times (that "Kernel"
message) while trying to find workarounds. Nothing to with the "sheet2" name
which I had changed.

It had occurred to me the problem was due to the file not compiling
correctly before saving. Kind of related - if you add a new sheet it's
impossible to return it's codename if the vbe is closed. However it's
possible with code to make the wb compile then get the codename of the new
sheet. Thought that trick might work with your issue - but it didn't!

Regards,
Peter T