Setting NumberFormat within a VBA Module
 

I just wrote a function that returns a variant, instanced as either a string
("N/A") or a date. But I noticed it appears as a number in the spreadsheet.
So I figure I could format the cell right within the function, using:

ActiveCell.NumberFormat = "yyyy-mm-dd"

It doesn't work. When I insert after this statement, the following:
debug.print (ActiveCell.NumberFormat)

I find that it is still general. The odd thing is, if I write another
function that only sets the active cell to the proper format, it works fine.

Any ideas?
Dom

A worksheet function cannot set a format, just return a value. You will have
to format the cell.

--

HTH

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


"Dominic Olivastro" wrote in message
ervers.com...
I just wrote a function that returns a variant, instanced as either a
string
("N/A") or a date. But I noticed it appears as a number in the
spreadsheet.
So I figure I could format the cell right within the function, using:

ActiveCell.NumberFormat = "yyyy-mm-dd"

It doesn't work. When I insert after this statement, the following:
debug.print (ActiveCell.NumberFormat)

I find that it is still general. The odd thing is, if I write another
function that only sets the active cell to the proper format, it works
fine.

Any ideas?
Dom

Thanks. I guess I should have known that.

But it also doesn't work if I call it in a subroutine. And if I record a
macro that does nothing but format the cell, it seems to work. For example,
I start the recorder, I format the cell to be the appropriate date-format,
then I end the recorder. Now, when I replay it, the function sets the
format of the cell appropriately. Why does it not work in my subroutine?

Dom

--
Dominic Olivastro
ipIQ, Inc.

web: http://www.ipIQ.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email:
"Bob Phillips" wrote in message
...
A worksheet function cannot set a format, just return a value. You will
have
to format the cell.

--

HTH

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


"Dominic Olivastro" wrote in message
ervers.com...
I just wrote a function that returns a variant, instanced as either a
string
("N/A") or a date. But I noticed it appears as a number in the
spreadsheet.
So I figure I could format the cell right within the function, using:

ActiveCell.NumberFormat = "yyyy-mm-dd"

It doesn't work. When I insert after this statement, the following:
debug.print (ActiveCell.NumberFormat)

I find that it is still general. The odd thing is, if I write another
function that only sets the active cell to the proper format, it works
fine.

Any ideas?
Dom

I think I would need to see the code, and how you tested it to guess at
that.

--

HTH

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


"Dominic Olivastro" wrote in message
ervers.com...
Thanks. I guess I should have known that.

But it also doesn't work if I call it in a subroutine. And if I record a
macro that does nothing but format the cell, it seems to work. For
example,
I start the recorder, I format the cell to be the appropriate date-format,
then I end the recorder. Now, when I replay it, the function sets the
format of the cell appropriately. Why does it not work in my subroutine?

Dom

--
Dominic Olivastro
ipIQ, Inc.

web: http://www.ipIQ.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email:
"Bob Phillips" wrote in message
...
A worksheet function cannot set a format, just return a value. You will
have
to format the cell.

--

HTH

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


"Dominic Olivastro" wrote in message
ervers.com...
I just wrote a function that returns a variant, instanced as either a
string
("N/A") or a date. But I noticed it appears as a number in the
spreadsheet.
So I figure I could format the cell right within the function, using:

ActiveCell.NumberFormat = "yyyy-mm-dd"

It doesn't work. When I insert after this statement, the following:
debug.print (ActiveCell.NumberFormat)

I find that it is still general. The odd thing is, if I write another
function that only sets the active cell to the proper format, it works
fine.

Any ideas?
Dom