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.

--
with kind regards

Spike

Spike;525829 Wrote:
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.

--
with kind regards

Spike

The *Format *function returns a string. You've specified the -datCFwd
-as a *Date *type variable and the date will always be held that way in
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 more
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 formatting
comes in when humans see it - so where, ultimately, is this formatted
date going to be seen by humans?
To solve your immediate problem, put the formatted date into a string
variable, or (the above seems to be a long way to get to this) just
leave off the *As Date* at the top of your existing code.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144305

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



"Spike" wrote:

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.

--
with kind regards

Spike