Compile Error: with VBA call to "Format"

[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?

 

[Bob Phillips]

In the VBIDE, goto Tool>References. There you will see an item with MISSING
in its text. Uncheck it.

 

[Barb Reinhardt]

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

 

[Bob Phillips]

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.

 

[EricG]

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

 

[jc]

does any one know specifically which reference is need to run the format
function in VBA.

 

[Bob Phillips]

That is not the problem, it is a detached reference having a knock-on
problem. Just uncheck the MISSINGs as I said.

 

[Jim Thomlinson]

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.

 

[Dave Peterson]

And the missing reference may not have anything to do with the line that's
causing the error.

 

[Chip Pearson]

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)