creating a 'routine'

[jamesmcg69]

Please can someone tell me how easy/difficult this is...

I need to create a routine where:

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

[K Dales]

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 where:
>
> 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
>
>