Reference for Trim$ ???

[Alan]

I am getting a VBA compilation error on use of the "Trim$" string
function in Excel 2003. Specifically, I get the error message:

Compile error: Can't find project or library

and it highlights "Trim$".

Under References, "Microsoft Word 12.0 Object Library" is listed
as "MISSING". However, I do not think this is related to the
problem.

I developed this using Excel 2007 on another PC, where it
appears to work fine.

Anybody know why this is a problem? Thanks, Alan

 

[RB Smissaert]

Un-tick that reference and if needed set a reference to
the Word library that is available on that machine.
Once there is a missing reference then error messages are
often not pointing in the right direction.

RBS

 

[Dave Peterson]

Open excel
Open your workbook
Go into the VBE
Select your project
Tools|References
Look for MISSING reference.

Uncheck that missing reference.

The missing reference may not (usually doesn't) have anything to do with the
line that caused the error.

 

[Alan]

Thank you. I did not know that.

When I opened this project again and enabled macros, I got 5
"Error in Loading DLL" error message boxes in a row. I changed the
Reference to 11.0, but then it would not let me save the worksheet
("Worksheet not saved"). I closed the worksheet, reopened it and did
not get the errors initially ---- until I went to the Visual Basic
Editor.

In short, I cannot get rid of the missing library. Alan

 

[RB Smissaert]

Did you follow the steps as in Dave Peterson's post?

RBS

 

[Alan]

I had to export all the VBA code, set up a new spreadsheet, go
into VBE and check all the right boxes. Then I imported the code.
This worked fine.

Thanks for the pointers. Alan

 

[Chip Pearson]

Any library marked as MISSING will cause other libraries to get
"lost", even libraries that are fully intact and loaded. Uncheck the
MISSING reference and all will be right in the world.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Alan]

Uncheck the
MISSING reference and all will be right in the world.

Is there a way to load Excel/Word/Office 11.0 object libraries
from VBE running in Excel 2007 and/or programmatically?

I'm telling you that the steps above did not work. Excel would
not save it after being unchecked, it would go right back to being
checked, and it would not save it under a different filename. The
only think that worked is starting over by setting up Excel and VBE
with the right settings and with a new spreadsheet. This was on 2003
I was trying to change it.

In 2007 there was no evident problem, and 11.0 object libraries
were not available under References. Is there a way to load them in
2007 or programmatically?

Thanks, Alan

 

[Dave Peterson]

Personally, I wouldn't use any references in any code that I shared with others.

I'd use late binding.

Some references:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible

and Dick Kusleika has a web page at:
http://www.dicks-clicks.com/excel/olBinding.htm
that explains this with Outlook

==========
I'd develop with the references so that I could get the benefit of the
intellisense and autocomplete within the VBE, but then change to late binding
before sharing with others.