VBA code for excel 2000 no longer works in excel 2007

[Blaine]

Hello everyone,

A single line of my vba code that I use to use all the time in excel
2000 no longer works in excel 2007. When I try to use the command:

Left(myString, Len(myString) - 1) is get the following error:

"Compile Error: Can't find project or library"

I made sure to use the "Run all items from my computer" when installing
excel 2007, so all libraries should be present.

Any ideas?

Thanks in advanced

 

[Ron de Bruin]

Hi Blaine

If you look in Tools>References in the VBA editor
Do you see a Missing item in the list

 

[Blaine]

Thanks for the reply Ron. For some reason, when I click "Tools", I can
see "References" but it is grayed out so that I can't select it. Do you
know what I might be doing wrong?

Thanks

 

[Blaine]

Ron,

Ok, I have the following check marked under Tools->References:

"Visual Basic for Applications"
"Microsoft Excel 12.0 Object Library"
"OLE Automation"
and
"Microsoft Office 12.0 Object Library"

 

[Ron de Bruin]

If you not see one with "missing" in it it is OK

Do you have the same problem if you try it in another workbook

Sub aaa()
Dim myString As String

myString = "12345"
MsgBox Left(myString, Len(myString) - 1)

End Sub

 

[Blaine]

Ron,

That's a good point. I tried it and it works in another workbook. I
probably should have mentioned this earlier, but the "Left(myString,
Len(myString) - 1)" is buried in an xla file (addin). Any ideas on why
it would work as a direct macro but not in the addin?

 

[Ron de Bruin]

Have checked the references of the add-in ?
Select the add-in project first on the left in the editor

 

[Blaine]

Thanks for your help Ron,

I'm not sure why, but for some reason it started working!

Talk to you later,
Blaine

 

[2007FanFoe]

Hi Blaine,
Two recommendations when this happen:
1 - Clean or decompile the VBA project normally is sufficient.
There're two utility that facilitate this:
http://www.appspro.com/Utilities/CodeCleaner.htm
http://orlando.mvps.org/VBADecompilerMore.asp?IdC=Orlmorewin

2 - Explicit VBA parent objects like this:
VBA.Strings.Left(myString, VBA.Strings.Len(myString) - 1)