Option Explicit and Date Format "dd mmm yyyy"?

[TroyB]

Hi,

I have the following extract of code requiring the user to input the date
into a prompt and then inserted into the spreadsheet.



Sub DateTest()

dateformat = Format(Now(), "dd mmm yyyy")

PayDate = Application.InputBox("Input Pay Date to appear on Payslips", "DATE
REQUIRED", dateformat)

Range("A1").Value = PayDate

End Sub



I have recently been educated that using the "Option Explicit Statement" is
a good way to ensure declaration of all variables and i tend to agree with
this philosophy as the code is becoming larger (and complex!). But when i
use the following code (including the declaration of variables), i can't get
it to provide the date format i require, ie "dd mmm yyyy".



Option Explicit

Sub DateTest()

Dim PayDate As Date, DateFormat As Date

DateFormat = Format(Now(), "dd mmm yyyy")

PayDate = Application.InputBox("Input Pay Date to appear on Payslips",
"DATE REQUIRED", DateFormat)

Range("A1").Value = PayDate

End Sub



Using Option Explicit, how can i provide a default input of todays date in
"dd mmm yyyy" format and, following the user input insert the date into the
spreadsheet in the format "dd mmm yyyy"?



Thanks in advance



Boeky

 

[Rob van Gelder]

Datatype conversion is where a lot of bugs occur.

In your case, you're handing the responsibility of datatype conversion to
Excel in two places:
The Format function returns a string, which you are trying to assign to a
date.
Application.InputBox returns a string, which you are trying to assign to a
date.

When you let the compiler (Excel) do the datatype conversion for you, you
had better be sure you know how Excel is going to do it.


Simple answer to your question:

DateFormat = Now()

PayDate = Application.InputBox("Input Pay Date to appear on Payslips",
"DATE REQUIRED", Format(DateFormat, "dd mmm yyyy"))

 

[Bob Phillips]

You still need to format the result

Range("A1").Value = Format(PayDate, "dd mmm yyyy")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob van Gelder]

Excel will still treat the string as a date, so you get the same result.
Better effort spent formatting the cell to dd mmm yyyy

 

[Bob Phillips]

Not for me it didn't, I either had to format the cell or coerce it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob van Gelder]

I hate to extend a thread further than it has to, but I'm confused.

PayDate is a Date variable
and you're saying that Range("A1").Value = PayDate does not result in A1
being a date?

I see no coersion here.

Sub test()
Dim PayDate As Date

PayDate = Now

Range("A1").Value = PayDate

Range("A1").NumberFormat = "dd mmm yyyy"
End Sub

 

[Bob Phillips]

No, I am not saying it wasn't a date, I am saying that as given the input
box date format was dd mmm yyyy, but the cell format was not necessarily
that (dd-mmm-yy in my case), so I coerced the format.

Thus to get the cell formatted as the OP wanted (which I accept was not
explicitly stated one way or the other, but a reasonable assumption), then
you either force the format in the code, or format the cells. I prefer the
former as it is more controlled.

Your original code did not do the latter, your latest post did.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Stephen Bullen]

Hi Bob,

Thus to get the cell formatted as the OP wanted (which I accept was not
explicitly stated one way or the other, but a reasonable assumption), then
you either force the format in the code, or format the cells. I prefer the
former as it is more controlled.

But it only works when the format is unambiguous, such as the "dd mmm yyyy"
in this example. If the OP had wanted dd/mm/yyyy, your code would have
resulted in the day and month being transposed. Hence, I consider the latter
"More controlled" - pass the number to Excel as a Double, then format the
cells to tell Excel how you want it represented.

There's much more about these sorts of issues at
http://www.oaltd.co.uk/ExcelProgRef/Ch22

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Rob van Gelder]

I still don't get your logic.

You still need to format the result

Range("A1").Value = Format(PayDate, "dd mmm yyyy")

Do you really expect that code to change the cell formatting - or is it a
typo?

 

[Rob van Gelder]

I agree - more controlled to pass variable as numeric.
However, I write .Value from a VBA Date datatype. Is there an advantage to
convert to Double - or does it get converted to Double anyway (behind the
scenes)?

 

[Stephen Bullen]

Hi Rob,

I agree - more controlled to pass variable as numeric.
However, I write .Value from a VBA Date datatype. Is there an advantage to
convert to Double - or does it get converted to Double anyway (behind the
scenes)?

If you write a Date data type to a cell, Excel also gives the cell a default
'date' number format. Similarly, if you write a string that looks like a
date, Excel gives it a format something like that used in the string. Both
those waste a few cycles if you then want to format the cell properly. I
tested it with the following (in which my regional settings are DMY order):

Sub Test()

'Got 38300.45299
Range("A1").Value = CDbl(Now())

'Got 09/11/2004 10:52
Range("A2").Value = CDate(Now())

'Got 09-Nov-04 (notice, NOT the same as the date string entered!)
Range("A3").Value = "9 Nov 2004"

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Rob van Gelder]

Thanks for the info

 

[Bob Phillips]

Sorry, I see where you are now.

I was thinking, but not saying

With Range("A1")
.Value = PayDate
.NumbedrFormat = "dd mmm yyyy"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob van Gelder]

You were shaking my already shaky understanding of Excel's date handling!

Glad thats sorted. Cheers

 

[Bob Phillips]

When in doubt on dates, ask Norman (Harker that is).

Bob