Are you aware of any other function that changes formatting when it returns
the results? So consistency would be one factor. In any event, it is a
restriction imposed by Excel. Calling a sub from a function does not
overcome that limitation.

--
Regards,
Tom Ogilvy

"James Bond" wrote in message
...
Ron Rosenfeld wrote in
:

On Mon, 30 Aug 2004 01:36:43 GMT, James Bond wrote:

So I was thinking one way to do this would be to have my VBA function
determine if zeros need to be added and then change the formatting of
the active cell on the fly to accomodate that number of decimal
places. Now I don't really know exactly how to make all that happen
yet, but that is the direction I am heading.

A function cannot change the formatting of the cell.

Two possibilities:

1. Output a text string with the necessary formatting.
2. Use an event macro, probably the 'calculate' one to adjust the
formatting after your UDF has done it's work. You'd need to be able
to define the range in which you might have the results, for this to
work, unless you want to check every cell in the worksheet for your
formula each time you do a calculation.



--ron


Thanks for the input everyone. It seems I cannot do what I was hoping
for. So I gather from what you are all telling me that I cannot call a
method from within a function.

To help my understanding of all this, can you tell me in a little more
detail why I can't do that. My thinking was that when excel calls my
function, any code in that function would run (such as a method call to
do some formatting in my case) and as long as excel got a valid return
value it would be happy. So where does my thinking break down?

TIA
James