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
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
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?
After deleting exd files, does the problem come back when they are re-created, or does it go away?
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 WindowsTemp folder, because Windows XP uses the %temp% folder, in the user’s subdirectory.
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.
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.
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
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…
I 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?
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.
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!!
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.
Sometimes it helps to remove the profile (I know, a royal PITA!) and recreate it.
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.
Nothing helps! I still get this error message with Office 2003. Luckily we get Office 2007 soon and I can use the name manager again! Great tool anyway!
I stumbled across this problem, specifically on (2) machines running Visio 2010. The control in question was a Listview, which is in the same Common Controls library as the treeview. Delection of the MSCommctrlLib.exd fixed teh initial problem, but I am still battling an issue where the properties and methods of the control are not recognized… but only on one specific userform. When other userforms containing listviews are run, there is no problem. Only one specific userform, but the twist is that I have listviews contained in a multipage control, which has been well documented as a source for strange behaviours. Wondering if anyone else has seen this.
I had the problem about a year ago. (we use excel2007 .xlsm )Yesterday it occured again. we used the same solution as above. Yesterday we deleted the exc files again. But the files kept creating again. The origin of the files is probably our administration software (Exact), which I don’t use, but my coworker does. Deleting by hand is a short term solution. Is there a routine to get rid of this problem for all time? In de forms directory I have three files; excel.box, MSComctLib.exc and RefEdit.exd
Is the solution maybe to change the VBA routine? with kind regards, Marco Roosendaal