Bogus Compile Errors
Hi all,
Let me start this post with a shameless plug: Charles Williams and I developed Name Manager. From my website alone this tool is good for about 60 downloads a day.
Just a couple of weeks ago, all of a sudden Charles and I started receiving complaints about compile errors, which neither of us could reproduce. Because the version we had available back then used the treeview control from the Windows common controls library, we blamed that control. And indeed, removing the control from the form that housed it fixed the problem for some of our users. But not for all.
Since this kind of errors is extremely hard to troubleshoot, I thought it might be useful to share our experiences.
Luckily we found a user who was willing to help us trouble-shoot the matter. We asked him to do all sorts of things: try on a different client, try logging on as administrator, removing accounts from client, you name it.
After exchanging a host of emails and screenshots and trying all sorts of variations we discovered the culprit: problematic .EXD files in one of the system folders of the client computer (these are just an example, your system may show others too or even none at all):
These files are typically stored in this location:
C:\Documents and Settings\[UserName]\Application Data\Microsoft\Forms\
So far, removing these files from that folder has resolved the issue for the people that experienced trouble with Name Manager.
So, if you distribute an addin to other users and you get a complaint about compile errors in your work: start off by asking the user to weed out the Forms folder I showed above. If it doesn’t help, it certainly wont hurt!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Jim Cone:
Jan,
Thanks for that. I have the files you listed (plus others) in the same folder on my machine and have to confess that is one folder I have never been to before.
Was the problem Excel version related?
For what it is worth, I found this older KB article with some information about exd files…
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q158875&ID=KB;EN-US;q158875
Jim Cone
4 February 2008, 3:15 pmDoug Jenkins:
Reading the KB article, it seems that if a Windows/temp folder exists, the exd files will be created there, and if there is no Windows/temp they will be created somewhere else. Either way, if they are deleted they will be re-created the next time the control is used.
Do exd files in Windows/temp cause any problems?
4 February 2008, 4:16 pmAfter deleting exd files, does the problem come back when they are re-created, or does it go away?
Jon Peltier:
What’s tricky about these .exd files is that you may have them in your regular %temp% directory, and you delete them, thinking you’re okay. But they can occur in the \Forms directory, as Jan Karel points out, and you can be battling them for days before you figure it out. A client recently had a lot of issues because of this, where an Excel 2003 add-in contained a table to build a command bar and images for the icons. He would install the add-in, and the images would be stripped out, and the code would fail. He’d replace the worksheet, save the add-in, and it would work fine for a while, then one day the images would be wiped out again. Since we cleaned out all the .exd files, he hasn’t seen this issue.
Doug - I haven’t noticed anything in the Windows\Temp folder, because Windows XP uses the %temp% folder, in the user’s subdirectory.
4 February 2008, 5:28 pmjkpieterse:
So far the problem seems to have been fixed by removing the exd files. What’s odd is that this problem has suddenly surfaced, after over a year of no problems at all.
5 February 2008, 12:33 pmJon Peltier:
It is surprising, because several of us seem to have just recently stumbled across it. I think I’ve only seen it affecting Excel 2003, and I don’t know whether the affected installations had upgraded to SP3.
5 February 2008, 4:33 pmjohn:
hi there
now slap me for being stupid ….but do you have to compile your vba
as i have never actually compiled mine and it all still works
sorry for being a right nana
john
6 February 2008, 5:26 amdoco:
Is this an issue with all exd files or just those you have listed? Don’t know if what I am about to explain is related or not…
6 February 2008, 7:57 amI also have created an add-in (originally created in Office 2000 and now running 2003) with about a half dozen utilities that require use of tabbed control on a form (custom wizard). I have it on two laptops and two desktops. It always worked great until the last year every now and then I began getting error message stating “could not load an object because it is not available on this machine.” I trace the problem to the tabbed control and in fact it is on the machine. Deletion of the add-in and reloading it usually eliminates the problem. I currently have that problem on this laptop, but deleting those exd files didn’t seem to do the trick.
How is it something like an add-in can work merrily along without a hitch and suddenly its broken?
jkpieterse:
Well, it is not certain which exd file(s) may be corrupt. Best is to just remove all of them, if absent they will be recreated the next time Excel needs them anyway.
6 February 2008, 9:39 amjeng02:
How about adding a check to the Name Manager install workbook that checks for 1) Excel 2003 (most problematic?), and 2) problematic .EXD files, and then warns the user?
I just had the compile error crash Excel for me… Still, I don’t want to seem ungrateful. Name Manager is really cool!!
20 February 2008, 12:57 pmjeng02:
I’m sorry to say that I cleaned out all the .exd files out of my user profile, but Name Manager still crashes Excel with that compile error every time I try to edit a name’s reference text.
I am using XP Pro, Excel 2003, 11.8169.8172 SP3.
Name Manager is still a great tool. I’ll just have to do my edits in the normal Names dialog.
20 February 2008, 1:18 pmjkpieterse:
Sometimes it helps to remove the profile (I know, a royal PITA!) and recreate it.
21 February 2008, 5:46 amDick Kusleika:
John: You don’t “have” to compile it, no. It does get compiled when you run it and compiling before running does a couple of things for you. It tells you if you have any errors before you run it. It also converts your code into something called p-code (I think). Basically there’s two compilation steps in VBA: Text to p-code, p-code to machine code. When your code is fully compiled in the VBE, it removes one of those steps that VBA has to do before it can run. You should always compile your code before you “release” it into the wild.
28 February 2008, 9:21 am