Solved: Format problem

[jwise]

I'm having a problem with VBA's FORMAT routine. Here's the code, expunged of the real stuff:


Sub fmtData()
'
' Code produces incorrect output
'

Cells(1, 2) = Format(Cells(1, 1), "$###,##0") 'OK
Cells(2, 2) = Format(Cells(2, 1), "$###,##0") 'OK

Cells(3, 2) = Format(Cells(3, 1), "mmm yyyy") 'Here is the problem
Cells(4, 2) = Format(Cells(4, 1), "mmm-yyyy") ' And here

Cells(6, 2) = Format(Cells(6, 1), "0.00%") 'OK


End Sub

Please put the following in the cells:
A1 = 123456
A2 = 123
A3 = 3/10/2006
A4 = 4/28/2007

A6 = "=5/8"

I am only concerned with A3 and A4 which are dates. On this XL2000, my result is "1Mar" for A3 (in cell B3) and "1Apr" for A4 (in cell B4). The dollars test in A1 and A2 works as expected, and so does the percentage in A6.

Any ideas as to what I've done wrong?

Update: I copied the code out of VBE and into a regular text file. Then I wrote this query. To test it again, I did the whole thing over (after stopping and starting XL2000 again.

It worked as described on the second try. What gives?

[jammer6_9]

try this...


Cells(3, 2) = Format(Cells(3, 1), "d/mmm/yyyy")
Cells(4, 2) = Format(Cells(4, 1), "d/mmm/yyyy")

[jammer6_9]

More set of examples

Dim MyTime, MyDate, MyStr
MyTime = #5:04:23 PM#
MyDate = #1/27/1993#
' Returns current system time in the system-defined long time format.
MyStr = Format(Time, "Long Time")
' Returns current system date in the system-defined long date format.
MyStr = Format(Date, "Long Date")
MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday,
' Jan 27 1993".
' If format is not supplied, a string is returned.
MyStr = Format(23) ' Returns "23".
' User-defined formats.
MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00") ' Returns "334.90".
MyStr = Format(5, "0.00%") ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello".
MyStr = Format("This is it", ">") ' Returns "THIS IS IT".

[jwise]

The FORMAT routine was just what I needed. Thank you for your suggestion.

To make my document look better, I wrote a Function which basically stretched the text field (or string) to be a longer string. I concatenated some blanks to the start and end of the field, e.g. "12.45%" became " 12.45% ". Is there a built-in function which will lengthen a string, hopefully by padding it with blanks on either end or both ends?

Please understand that this is really a question about how you use the documentation.

It is my understanding that you can use Excel functions in VBA. For example, how do I use VLOOKUP in VBA? I searched several texts, but I did not use the correct search words.

[Paul_Hossler]

jwise --

This seems to work and pads with spaces:

Sub aaa()
ActiveCell.NumberFormat = "0.0% "
End Sub



Also WorksheetFunction in help explains how to call a VLookup, and others

Help's example (not every function can be used this way, but most of the useful one can -- there's a list in help)


Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer

[jwise]

Thanks for all replies.

I have been able to get this to work.