Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a 'routine'
Please can someone tell me how easy/difficult this is...
I need to create a routine whe 1. The values of a number of cells in my spreadsheet are built into an equation. 2. The equation is run repeatedly until the value = 0 3. The information I need is the number of times it is run before value = 0 I have done this 'long hand' but would like it built into the function contained within one cell. What are the key functions I need to be looking at in order to create this? Thank you. James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a 'routine'
It would help to know the exact calculation you need to do, but here is what
I have in mind - you could use the function CountIterations in your worksheet cell: Public Function CountIterations(Parameter1 as ???, Parameter2 as ???...) as Integer Dim CountCalls as Integer, Result as ??? CountCalls = 0, Result = 1 While Not(Result = 0) Result = MyFunction(Parameter1, Parameter2, ...) CountCalls = CountCalls + 1 Wend CountIterations = CountCalls End Function Public Function MyFunction(Parameter1 as ???, Parameter2 as ???...) as ??? 'Put your function code in here End Function A few notes: 1) Since I did not know the proper variable types, I used ??? 2) I assume the function may change the values of the cells used for calculation, since otherwise it would continue to return the same value and never converge to zero - so your parameters may need to be the actual cells (Cell1 as Range, Cell2 as Range) passed ByRef so that you can not only use the values but change their contents. 3) WARNING: If your function returns a Single or Double variable type you may need to use a different end condition than Result = 0: this is because rounding inaccuracies may keep the value from ever equalling exactly zero (this is a common and well known issue with recursive calculations on computers). You might need your condition to be something like Abs(Result)<=0.00001 or something like that. "jamesmcg69" wrote: Please can someone tell me how easy/difficult this is... I need to create a routine whe 1. The values of a number of cells in my spreadsheet are built into an equation. 2. The equation is run repeatedly until the value = 0 3. The information I need is the number of times it is run before value = 0 I have done this 'long hand' but would like it built into the function contained within one cell. What are the key functions I need to be looking at in order to create this? Thank you. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA routine (101) | Excel Programming | |||
creating a routine to help memorize | Excel Programming | |||
SUB ROUTINE | Excel Programming | |||
Routine?? | Excel Programming | |||
Need VBA Routine | Excel Programming |