more newbie help on my significant figures function...

[James Bond]

Thanks to all who have already helped with my little significant figures
function. I have ended up with this little function:

Function Sig(Num, Dig)
Sig = WorksheetFunction.Round(Num, Dig - 1 - Int
(WorksheetFunction.Log10(Abs(Num))))
End Function

There is only one thing left for my function to do and I need some help
deciding the best way to go about it.

The one thing my significant figures function does not do is properly add
zeros to the right of a number when needed. For example, the number 5,
when expressed in 3 significant digits, would be 5.00.

Now first I am going to have to make my function determine when it needs
to actually add zeros and then append them, but I assume that even when I
figure out how to do that, I will run into a problem with formatting
since the general format will not show any trailing zeros (yet I don't
want to have to specifiy ahead of time a number formatting with x decimal
places).

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.

So basically, before I start figuring out how to do all this, I was
hoping for some feedback regarding my plan. If there are better/easier
ways to achieve this I would like to hear about it.

TIA

James

 

[Tom Ogilvy]

if you plan on having a cell have a formula like:

=Sig(1234.567,10)

then your function won't be able to format the cell. User defined function
used in a cell can only return a value to the cell in which they are
displayed. The can not change formatting, set the value in other cells or
otherwise alter the environment. The best it could do (ifyou want to pad
with zeros) would be to display the results as a string.

 

[Ron Rosenfeld]

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

 

[James Bond]

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

 

[Tom Ogilvy]

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.

 

[Ron Rosenfeld]

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?

I don't know why the designers made those decisions. And I don't know enough
about designing something like this to even be able to speculate.


--ron

 

[Harald Staff]

Hi Ron

I believe this has to do with keeping track of the order of calculations. A
sub or a vba function ran from code is capable of doing anything and
everything. Which quite frequently might be to alter stuff that would change
the input values to the function. So Excel would have to evaluate what every
piece of code might be up to before calculating.

So it's a law: A function called from a cell can not alter the environment,
it can only return a single value to its own cell. But there's a general
Calculate event that can call macros. If one is extremely sure about what's
going on now and forever, one might use this. Wouldn't recommend it for
anything but extremely coded & overprotected workbooks though.

Best wishes Harald

 

[Ron Rosenfeld]

Hi Ron

I believe this has to do with keeping track of the order of calculations. A
sub or a vba function ran from code is capable of doing anything and
everything. Which quite frequently might be to alter stuff that would change
the input values to the function. So Excel would have to evaluate what every
piece of code might be up to before calculating.

So it's a law: A function called from a cell can not alter the environment,
it can only return a single value to its own cell. But there's a general
Calculate event that can call macros. If one is extremely sure about what's
going on now and forever, one might use this. Wouldn't recommend it for
anything but extremely coded & overprotected workbooks though.

Best wishes Harald

Interesting. And there are certainly workarounds for the problem in the
existing methods.


--ron