9

I've code which, when I press a button, will add a new sheet to a workbook and change the codename of the sheet to make it easier to refer to later in my code.

Dim wbk As Workbook
Dim wks As Worksheet

Set wbk = ThisWorkbook

wbk.Sheets.Add.Name = "Admin - Save Log"
Set wks = wbk.Worksheets("Admin - Save Log")
wks.Parent.VBProject.VBComponents(wks.CodeName).Name = "wksAdminSaveLog"

This does work - HOWEVER - only when I have the "Developer" window open or have previously had it open.

If I click the button when I first open the Excel document (having not opened the "Developer" window) it adds the sheet, however it comes up with the following error and does not change the codename of the sheet:

Run-time error '9': Subscript out of range

It is only when I press Debug and then run the code after the "Developer" window is open again that it adds the codename.

Is there any way around this so that the user does not have to have the developer window open in order for it to run correctly?

Community
  • 1
  • 1
Fu113R
  • 93
  • 4
  • 1
    You should `Set wks = wbk.Sheets.Add`, *and then* set its `.Name`. That way you wouldn't need to have the string literal spelled out twice. – Mathieu Guindon Dec 07 '16 at 16:24
  • You could try a `DoEvents` call or two. If you can, though, it would probably be safer to use an `Application.Ontime` call to run the code to change the codename (and anything else that happens). – Rory Dec 07 '16 at 16:25
  • Just to clarify, is the runtime error 9 raised on the `wks.Parent.VBProject.VBComponents(wks.CodeName)` access? – Mathieu Guindon Dec 07 '16 at 16:26
  • 2
    You're adding and renaming VBComponents in the VBProject that's currently executing. You're lucky the entire IDE & host app doesn't completely crash already - I suspect the VBE needs to have been loaded specifically so that something behind the scenes involving either the P-Code compiler or the VBA interpreter is having a chance to pick up the new component; if the VBE hasn't loaded, you're executing compiled code where `wks.CodeName` doesn't exist as a component. Tricky stuff. Interesting question. – Mathieu Guindon Dec 07 '16 at 16:32
  • As much as I still want to know the answer to this particular issue - is there a reason you can't just have the sheet already in the workbook with the correct code name but set the visibility to `xlVeryHidden` when it's not required? – SierraOscar Dec 07 '16 at 16:38
  • My suspicion (fwiw) is that the `VBComponents` collection is initialized at the start of run-time, but that opening the IDE triggers a refresh (presumably triggered in a separate thread but still accessing the same instance of that collection) because it has to display all available components at that time. Before opening the IDE that particular index doesn't exist because it wasn't there at the start of run-time. No idea how you would test that though - \* glares at @Mat'sMug \* – SierraOscar Dec 07 '16 at 16:40
  • **or** opening the IDE causes the execution to return control to the application - which updates the collection. But then I'm sure using `DoEvents` (as rory suggested) would accomplish the same thing.... – SierraOscar Dec 07 '16 at 16:43
  • The whole point is that I want to be able to easily add a change log to different workbooks, so rather than creating the sheets (or move/copy sheets from a different workbook), I want the user to be able to add the sheets then add the necessary code to the sheets at a press of a button. There are a large number of workbooks that this could potentially be added to so want to make it as automated as possible. – Fu113R Dec 08 '16 at 09:46

4 Answers4

6

@Comintern already got you a working solution, but this code doesn't pollute your Immediate Window, and uses the hidden _CodeName property to change the sheet name instead of accessing the vbComponents collection.

It also uses an early-bound Worksheet assignment to wks, and then a With block because it is accessing more than 1 member of wks.

Interestingly, the placement of the VBProject member usage is important.

Dim wbk As Workbook
Dim wks As Worksheet

Set wbk = ThisWorkbook
Set wks = wbk.Worksheets.Add

'Oddly, this statement MUST appear AFTER the Worksheets.add
Debug.Assert wbk.VBProject.Name <> vbNullString 'Don't pollute the Immediate window

With wks
  .Name = "Admin - Save Log"
  .[_CodeName] = "wksAdminSaveLog"
End With
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • I don't think it's odd at all: you're "creating" the VBE *after* all VBComponents exist in the project. – Mathieu Guindon Dec 07 '16 at 19:49
  • @Mat'sMug - Actually this doesn't de-reference the VBE at all. I wonder if I was swinging for the fences and could have just stopped at dereferencing `wbk.Project`. @ThunderFrame - Do you have to use the hidden `_CodeName` after you've dereferenced the project? Good call on the `.Assert`, BTW. – Comintern Dec 07 '16 at 19:52
  • I didn't try the vanilla `CodeName` *because* I knew about what Rubberduck does. And before I realised the placement of `vbProject` was important, I *thought* I'd found a bug in using `[_CodeName] inside a with block. – ThunderFrame Dec 07 '16 at 20:26
  • @ThunderFrame When I try this method it comes up with the following error on the Debug.Assert bit . . . . . Run-time error '1004: Programmatic access to Visual Basic Project is not trusted . . . . any ideas on how I can get around this? – Fu113R Dec 08 '16 at 10:17
  • I had to go to file>options>trust centre>trust centre settings>macros and then tick the box at the bottom "Trust access to the VBA project object model" - once I've done this, the code you suggested worked a treat! Thankyou very much! :) – Fu113R Dec 08 '16 at 14:46
5

If you need the VBE to have been opened, you can "trick" the debugging context into doing it for you. This seems to do whatever the project needs to update its indexing by forcing the VBE.MainWindow into existence:

Dim wbk As Workbook
Dim wks As Worksheet

Set wbk = ThisWorkbook

Set wks = wbk.Sheets.Add
wks.Name = "Admin - Save Log"
Debug.Print wbk.VBProject.VBE.MainWindow.Caption   '<--Force the VBE to exist.
wbk.VBProject.VBComponents(wks.CodeName).Name = "wksAdminSaveLog"

Edit:

It seems that simply obtaining the reference to the VBE.MainWindow is enough (see the comments). This also works:

Dim editor As Object
Set editor = wbk.VBProject.VBE.MainWindow   '<--Force the VBE to exist.
Comintern
  • 21,855
  • 5
  • 33
  • 80
  • 1
    Is simply referencing `wbk.VBProject.VBE.MainWindow.Caption` enough or would it have to be explicitly used with the Debug object? (I know `Debug.Print` is better for "throw away" code - just curious) – SierraOscar Dec 07 '16 at 17:07
  • 1
    @MacroMan - Not *entirely* sure. I did test just a `Debug.Print` and that didn't do anything. Printing the `Caption` was the first thing that I thought of that would get a property request all the way through to the `MainWindow`. Setting a object reference also appears to work. I'll update the answer. – Comintern Dec 07 '16 at 17:15
0

Another simple method to force refreshing of the VBComponents Collection:

PropertyGetDiscard ThisWorkbook.VBProject.VBComponents.Count

 

Private Sub PropertyGetDiscard(AnyPropertyGet): End Sub

The procedure PropertyGetDiscard is used to avoid polluting the Immediate window or using a superfluous variable

hymced
  • 570
  • 5
  • 19
0

A general approach to solve such VBComponent-dependent code problems is to use the VbeInit procedure given here that is flexible to be called multiple times for each newly opened workbook that may not yet be "VBComponent-initialized".

Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96