Excel Iteration Program

[raj74]

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]

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]

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

 

[raj74]

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

 

[raj74]

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, D140)l and enter it to get all the output result what I am doing
mannually.

Thanks anyway for giving your time.

sincerely

Raj

 

[raj74]

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

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

Everything I have checked in for iteration process like you mentioned in tool 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, D140)l and enter it to get all the output result what I am doing
mannually.

Thanks anyway for giving your time.

sincerely

Raj

calculation" turned on?