***Resolved***Format function causing probs in excel 97 VBA

**beasty1711** · Jan 23rd, 2003, 08:42 AM

im getting

Compile Error:
Can't Find Project Or Library

from this line of code

VB Code:

MMO_FP.tv_ratings.Value = Format(Sheet6.Cells("c3").Value, 0)

all i'm trying to do is take the decimal places off the number in cell C3

any ideas?

**McGenius** · Jan 23rd, 2003, 08:47 AM

In your case use this:
MMO_FP.tv_ratings.Value = Format(Sheet6.Cells("c3").Value, "0")

**beasty1711** · Jan 23rd, 2003, 08:52 AM

tried that, but it still comes up with the same error message with the format word highlighted....
i'm on SP2 with excel aswell

**joltremari** · Jan 23rd, 2003, 09:32 AM

Coudn't reproduce your error but try this:

MMO_FP.tv_ratings.Value = Format(Sheet6.Cells(3, 3).Value, 0)

JO

**McGenius** · Jan 23rd, 2003, 09:33 AM

Sorry, I mssed that error desc ... In Excel's VB Editor go to Tools > References and check if Visual Basic For application is selected.

**techgnome** · Jan 23rd, 2003, 09:39 AM

Last time I saw this, it was because of a problem of "in"compatible version of (of all thins) the Common Control OCX (comctl.ocx)...... double check all your references. Make sure all is OK.

**TheBionicOrange** · Jan 23rd, 2003, 10:05 AM

I'm presuming you are accessing Excel from VB.

If so have you got the Excel library referenced in your project ?

**beasty1711** · Jan 23rd, 2003, 10:14 AM

nah its an excel macro

that was written in excel xp and now i have been given the nice job of debugging it for excel 97

i've checked the referances and they all look fine

the versions of the control are the same on my PC Excel xp and 97 and another PC which is 97 only

**beasty1711** · Jan 23rd, 2003, 10:29 AM

thats for everyones help
but i managed to solve by doing

VB Code:

Range("c3").NumberFormat = "0"
MMO_FP.tv_ratings.Value = Range("c3")

i know its not the best way.. but afterall this is an excel macro.. and who said that macros were the best way :P

**McGenius** · Jan 23rd, 2003, 10:32 AM

... that was written in excel xp ...

I see tons of problem now. That file should've been saved as Excel97 for compatibility.

**TheBionicOrange** · Jan 23rd, 2003, 10:35 AM

Instead of

Code:

MMO_FP.tv_ratings.Value = Format(Sheet6.Cells("c3").Value, 0)

try ...

Code:

MMO_FP.tv_ratings.Value = Format(Sheet6.Range("c3").Value, 0)

**grant5** · Jul 2nd, 2010, 01:10 PM

I know this thread title says, resolved, but it looks like it was resolved by using different code, not by finding the root cause.

I think I am having a similar same problem. I am running VBA for Excel 2007, although the files in question are saved as 97-2003 format.

I have two identical pieces of code in two different excel files, one was copied from the other. In the code I use the "Format" function to convert a number to a string without the leading placeholder for sign.

In the original file, this macro runs without any problems. In the copied version, VBA doesn't want to recognize the "Format" function. I get the following:

Compile error:

Wrong number of arguments or invalid property assignment

When I look at the code, the "F" in format has been forced back to lower case, whereas in the original file it is forced to upper case indicating a recognized function.
Both files have the same references selected.

Anyone know why VBA would recognize the "Format" function in one excel file and not another?

For reference, the line of code in question is:

Code:

If format(CSsht(CStar, 1)) = format(CSsht(CStar + 1, 1)) Then: GoTo FoundMatch

Thanks for any help!

**Hack** · Jul 2nd, 2010, 01:19 PM

Moved To Office Development

Thread: ResolvedFormat function causing probs in excel 97 VBA

Thread Tools

Display