problem formatting a variable

[Spike]

I nave a problem formatting a variable. If i run the following in a sub it
has no effect on the variable value, however if i pass the variable to
another sub and run the same formatting code on it it does it as i want.

dim datCFwd as date

datCFwd = Sheets("Model").Range("Model_AccountingEnd").Value
datCFwd = Format(datCFwd, "dd mmm yy")

the range Sheets("Model").Range("Model_AccountingEnd") is custom formatted
as "dd-mmm-yy" but the variable actuallly holds the value as "dd/mm/yy" and
this does not change unless it is passed to another sub as explained above

Any ideas to correct this will be very gratefully received.

 

[p45cal]

I nave a problem formatting a variable. If i run the following in a su
it
has no effect on the variable value, however if i pass the variable to
another sub and run the same formatting code on it it does it as
want.

dim datCFwd as date

datCFwd = Sheets("Model").Range("Model_AccountingEnd").Value
datCFwd = Format(datCFwd, "dd mmm yy")

the range Sheets("Model").Range("Model_AccountingEnd") is custo
formatted
as "dd-mmm-yy" but the variable actuallly holds the value as "dd/mm/yy
and
this does not change unless it is passed to another sub as explaine
above

Any ideas to correct this will be very gratefully received.

The *Format *function returns a string. You've specified the -datCFw
-as a *Date *type variable and the date will always be held that way i
it (look at the -Locals pane- while stepping through the code).
If you pass the date to a sub with the likes of:
test datCFwd
and the *test *sub is:
Sub test(xxx)
Debug.Print xxx
End Sub
then the variable *xxx* becomes a -Variant/Date- - a bit mor
flexible.
On the other hand if the receiving sub is:
Sub test(xxx As Date)
Debug.Print xxx
End Sub
then the *xxx *variable remains just a pure *Date *variable.

From the code's point of view, a date is just a date, the formattin
comes in when humans see it - so where, ultimately, is this formatte
date going to be seen by humans?
To solve your immediate problem, put the formatted date into a strin
variable, or (the above seems to be a long way to get to this) jus
leave off the *As Date* at the top of your existing code

 

[Joel]

Is the variable declared in one sub and not in another? Is the variable
declared on the variable line oof the Sub?