Excel VBA error when calling DLL "An attempt was made to load a program with an incorrect format"

Question

I have an Excel file that calls a .net DLL (called "MyDLL") in the auto_open VBA macro. When it gets to the 2nd line below, the error below occurs:

--VBA Code--

If objSensitivity Is Nothing Then

Set objSensitivity = CreateObject("MyDLL.Calculations") '<<Crashes here

--Error--

An Error has occured: -2147024885 Automation error An attempt was made to load a program with an incorrect format.

My system is:

• A Windows 7 64-bit PC.
• It has Excel 2003, 2007 and 2010 installed (x86)
• It has .Net Framework 1.1, 2.0, 3.0, 3.5 and 4.0 installed for both x86 and x64
• It has all the latest windows updates.

Other notes:

• I know that the DLL was created in VS2005 in VB with a Target CPU setting of 'AnyCPU'
• It makes no difference if i change the Target CPU setting of 'X86' - the error still occurs.
• I recently had to rebuild my PC due to a hard drive failure, and before that it worked fine. So i dont know what has happened for it to not work now.
• The issue occurs in all versons of Excel i open the file in.

Since i know that it worked fine previous to my rebuild, and i know it works for other users running it on Windows 7 64 bit, i can only assume it is something else specific to my PC, and not the actual DLL.

Can anyone help?

Answer 1

I sorted this out.

I did a repair install of all my versions of Office, as i suspected it was a faulty Microsoft Interop DLL OR for some unknown reason, it was using a 64-bit version of the Interop.

Very weird, but its up an running now.

Answer 2

Try comparing the PATH environment variable between your machine and one that works.

It's kind of old fashioned - but the search process for finding dependent DLL files still uses the PATH to find files.

If one of the dependencies of MyDLL is somewhere else on the machine (not in the same or a nested directory), a missing PATH directory could defeat the loader.

Answer 3

64-bit processes cannot load 32-bit code and vica-versa. Have you tried Excel x64?

Answer 4

The VBA CreateObject() call is used to instantiate COM objects. Has the DLL in question been properly registered to create the appropriate registry entries?