VBA: format, Format or FORMAT

[Guest]

The following code, after using for over 2 years, suddenly gives error.
Hi-lites FORMULA at line with >>>>>>
Compile Error: Wrong number of arguments or invalid property.
Checked same code on my test computer, on numerous files and it works great.
Tried changing FORMULA to lower case - It auto flips back to Upper Case.
Deleted code, and replaced it with working copy from test computer.
As soon as I tried it, it again came up with same error and it had flipped
'Formula' to 'FORMULA'.
I also tried it after completely closing all files and Excel, Re-opening
Excel, open one file that I know worked on my test machine. Still the same
problem.
Any suggestions would be greatly appreciated.
Thank You

Sub FooterInsertFormat()
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup.CenterFooter = ""
.RightFooter = "&""Arial,Bold""&8&F / &A" & Chr(10) & "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.6)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
End With
End Sub

 

[Guest]

I have not used that method for a long time but does the Format function not
need to be inside the quotes also, like:
.LeftFooter = "&""Arial,Bold""&8 & FORMAT(Date, "mmm, dd, yyyy")" & Chr(10)
& "JFS"

 

[Dave Peterson]

Look to see if you created a function named FORMULA.

Make sure you don't have any module names that are FORMULA.

 

[acampbell012]

I did split the line for readability in the window but, worked fine for
me.

Sub FooterInsertFormat()
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftFooter = "&""Arial,Bold""&8" & _
Format(Date, "mmm, dd, yyyy") & Chr(10) & "JFS"
.CenterFooter = ""
.RightFooter = "&""Arial,Bold""&8&F / &A" & Chr(10) & "Page &P
of &N"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.6)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
End With
End Sub

 

[Dave Peterson]

Change all those FORMULAs to FORMAT.

(Stupid fingers!)

 

[Guest]

Disregard, the last message. I just tried the code and it worked fine, no
error message.

 

[Guest]

One more thing to check. If you have used the term "FORMAT" elsewhere in
your code VBA will remember it and will automatically change subsequent
occurences to the same case.

 

[Guest]

Thanks All:

JLGWhiz: I'll keep your note in mind for future.

Dave: Right on the money (again), in spite of those stupid fingers.

I should have posted a bit sooner - Maybe after 1/2 Day instead of 1-1/2 days
of futile, ignorant searching.

Thanks again.