References in VBA (Tools - References)

[Alan]

Hi All,

In the spirit of curiosity, I am just wondering what would happen if I
were to go and set every single reference in excel under tools -
references?

I assume it would mean that every single object model would become
available (early bound?), but what would be the downside?

Would my PC grind to a crawl and / or fail to ever open excel again?

I could just try it, but I don't want to have to reinstall Excel,
Office, or even Windows if it goes badly wrong!

Just to be clear, I don't have any particular need to do this - I am
just wondering.

Thanks,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[Bob Phillips]

This is hypothetical of course, because no sane person would ever do this
).

Assuming that Excel is still standing after this, I don't think you would
encounter any particular problems. But if you pass the workbook to anyone
else, and they don't have a particular file referred to, they might well get
problems.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Alan]

This is hypothetical of course, because no sane person would ever do
this ).

Absolutely - it was in the spirt of academic interest only ;->

{Laughs maniacally}

Now, purely hypothetically, I wonder if you could you programmatically
create a reference in the VBE on the fly?

Assuming that Excel is still standing after this, I don't think you
would encounter any particular problems. But if you pass the
workbook to anyone else, and they don't have a particular file
referred to, they might well get problems.

So you mean, for example, if I have a reference to, say, Outlook 9.0
(Outlook 2000) but the other person has Outlook 2003 (Outlook 11.0 ?)
installed on their machine (and not OL2000) , and therefore
MSOUTL9.OLB file does not exist on their machine, the workbook might
throw a wobbly?



On the general issue, I have used 'late binding' - if that is the
correct term - (CreateObject) to avoid the issue of other users having
references set or not.

The only problem with that is you don't seem to be able to explicitly
declare variables as type, and hence the intellisense does not kick in
and help out which is a big downer for a relative VBA beginner like
me.

I guess I could write the code using (early?) binding, then finally
delete the DIM statements and use CreateObject rather than NEW once I
am done.

Would that be regarded as 'best practice' in order to maximise
portability across machines?


Thanks for your interest!

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[Chip Pearson]

Yes, you can create references programmatically on the fly. See

ThisWorkbook.VBProject.References.AddFromFile
ThisWorkbook.VBProject.References.AddFromGuid


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com