Excel Iteration Program

I have an annoying situation when I each time open the Ms excel file I
have to activate(pressing F2 and entering) the output cell (Col C) which
is having a circular reference with Col A to get the desired value but it is
lost when i reopen it next time and show #value.
The ouput cell C is having a interpolate function (a macro, attached below)
to interpolate from a range of value given separately for the value of
corresponding B
cell which is indirectly having a circular reference with Col A.

To make it clear lets see the File:

The three data range

A1 B1 C1
A2 B2 C2
A3 B3 C3

> Now the three cols are linked by following relation
>
> A = 4 + C
> B = constvalue x A, Here B1 = 1.0 * A1, B2 = 0.9 * A2, B3 = 0.98 * A3
> C = Function of (B)
>
> C value is retrived by interpolation between a range of B & C, Given
> separately, For that a macro has been writeen.

The data range from which C will interpolate the output value for
corresponding B value are as follows:
B C
0.00 0.00
2.00 1.00
4.00 2.00
6.00 3.00
8.00 4.00
10.00 5.00
The solution is C1: C2:C3 = 4.00:3.27:3.84

The macro for interpolation function is as below
---------------------------------------------------
Option Explicit

Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant

' Interpolation Function

Dim i As Integer
Dim x1 As Double
Dim x2 As Double
Dim y1 As Double
Dim y2 As Double
Dim numRows As Integer


numRows = c1.Rows.Count

For i = 1 To numRows
If c1.Cells(i, 1).Value > Target Then
Exit For
End If
Next i

x1 = c1.Cells(i - 1, 1).Value
x2 = c1.Cells(i, 1).Value
y1 = c2.Cells(i - 1, 1).Value
y2 = c2.Cells(i, 1).Value

Interpolate = (y2 - y1) * (Target - x1) / (x2 - x1) + y1

End Function
---------------------------------------------
Could this problem be solved so that iteraion automatically starts without
any activation?? Kindly help.

Regards

Raj

raj74
Sat Jul 26 02:59:00 CDT 2008

why no expert is replying to my problem, is this a excel bug that it does not
do the iteration properly and need activation?????????????

regards

"raj74" wrote:

> I have an annoying situation when I each time open the Ms excel file I
> have to activate(pressing F2 and entering) the output cell (Col C) which
> is having a circular reference with Col A to get the desired value but it is
> lost when i reopen it next time and show #value.
> The ouput cell C is having a interpolate function (a macro, attached below)
> to interpolate from a range of value given separately for the value of
> corresponding B
> cell which is indirectly having a circular reference with Col A.
>
> To make it clear lets see the File:
>
> The three data range
>
> A1 B1 C1
> A2 B2 C2
> A3 B3 C3
>
> > Now the three cols are linked by following relation
> >
> > A = 4 + C
> > B = constvalue x A, Here B1 = 1.0 * A1, B2 = 0.9 * A2, B3 = 0.98 * A3
> > C = Function of (B)
> >
> > C value is retrived by interpolation between a range of B & C, Given
> > separately, For that a macro has been writeen.
>
> The data range from which C will interpolate the output value for
> corresponding B value are as follows:
> B C
> 0.00 0.00
> 2.00 1.00
> 4.00 2.00
> 6.00 3.00
> 8.00 4.00
> 10.00 5.00
> The solution is C1: C2:C3 = 4.00:3.27:3.84
>
> The macro for interpolation function is as below
> ---------------------------------------------------
> Option Explicit
>
> Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant
>
> ' Interpolation Function
>
> Dim i As Integer
> Dim x1 As Double
> Dim x2 As Double
> Dim y1 As Double
> Dim y2 As Double
> Dim numRows As Integer
>
>
> numRows = c1.Rows.Count
>
> For i = 1 To numRows
> If c1.Cells(i, 1).Value > Target Then
> Exit For
> End If
> Next i
>
> x1 = c1.Cells(i - 1, 1).Value
> x2 = c1.Cells(i, 1).Value
> y1 = c2.Cells(i - 1, 1).Value
> y2 = c2.Cells(i, 1).Value
>
> Interpolate = (y2 - y1) * (Target - x1) / (x2 - x1) + y1
>
> End Function
> ---------------------------------------------
> Could this problem be solved so that iteraion automatically starts without
> any activation?? Kindly help.
>
> Regards
>
> Raj
>

raj74
Mon Jul 28 12:59:41 CDT 2008

Thanks for the reply.
Actually what I am looking for why excel doesn't recalculate when specfic
UDF (User defined Function) is used. I have given a sample to my problem.In
actual problem my A col data is in circular ref with D col data, again B col
is linked with A Col & C col inked with B col by respective formula. The
output will be in C col based on the B col value. Interpolation fn (UDF) is
used because to get the value of output C for corresponding value of B from a
given (B,C) data range.
Each time I have to activae the output cell py presing [F2] and [Enter] like
similar situation given to you for the results.why it is not shown up
automatically. Still I couldn't found out why, Is their any code to activate
all the UDF or recalculate all the formula in a particular worksheet.
I am using Excel 2000. If I could send you the softcopy...
Any way thanks for your reply.

Sincerely
Raj
New Delhi


"Dana DeLouis" wrote:

> >> Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant
> >> The solution is C1:C3 = 4.00, 3.27, 3.84
>
> Hi. I believe this function in C1:C3 can be eliminated.
> In C1, just put the equation =B1/2 and copy down to C3.
>
> As a side note, I believe you can recursively solve your equation when taking the limit at infinity.
> I've written it here as a vba function to make it easy to show.
> Here, k is your "+4" value, and f is your factor (1, .9, or .98)
>
> Function Fx(k, f)
> Fx = (f * k) / (2 - f)
> End Function
>
> Sub TestIt()
> Debug.Print Fx(4, 1)
> Debug.Print Fx(4, 0.9)
> Debug.Print Fx(4, 0.98)
> End Sub
>
>
> This returns the 3 solutions given in your example (and at a higher precision I might add)
> 4
> 3.27272727272727
> 3.84313725490196
>
> --
> HTH :>)
> Dana DeLouis
>
>
> "raj74" <raj74@discussions.microsoft.com> wrote in message news:CC070166-54E9-400D-AB8F-8B17CCAD7492@microsoft.com...
> > why no expert is replying to my problem, is this a excel bug that it does not
> > do the iteration properly and need activation?????????????
> >
> > regards
> >
> > "raj74" wrote:
> >
> >> I have an annoying situation when I each time open the Ms excel file I
> >> have to activate(pressing F2 and entering) the output cell (Col C) which
> >> is having a circular reference with Col A to get the desired value but it is
> >> lost when i reopen it next time and show #value.
> >> The ouput cell C is having a interpolate function (a macro, attached below)
> >> to interpolate from a range of value given separately for the value of
> >> corresponding B
> >> cell which is indirectly having a circular reference with Col A.
> >>
> >> To make it clear lets see the File:
> >>
> >> The three data range
> >>
> >> A1 B1 C1
> >> A2 B2 C2
> >> A3 B3 C3
> >>
> >> > Now the three cols are linked by following relation
> >> >
> >> > A = 4 + C
> >> > B = constvalue x A, Here B1 = 1.0 * A1, B2 = 0.9 * A2, B3 = 0.98 * A3
> >> > C = Function of (B)
> >> >
> >> > C value is retrived by interpolation between a range of B & C, Given
> >> > separately, For that a macro has been writeen.
> >>
> >> The data range from which C will interpolate the output value for
> >> corresponding B value are as follows:
> >> B C
> >> 0.00 0.00
> >> 2.00 1.00
> >> 4.00 2.00
> >> 6.00 3.00
> >> 8.00 4.00
> >> 10.00 5.00
> >> The solution is C1: C2:C3 = 4.00:3.27:3.84
> >>
> >> The macro for interpolation function is as below
> >> ---------------------------------------------------
> >> Option Explicit
> >>
> >> Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant
> >>
> >> ' Interpolation Function
> >>
> >> Dim i As Integer
> >> Dim x1 As Double
> >> Dim x2 As Double
> >> Dim y1 As Double
> >> Dim y2 As Double
> >> Dim numRows As Integer
> >>
> >>
> >> numRows = c1.Rows.Count
> >>
> >> For i = 1 To numRows
> >> If c1.Cells(i, 1).Value > Target Then
> >> Exit For
> >> End If
> >> Next i
> >>
> >> x1 = c1.Cells(i - 1, 1).Value
> >> x2 = c1.Cells(i, 1).Value
> >> y1 = c2.Cells(i - 1, 1).Value
> >> y2 = c2.Cells(i, 1).Value
> >>
> >> Interpolate = (y2 - y1) * (Target - x1) / (x2 - x1) + y1
> >>
> >> End Function
> >> ---------------------------------------------
> >> Could this problem be solved so that iteraion automatically starts without
> >> any activation?? Kindly help.
> >>
> >> Regards
> >>
> >> Raj
> >>

raj74
Mon Jul 28 13:18:35 CDT 2008

Kindly Ignore the previous post,
The corrected post is here under (some col name was misplaced in the previous)


_________________________________________________________________________________
Actually what I am looking for why excel doesn't recalculate a iteration
when specfic UDF (User defined Function) is used. I have given a small
example to my actual problem. In actual problem my A col data is in circular
ref with D col data, again B col is linked with A Col & C col inked with B
col by respective formula. The output will be in D col based on the C col
value. Interpolation fn (UDF) is used to get the value of output D for
corresponding value of C from a given (C,D) data range.
Each time I have to activae the output cell py presing [F2] and [Enter] to
get the correct results like similar situation I had posted previously. why
the output value not shown up automatically. Still I couldn't found out why,
Is their any code to activate all the UDF or recalculate all the formula in a
particular worksheet.
I am using Excel 2000. If I could send you the softcopy...
Any way thanks for your reply.

Sincerely
Raj
New Delhi


> "Dana DeLouis" wrote:
>
> > >> Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant
> > >> The solution is C1:C3 = 4.00, 3.27, 3.84
> >
> > Hi. I believe this function in C1:C3 can be eliminated.
> > In C1, just put the equation =B1/2 and copy down to C3.
> >
> > As a side note, I believe you can recursively solve your equation when taking the limit at infinity.
> > I've written it here as a vba function to make it easy to show.
> > Here, k is your "+4" value, and f is your factor (1, .9, or .98)
> >
> > Function Fx(k, f)
> > Fx = (f * k) / (2 - f)
> > End Function
> >
> > Sub TestIt()
> > Debug.Print Fx(4, 1)
> > Debug.Print Fx(4, 0.9)
> > Debug.Print Fx(4, 0.98)
> > End Sub
> >
> >
> > This returns the 3 solutions given in your example (and at a higher precision I might add)
> > 4
> > 3.27272727272727
> > 3.84313725490196
> >
> > --
> > HTH :>)
> > Dana DeLouis
> >
> >
> > "raj74" <raj74@discussions.microsoft.com> wrote in message news:CC070166-54E9-400D-AB8F-8B17CCAD7492@microsoft.com...
> > > why no expert is replying to my problem, is this a excel bug that it does not
> > > do the iteration properly and need activation?????????????
> > >
> > > regards
> > >
> > > "raj74" wrote:
> > >
> > >> I have an annoying situation when I each time open the Ms excel file I
> > >> have to activate(pressing F2 and entering) the output cell (Col C) which
> > >> is having a circular reference with Col A to get the desired value but it is
> > >> lost when i reopen it next time and show #value.
> > >> The ouput cell C is having a interpolate function (a macro, attached below)
> > >> to interpolate from a range of value given separately for the value of
> > >> corresponding B
> > >> cell which is indirectly having a circular reference with Col A.
> > >>
> > >> To make it clear lets see the File:
> > >>
> > >> The three data range
> > >>
> > >> A1 B1 C1
> > >> A2 B2 C2
> > >> A3 B3 C3
> > >>
> > >> > Now the three cols are linked by following relation
> > >> >
> > >> > A = 4 + C
> > >> > B = constvalue x A, Here B1 = 1.0 * A1, B2 = 0.9 * A2, B3 = 0.98 * A3
> > >> > C = Function of (B)
> > >> >
> > >> > C value is retrived by interpolation between a range of B & C, Given
> > >> > separately, For that a macro has been writeen.
> > >>
> > >> The data range from which C will interpolate the output value for
> > >> corresponding B value are as follows:
> > >> B C
> > >> 0.00 0.00
> > >> 2.00 1.00
> > >> 4.00 2.00
> > >> 6.00 3.00
> > >> 8.00 4.00
> > >> 10.00 5.00
> > >> The solution is C1: C2:C3 = 4.00:3.27:3.84
> > >>
> > >> The macro for interpolation function is as below
> > >> ---------------------------------------------------
> > >> Option Explicit
> > >>
> > >> Function Interpolate(c1 As Range, c2 As Range, Target As Double) As Variant
> > >>
> > >> ' Interpolation Function
> > >>
> > >> Dim i As Integer
> > >> Dim x1 As Double
> > >> Dim x2 As Double
> > >> Dim y1 As Double
> > >> Dim y2 As Double
> > >> Dim numRows As Integer
> > >>
> > >>
> > >> numRows = c1.Rows.Count
> > >>
> > >> For i = 1 To numRows
> > >> If c1.Cells(i, 1).Value > Target Then
> > >> Exit For
> > >> End If
> > >> Next i
> > >>
> > >> x1 = c1.Cells(i - 1, 1).Value
> > >> x2 = c1.Cells(i, 1).Value
> > >> y1 = c2.Cells(i - 1, 1).Value
> > >> y2 = c2.Cells(i, 1).Value
> > >>
> > >> Interpolate = (y2 - y1) * (Target - x1) / (x2 - x1) + y1
> > >>
> > >> End Function
> > >> ---------------------------------------------
> > >> Could this problem be solved so that iteraion automatically starts without
> > >> any activation?? Kindly help.
> > >>
> > >> Regards
> > >>
> > >> Raj
> > >>

raj74
Tue Jul 29 10:31:02 CDT 2008

Everything I have checked in for iteration process like you mentioned in tool
> option > calculation. I have interested not the result of the file I have
posted (in my first post) but the activation it requires (output cell having
an UDF) to get the correct result.
I was reading something called application.volatile in excel to force to
start the calculation of UDF (user define function) to recalculate a
particular sheet. But i don't know how to write the code or whether it is
useful for my case.
As an alternative soln, if any code can be written so that after running it,
it will activate a formula (my interpolate UDf function) of a particular col
(say, D1:D40)l and enter it to get all the output result what I am doing
mannually.

Thanks anyway for giving your time.

sincerely

Raj

"Dana DeLouis" wrote:

> I'm not sure, but under Tools | Options, do you have "enable iterative calculation" turned on?

raj74
Wed Jul 30 05:07:03 CDT 2008

Hellio!!!!!!!!!!

Thanks anyway, but I have dealing with 15 worksheet in my real problem and
each time re enter all values is not possible. but I have got the answer from
a friend of mine. Paste the below module and run the macro, which will give
give you the result.


Sub WorkaroundToForceUDFCalculation()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
With sht
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With
Next sht
End Sub

Sincerely

Raj
New Delhi


"Dana DeLouis" wrote:

> Hi. What I believe is happening is the following.
> Both A1, & B1 are not numbers, but are the results of a Custom Function in
> C1.
>
> When the Workbook is open, A1 & B1 do not have any information. This lack
> of Information is passed to your vba Function, and this returns a #Value
> error.
> This value error is passed back to A1 & B1, and the error goes back into
> C1. Hence you are stuck, and can't get out of this error loop.
> I don't believe Excel has any logic to get itself out of these types of
> loops when opened.
> You will have to re-enter the data in order for Excel to pick up the
> calculation again.
>
> The workaround for your situation might be the following.
> In the vba editor (Alt+F11) enter the following code on the "ThisWorkbook"
> module.
> When this particular workbook is opened, we make sure Iteration is turned
> on, and we re-enter your Formulas.
> This worked well for me using Excel 2007.
>
> Private Sub Workbook_Open()
> With Application
> '// Make Sure Iteration is turned on
> .Iteration = True
> .MaxIterations = 1000
> .MaxChange = 0.0000001
>
> 'Re-Enter Formulas
> [C1:C3].Formula = [C1:C3].Formula
> End With
> End Sub
>
> --
> "To understand recursion, one must first understand recursion."
> Dana DeLouis
>
>
>
>
> "raj74" <raj74@discussions.microsoft.com> wrote in message
> news:FBEE8E6D-C9AF-43C2-8A0A-3939E0E48609@microsoft.com...
> > Everything I have checked in for iteration process like you mentioned in
> tool
> >> option > calculation. I have interested not the result of the file I
> have
> > posted (in my first post) but the activation it requires (output cell
> having
> > an UDF) to get the correct result.
> > I was reading something called application.volatile in excel to force to
> > start the calculation of UDF (user define function) to recalculate a
> > particular sheet. But i don't know how to write the code or whether it
> is
> > useful for my case.
> > As an alternative soln, if any code can be written so that after running
> it,
> > it will activate a formula (my interpolate UDf function) of a particular
> col
> > (say, D1:D40)l and enter it to get all the output result what I am doing
> > mannually.
> >
> > Thanks anyway for giving your time.
> >
> > sincerely
> >
> > Raj
> >
> > "Dana DeLouis" wrote:
> >
> >> I'm not sure, but under Tools | Options, do you have "enable iterative
> calculation" turned on?
> >
>