Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
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. -- Regards, Tom Ogilvy "James Bond" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
On Mon, 30 Aug 2004 12:27:43 GMT, James Bond wrote:
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
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" skrev i melding ... On Mon, 30 Aug 2004 12:27:43 GMT, James Bond wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
more newbie help on my significant figures function...
On Tue, 31 Aug 2004 16:12:13 +0200, "Harald Staff"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i show 3 significant figures | Excel Discussion (Misc queries) | |||
significant figures | Excel Worksheet Functions | |||
significant figures? | Excel Discussion (Misc queries) | |||
Rounding/Significant figures | Excel Worksheet Functions | |||
pls help newbie with my significant figures function | Excel Programming |