VBA References (How to assign/unassign them)

[gedkins]

I have a very sophisticated Excel application that utilizes list boxes, common dialogs, etc. The result is it has a series of references to various dll's.Such as VB for Applications,Excel 10.0 Object Lib, OLE Automation, Office 10. Lib, MS Shell Controls and Automation. When I run this sheet on certain workstations it invariably ends up with a missing reference. This causes a runtime error and the VBA debugger opens, usually on a varibale declaration. If I then access the projects references it shows me which one is missing and I deselect it. Close the debugger and the program runs fine from then on. How on earth can it fail with the abscence of a reference and then run after deselecting the reference. It appears to me it never needed the reference in the first place. Can someone get me right on the references. How do you know what you should have? Can you prescan as the workbook is loading for what is needed and warn if the referenced dll is not present? Or this my re-introduction to dll hell?

Guy



Guy Edkins
Managing Partner
Delta Group Ltd

http://www.deltagl.com

 

[cummings54]

The reference libraries are prioritized in a project. The ones with higher priority are looked at first. It is also possible that the class you are referencing is defined in more than one library assigned to the project. Basically, if the library you don't have has a higher priority, your project will look there first for the class definition and then crash. When you remove it from the project and the class can be found elsewhere it runs OK.

 

[CajunCenturion]

I'll try to explain what happens. At run time, the VBA code is being interpreted, so when it comes across a variable declaration, it looks at the various libraries to find the proper definition for that variable type. It scans these libraries in order until the proper type is found. If it gets to a library that does not exist, before it has found the variable type, the error will occur. The error does not occur because the variable type is not found, but rather, because the missing reference interrupts the search. Once you remove the missing reference, the search will proceed, subsequently find the type defintion, and the program runs fine.

The solution is to pre-compile your project. Only include in the project those references necessary for an error-free compile.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[bpeirson]

CajunCenturion, I was under the impression that we can't compile code with VBA.

If we can compile with VBA, how do we do it and will the code stand alone?

 

[gedkins]

I forgot about the precedence of the libraries and the fact one is missing stops the code from running and removing the reference corrects the problem. The subtle nuances we forget. Thanks for the help!

Guy Edkins
Managing Partner
Delta Group Ltd

http://www.deltagl.com

 

[CajunCenturion]

You can compile the code, although you don't generate an exe in the traditional sense as you would with a traditional programming project. However, in the VBE, there is a compile option, which can be useful during the development process.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein