Compile Error: with VBA call to "Format"

jc

unread,

Sep 10, 2009, 1:08:15 PM9/10/09

to

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

Bob Phillips

unread,

Sep 10, 2009, 1:24:38 PM9/10/09

to

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

--
__________________________________
HTH

Bob

"jc" <j...@discussions.microsoft.com> wrote in message
news:2EF24466-850A-498E...@microsoft.com...

Barb Reinhardt

unread,

Sep 10, 2009, 1:37:04 PM9/10/09

to

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

unread,

Sep 10, 2009, 1:50:04 PM9/10/09

to

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.

--
__________________________________
HTH

Bob

"Barb Reinhardt" <BarbRe...@discussions.microsoft.com> wrote in message
news:1D8E3F4C-1CD9-4BC5...@microsoft.com...

EricG

unread,

Sep 10, 2009, 1:57:28 PM9/10/09

to

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

unread,

Sep 10, 2009, 2:06:34 PM9/10/09

to

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

Bob Phillips

unread,

Sep 10, 2009, 2:30:03 PM9/10/09

to

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

--
__________________________________
HTH

Bob

"jc" <j...@discussions.microsoft.com> wrote in message

news:4D6FFF83-9B76-46FE...@microsoft.com...

Jim Thomlinson

unread,

Sep 10, 2009, 3:07:09 PM9/10/09

to

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.
--
HTH...

Jim Thomlinson

Dave Peterson

unread,

Sep 10, 2009, 3:57:26 PM9/10/09

to

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

--

Dave Peterson

Chip Pearson

unread,

Sep 10, 2009, 4:29:06 PM9/10/09

to

>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)