Dutch Gemini's Weblog Pages

December 3, 2009

Change the Sheet’s CodeName in Excel using VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 3:16 pm

Update: this trick seems to work only with the VBIDE open. If your application want to modify the CodeName and you do not have the VBIDE open, the statement does not fail but does not work either. Please read entire story for full details.

In Excel is it not permitted to change the sheet’s .CodeName via code but only using the Properties windows in the IDE. Trying to assign a different codename will fail, Excel stops telling us that we try to assign a read-only property.

With a little trick, you can change the .CodeName at run-time. enter the text in a code module or in the immediate window:

' Assign the CodeName of a sheet via the VBProject of the sheet's Parent (i.e. the workbook)
'
ActiveSheet.Parent.VBProject.VBComponents(ActiveSheet.CodeName).Name = "MyNewCodeName"

With the above statement we access the same container but this time Excel provides us with a read-write property.

Pay attention that the name may not contain white spaces or other characters that are accepted for the .Name property.

However, Excel does not carry out the command when the code is executed in one rush, i.e. not in debug mode. Apparently the next statement does not access the proper VBComponent at run-time, although (according to the books) Excel should do so:

ActiveSheet.Parent.VBProject.VBComponents(ActiveSheet.CodeName)

I tried to workaround the problem assigning the given VBComponent to an object, but with a breakpoint set on line 3 I could see that the object was Nothing:

Dim oObject As Object
Set oObject = ActiveSheet.Parent.VBProject.VBComponents(ActiveSheet.CodeName)
If Not oObject Is Nothing Then oObject.Name = "MyNewCodeName" ' does not work, oObject is Nothing

The final solution is to loop through the collection of VBComponents programatically and assign the value once the proper VBComponent is identified in the loop. This seems to hook to the desired VBComponent correctly:

Dim oVBComponent As Object ' or "As VBIDE.VBComponent" if using early binding
'
For Each oVBComponent In ActiveSheet.Parent.VBProject.VBComponents
    ' Besides looking at the Name you can also test on Type, etc. (not done here).
    '
    If (oVBComponent.Name = ActiveSheet.CodeName) Then oVBComponent.Name = "MyNewCodeName"
Next oVBComponent

Dutch

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: