A recent comment reported a problem with the use of the UCase and LCase (upper and lower case) functions in VBA. The function was returning the error message “Compile error: Can’t find project or library”.
I found the same problem reported at: http://answers.microsoft.com/en-us/office/forum/office_2007-customize/ucase-problem/2a170fd6-b594-48aa-972f-cdf05a05b99b , which gave the simple solution of adding “VBA.” before UCase or LCase. It seems that the problem only occurs on some computers, and all instances I have seen reported have been with VBA routines that link to compiled dll files.
I have now updated my two spreadsheets where I know this problem has occurred. The new files can be downloaded from:
Anyone having a similar problem with any of my other download files, please leave a comment here.
Also note that the same problem may occur with other VBA string functions, and see the comment from Jon Peltier below, regarding base cause of the problem, and alternative method to fix it.
Newton Excel Bach, not (just) an Excel Blog 
More accurately, you shcould add “Strings.” before the offending command. Any of the Strings functions can be blamed for this: UCase, LCase, Left, Right, Mid, etc.
What is happening is that some library is missing. When this happens, VBA usually can determine which it is and usually correctly identifies the member of the missing library. But sometimes it gets confused, and it blames something else, and that’s usually a member of Strings.
So mosey on over to Tools > References, and look for any library prefixed with “MISSING”. That’s the real culprit.
If this doesn’t fix the problem, then close Excel, clean out the temp directory, clean out %appdata%\Roaming\Microsoft\Forms, then restart Excel. If that doesn’t help you probably need to repair/reinstall Office.
LikeLike
Thanks for the comments Jon.
Good point about the other string functions.
I’ve not actually encountered this problem myself, but adding VBA. before string functions seems like an easy way to avoid it, where it works. Do you know of any down side to doing this?
By the way, the original poster of the link in the post suggests that his problem occurred in only some of a group of computers, all of which had the same references.
LikeLike
There may be a problem with references on the handful of computers, or perhaps different versions of Excel & Windows, different updates, etc.
Note also that the references are attached to the VB projects, so references must be checked with the offending projects active in the VBIDE.
The downside of using the prefix of VBA or Strings is masking a problem which appears to happen with Strings functions but may cause its real problem later when its out of your hands.
LikeLike
Pingback: Excel Roundup 20140310 | Contextures Blog
I had the problem with LEFT function (and every other string functions also). Even if VBA.LEFT would solve the problem, it was not logical that 4 machines with same configuration (excel 2003, windows XP ) were not reacting the same all the time.
After many tries, I found out that the problem occurs when the workbook is opened in «READ ONLY» from Livelink / OpenText. The same workbook saved on MyDocuments works perfectly when re-opened. That doesn’t solve the problem, but maybe someone will have an idea where to go from here.
LikeLike
Whenever you open from a temporary/dynamic source (i.e., not from a distinct location on a local hard drive) there are opportunities for things to go wrong. While this is improving, and Microsoft has taken great strides to allow web-based access to shared files, there still may be issues on particular systems.
LikeLike