Missing references VBA fix (to avoid compile error)?

Hi,

I have an excel database which links into Outlook and Word via macros to automate sending of e-mails and creating documents, etc. Obviously, I have created the correct VBA references and things have been working fine for a while.

However, this is a shared workbook over a small number of machines and due to a recent upgrade, one of the machines is running Vista and Office 2007, whereas the rest run Office 2000 and NT.

All works well until the workbook is opened and saved on the Office 2007 machine as this then changes all the references to Word 12, Outlook 12, etc, instead of Word 9 as seen in Office 2000. Then, when an office 2000 machine opens the workbook, it has a compile error as it cannot find the office 12 references!!

I have created some code to fix this, which uses the AddFromGuid method, which works ok, e.g.:

Please Login or Register  to view this content.

What I want to do is as excel opens, check for missing references and fix them automatically, which is pretty simple on the face of it. However, as the macro fails due to a compile error all macros stop running, therefore whatever I put in the Open_Workbook event doesn't run.

Does anyone know how to either stop the compiler running on start up, or a way of error handling the compile error??

Either

a) add some code to the Workbook_BeforeClose routine to unload the reference(s) such that they are not missing on open

b) use Late Binding and avoid need for referencing libraries in first instance
(can make coding trickier and I would say it's not always possible pending the actions being performed)

Examples...

http://www.excelforum.com/excel-prog...ough-code.html

If I unload the references before close then you are correct, they won't be classed as missing on start up. However, the compile error will still occur as the coding for operating Outlook and Word won't be recognised (as there are no references at all).

No, I think you're missing my point.

Early Bind in the Workbook_Open event, remove the libraries in the Close event.
Bind appropriately (by checking version).
The link provided previously gave an illustration of this concept.

If you applied your logic to the entire process the same would be true upon Close - ie at the point of de-referencing the library all code referencing said objects would generate debug - it does not.

So my question would be... are you invoking routines via the Open event ?

If you are I'm not quite sure how you can handle this to be honest using Early Binding. The variable being Public shouldn't matter unless the module in which it resides is invoked from the open event at which point it will compile the Module (ie running the binding first in the open event will not make any difference).

Attached is a basic demo of the point... if you open the file you should find the word library has been added without compile error, closing the file and opening another file will show it's been removed upon close... however if you re-open the file, activate the call in the open event to test save and re-open the file you will get the compile error you're referring to.

DonkeyOte,

I can't seem to find your attachment but I understand your point I think as I just did a similar test.

I created a workbook with the following code to operate Outlook in module 1:

Please Login or Register  to view this content.

In the ThisWorkbook object I put the following:

Please Login or Register  to view this content.

And this works perfectly. However, doing similar on the proper workbook still has a compile error. This then confused me.

However, as you guessed in the last post I am also invoking other routines in the Open_Event in the proper workbook:

Please Login or Register  to view this content.

Is this the reason why it's compiling and subsequently failing???

Are the error caused by you doing this action?

I removed the references manually

Personally I would go with the late binding approach.
You would need to replace explicit references to objects with generic object.

Please Login or Register  to view this content.

Also any constants would need to be declared. If you use Option explicit you should be able to track these down.

With regards to other code in the Open event you can get around that by moving the code to a separate module and after doing the AddFromGuid then call that routine.

Originally Posted by Andy Pope

With regards to other code in the Open event you can get around that by moving the code to a separate module and after doing the AddFromGuid then call that routine.

Thanks Andy - the one thing I did not check ... once again I am guilty of making "assumptions" without testing (ie that it would work it's way through and still debug)

Can you outline (roughly) the process of code compilation (ordering etc) - as is blatantly obvious I'm not overly clear on it myself.

I don't pretend to know the in's and out's of how the compiler works. I can not find that level of info in any of my books. Even web searching was fruitless

I think, and I could well be wrong, that a module is not compiled until it is referenced by a some code. This assumes the code has not be manualled compiled or the VBE setting "Compile On Demand" is not set

Thanks Andy I appreciate your input.

So in short, as seemingly illustrated here, only Modules explicitly referenced in the Open Event(s) will be compiled upon Open, ie:

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

on the workbook being opened only Module1 is initially Compiled even though in turn Module1 explicitly references Module2... Module2 will be compiled when Module1 is active in terms of run-time (by which time libraries have been set etc... hence no error)

Interesting stuff (to me at least).

Looks like the compile does not occur until the actual routine is called.

I constructed and compiled the code with the reference to Outlook.
I then removed the reference and stepped through the code from the Open event. No compile error until a routine with an unresolved reference was called

fantata1 - glad you have resolved ! I've learnt something too so thanks for asking the question in the first instance !

Originally Posted by Andy

Looks like the compile does not occur until the actual routine is called.

Hmm... but that would contradict the earlier code example, no ?

ie if you had A_Mod1 called from Workbook_Open but called after the Library was added it would still debug even though by the time A_Mod1 is physically called the references should resolve given the libraries have been added.

Or am I missing something... is there something specific about how/when the libraries are added mid routine ?

I'm guessing an automation issue of some sort ?

(apologies if (as I suspect I am) I am missing the point...)

If I extend the Open event with this I still do not get an error.

Please Login or Register  to view this content.

The reference is in place before any of the "A" routines get called.
Note: you will not be able to step through the code this time.

Originally Posted by AndyP

The reference is in place before any of the "A" routines get called.

I guess I'm back to square one...

On that basis why does my earlier attachment debug when the subroutine test is called from the open event given it is only called after the routine which is adding the Word library ?
(the obvious implication is that the Module in which test is compiled before the library is available and thus debugs)

However I just tested your earlier file and moved the library addition code to a further subroutine (so as to mimic setup of my earlier sample file) and your file still works without issue

So by powers of deduction I can only assume the issue relates to the nature of the EarlyBindLibrary routine as used in my earlier file (not GUID based).

Any ideas ?

Your Public variable is the problem I think. In order for the ThisWorkbook module to compile, the editor has to get the address of the Test function. It would appear that while doing this, although it doesn't check inside the procedures in that module, it does attempt to check any public or module-level variables.

Bingo, thanks R ... in all honesty I thought I'd checked that earlier - ie this was why I had added a non-Public variable to my test routine (so as to be able to toggle) but I guess for whatever reason that one slipped past me.

Mystery resolved. Onwards and downwards.