Workbook_Open in Personal.xls (2003)

Topic

New Reply

Hi,
I wrote a Workbook_Open macro to create a custom menu and stored it in the ThisWorkbook module of my Personal.xls. I did this yesterday, and I’ve experienced some problems with Excel running macros from my Personal.xls since. Is this a practice to be avoided? I looked online and, while I didn’t see any counsel against doing so, no one in any code examples that I saw did it, either.
Thanks a lot.

Reply | Quote

Replies

I tend to put as little code in ThisWorkbook as I possibly can. For one: Editing in the VBA editor leaves behind all sorts of junk (you cannot see). Normal modules can be cleaned up by exporting, deleting and importing them. But the thisworkbook module cannot be removed; you must rebuild the workbook. Also, some code only works well in normal modules.

Reply | Quote

Hi Jan Karel,

I am very interested in your remarks on this topic. Could you expand on them a little? For example, what code would work well in normal modules but not in workbook or worksheet modules?

Thanks…

Reply | Quote

Well, it’s not exactly about particular types of code, but the thisworkbook module is a class module and as such behaves itself differently than a normal module.
If you don’t have the habit of fully qualifying your objects, code in thisworkbook sometimes doesn’t work, where-as when you place the exact same cod ein a normal module it does.

I have no examples at hand, because I’m speaking from experience I’ve had with other people asking questions in forums (like this one), which were solved by moving the code to a normal module.

Another thing I often do in the Workbook_open event is NOT call the initialisation routines directly. Rather I use Application.Ontime to set an event that will run the init routine.
The net effect is, that the init sub runs *after* Excel is fully loaded (including its addins).

Reply | Quote

Thanks I never thought about the permanent nature of thisworkbook in that way. I’ll move the code elsewhere and call it. Never used application.ontime before, so I’ll take a look at that.
Regards

Reply | Quote

What problems are you having? What does the code do? Can you post a copy of the code?

Reply | Quote

HI Legare,
The problems were:
a) some macros in Personal.xls weren’t being found when I ran them.
a couple of crashes
c) This morning, I sent a new workbook to someone, and suddenly she got one of my personal toolbars on her screen, although of course the macros it runs weren’t present.
(We’ve got a heavily protected environment. I ran a full scan anyway and came up with no viruses etc.)
This last problem made me suspect that my xlb file was corrupt, so I deleted it. I also moved the code from thisworkbook in Personal per Jan. These two actions seem to have cleared up the problems.
Thanks.

Reply | Quote