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

[beasty1711]

im getting

Compile Error:
Can't Find Project Or Library

from this line of 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]

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

[beasty1711]

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]

Coudn't reproduce your error but try this:

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



JO

[McGenius]

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]

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]

I'm presuming you are accessing Excel from VB.

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

[beasty1711]

nah its an excel macro :eek: 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]

thats for everyones help
but i managed to solve by doing

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]

... that was written in excel xp ...
I see tons of problem now. That file should've been saved as Excel97 for compatibility.

[TheBionicOrange]

Instead of


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


try ...


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

[grant5]

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:
If format(CSsht(CStar, 1)) = format(CSsht(CStar + 1, 1)) Then: GoTo FoundMatch
Thanks for any help!

[Hack]

Moved To Office Development