Compile Error: with VBA call to "Format"

  • Thread starter Thread starter jc
  • Start date Start date
J

jc

In trying to run a VBA macro on a EXCEL 2002 machine that has work on other
EXCEL 2003 machines.

The code interupts with:

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

on hitting OK it goes into the VBA editor.

The word "Format" is highlited

What do I need to get format to work?
 
In the VBIDE, goto Tool>References. There you will see an item with MISSING
in its text. Uncheck it.
 
I've never done any VBA with 2002, but I wonder of Format is supported. You
could try to use WorksheetFunction.Text to see if that works.

HTH,
Barb Reinhardt
 
I don't have XP on this machine Barb, but I do have 2000, and format works
there, so it is bound to work in XP.
 
Check under "Tools/References" in the Visual Basic Editor to see if you have
any bad references. This could have happened if the VBA was written on a
machine with 2003, and then moved to a machine with 2002.

HTH,

Eric
 
That is not the problem, it is a detached reference having a knock-on
problem. Just uncheck the MISSINGs as I said.
 
There is no specific library for Format. If you have a missing reference then
it will highlight a function at random and throw the error. When you open
your file on a 2002 machine under references in VBA -> Tools -> References
one of the references will be tagged as "Missing:". Any reference to one of
the other MS Office programs such as Word or Access is a likely candidate.
 
And the missing reference may not have anything to do with the line that's
causing the error.
 
There is no specific library for Format.

Actually, there is. It is the VBA library and the Format function,
like any function, can be overridden by a function in a library with
higher precedence.

Function Format(X As Double) As Double
Format = X * 2
End Function

Sub AAA()
' two very different Format functions
Debug.Print Format(10)
Debug.Print VBA.Strings.Format(10)
End Sub





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