Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difference in the listing of the New module addition in 2002 version | Excel Discussion (Misc queries) | |||
Access Module coded converted to Excel Function | Excel Discussion (Misc queries) | |||
Setting macro security level to "low" permanently? | Excel Discussion (Misc queries) | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) | |||
Is there any specific setting for MS excel to download CSV in fin. | Excel Discussion (Misc queries) |