FORMAT Statement

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

In Excel VB, I want to take the current month (DIMed as
Integer) and convert it to a string that always has two
bytes (11 to "11" or 5 to "05"). I thought I could use
the FORMAT function - FORMAT(CurrMo, "00"), but I get a
compile error that says "Wrong number of arguments or
invalid property assignment". Am I leaving something
out? Or is there another function I should use?
 
Hi Bill
format(1,"00")
for example works for me. You may post the relevant code in total
(maybe there's a wrong assignment, etc.)
 
Here is code I wrote to test different scenarios. Even
the FORMAT(2, "00") gives me the error.

Sub TestIt()
Dim CurrMo As Integer
Dim xx As Integer
Dim WriteIt As String
CurrMo = 11
xx = 4
WriteIt = "The result is " & Format(2, "00") & Format
(CurrMo, "00") & Format(xx, "00")
MsgBox (WriteIt)
End Sub
 
Hi Bill
this works without any problems for me!
try entering the code
?FORMAT(2, "00")
in the iimmediate window. Does this also raise an error?
 
Not sure what you mean by 'immediate window'. If you
mean the code window, when I enter ?FORMAT(2, "00"), it
changes it to "Print FORMAT(2, "00")" (which gives a
different error if you try to run).

If it helps, FORMATCURRENCY(...), FORMATNUMBER(...), etc.
seem to work fine.
 
You can see the immediate window by hitting ctrl-G when you're in the VBE.

It's where the debug.print statements show up--nice for testing/debugging your
code.

And any chance you have your own function called Format?

If I did:

Function Format(a as variant) as variant
'some dummy code here
end Function

I'd get that same error.

Try this (but it's just for testing!).

Option Explicit

Sub TestIt()
Dim CurrMo As Integer
Dim xx As Integer
Dim WriteIt As String
CurrMo = 11
xx = 4
WriteIt = "The result is " & VBA.Format(2, "00") _
& VBA.Format(CurrMo, "00") & VBA.Format(xx, "00")
MsgBox (WriteIt)
End Sub

If that works, find that format Function! It would scare me having a function
like that hanging around. (Naming it to myFormat would be ok for me--just not a
VBA term.)
 
You guys are great.

I found the immediate window and tried the code - same
error.

I used the VBA.xxx and it worked.

I couldn't imagine having created my own 'function' named
FORMAT - I probably haven't created three since I started
this job. But, lo and behold, I had created a macro
named Format several months ago. So - thank you both for
the help.
 
In another private response, Bill wrote that he renamed his custom Format
function to something else.
 
Back
Top