Excel 2002 - Iterations - Find the iteration sequence number

[Umfriend]

Hi all,

[A LOT OF TEXT, MIGHT HOP TO "QUESTION" BELOW, sry]

I use Iterations/Circular references to solve for a variable
(basically, it is a yield-to-maturity finder and no, the Excel
functions do not suffice).

The circular reference is basically set up such that I
1. Enter a value, i.e. Price
2. I have a "static" start value
3. I have a "variable" which basically records the deviation to the
start value to try (so that I am trying Start Value + Variable Value
as YTM)
4. I calculate the Price based on the YTM as under 3
5. I calculate the difference between 4 and 1
6. I calculate a new YTM to try under 4 by changing the Variable Value
under 3 based on the differnce in 5. (So basically I say Variable
Value = Variable Value + Change, or e.g. in B10 I would have "=B10 +
B11" and B11 would recede to zero after some iterations).

I found that simply programming a circular, and thus have Excel make
the guesses, caused it to be very slow and need many iterations.
Therefore, I created the structure as above.

I am trying to speed things up a bit further and to do that I am
trying to debug the UDF I use (reason for using a UDF is to avoid
issues with #VALUEs occuring in the circular structure due to other
issues, sort of error handling which works great. I know that using a
UDF probably slows things down and may get round doing it in Excel
competely later). The UDF basically gets me a new YTM to use. I want
to optimise the algortihm the get me he next guess therefor.

QUESTION

Now I can print.debug everthing I want except for one thing: The
iteration sequence number it is currently working on. When it
iterates, the statusbar will show "Iter: ##", and I want to print that
number with the other debug values each time the UDF is called.

Is there a way to do this? I would be satisfied if I could "read" the
text in the statusbar and print that to the Immediate window, but I
have not found a way to do it.

[Geesh, that's a lot of text for a simple query, sry]

 

[gocush]

Try this to get Statusbar value into a variable then do whatever with the
variable

Dim SB As String

Application.DisplayStatusBar = True
Application.StatusBar = "hello"
SB = Application.StatusBar
MsgBox SB

 

[Umfriend]

Gocush,

Thx for this, but I think it does not suffice. Your solution only
works when *I* have control of the statusbar, i.e., when I set the
value for it (But if I do that, then I would know what it is normally
).

The show my problem I expanded your solution somewhat:
Sub tst1()
Dim SB As String
Dim i As Integer
Application.DisplayStatusBar = True

Debug.Print "Setting the Statusbar myself:"
Application.StatusBar = "hello"
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar

Debug.Print "Having Excel Control the Statusbar"
Application.StatusBar = False
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar
End Sub

This yields:
Setting the Statusbar myself:
hellohello
Having Excel Control the Statusbar
FALSEFALSE

What I want is to be able to read the actual text in the statusbar
(which with me is "Ready Calculate" but could be "Ready" and during
my UDF will be "Iter: [Iteration sequence number"]

So, either I would like to be able to read the actual text of the
statusbar _or_ have a way to find the actual Iteration Sequence
Number, which is really what I am currently looking for (reading the
statusbar is just a way I thought to try to get it)

Umf

Try this to get Statusbar value into a variable then do whatever with the
variable

Dim SB As String

Application.DisplayStatusBar = True
Application.StatusBar = "hello"
SB = Application.StatusBar
MsgBox SB

QUESTION

Now I can print.debug everthing I want except for one thing: The
iteration sequence number it is currently working on. When it
iterates, the statusbar will show "Iter: ##", and I want to print that
number with the other debug values each time the UDF is called.

Is there a way to do this? I would be satisfied if I could "read" the
text in the statusbar and print that to the Immediate window, but I
have not found a way to do it.

[Geesh, that's a lot of text for a simple query, sry]

 

[Robin Hammond]

Um...

I don't think you can read the status bar during calculation from within
VBA.

An alternative and horribly kludgy approach to give you an idea of what
iteration you are on:

add this to a general module:

Public Function IterationCounter() As Long
IterationCounter = Application.Caller.Value + 1
End Function

'add this to the code for the sheet you are trying to monitor
Private Sub Worksheet_Calculate()
Cells(1, 1).ClearContents
Cells(1, 1).Formula = "=IterationCounter()"
End Sub

If there is no circular calculation in the sheet, create one.

Press calculate and watch the counter go up in the top left cell!

Robin Hammond
www.enhanceddatasystems.com

Gocush,

Thx for this, but I think it does not suffice. Your solution only
works when *I* have control of the statusbar, i.e., when I set the
value for it (But if I do that, then I would know what it is normally
).

The show my problem I expanded your solution somewhat:
Sub tst1()
Dim SB As String
Dim i As Integer
Application.DisplayStatusBar = True

Debug.Print "Setting the Statusbar myself:"
Application.StatusBar = "hello"
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar

Debug.Print "Having Excel Control the Statusbar"
Application.StatusBar = False
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar
End Sub

This yields:
Setting the Statusbar myself:
hellohello
Having Excel Control the Statusbar
FALSEFALSE

What I want is to be able to read the actual text in the statusbar
(which with me is "Ready Calculate" but could be "Ready" and during
my UDF will be "Iter: [Iteration sequence number"]

So, either I would like to be able to read the actual text of the
statusbar _or_ have a way to find the actual Iteration Sequence
Number, which is really what I am currently looking for (reading the
statusbar is just a way I thought to try to get it)

Umf

Try this to get Statusbar value into a variable then do whatever with the
variable

Dim SB As String

Application.DisplayStatusBar = True
Application.StatusBar = "hello"
SB = Application.StatusBar
MsgBox SB

QUESTION

Now I can print.debug everthing I want except for one thing: The
iteration sequence number it is currently working on. When it
iterates, the statusbar will show "Iter: ##", and I want to print that
number with the other debug values each time the UDF is called.

Is there a way to do this? I would be satisfied if I could "read" the
text in the statusbar and print that to the Immediate window, but I
have not found a way to do it.

[Geesh, that's a lot of text for a simple query, sry]

 

[Umfriend]

Oh man..... the simplicity, the beauty...Why did I not think of this myself?

Thx.

 

[gocush]

I was under the impression from your orig post that you had a UDF that gave
you the iteration number:
----------------------------------
When it
iterates, the statusbar will show "Iter: ##",
-------------------------------------------------------
and that you were looking for a way to read whatever value was showing in
the status bar into a variable. something along the lines of the following.



Sub ShowStatus()

Dim SB As String
Dim IterNum As Long

'Your UDF which returns the IternUM
Application.StatusBar = "Working on Iteration..." & IterNum

SB = Application.StatusBar
Debug.Print SB
'or
Range("a1") = SB
End Sub


.........Must have misread it. sorry

Gocush,

Thx for this, but I think it does not suffice. Your solution only
works when *I* have control of the statusbar, i.e., when I set the
value for it (But if I do that, then I would know what it is normally
).

The show my problem I expanded your solution somewhat:
Sub tst1()
Dim SB As String
Dim i As Integer
Application.DisplayStatusBar = True

Debug.Print "Setting the Statusbar myself:"
Application.StatusBar = "hello"
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar

Debug.Print "Having Excel Control the Statusbar"
Application.StatusBar = False
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar
End Sub

This yields:
Setting the Statusbar myself:
hellohello
Having Excel Control the Statusbar
FALSEFALSE

What I want is to be able to read the actual text in the statusbar
(which with me is "Ready Calculate" but could be "Ready" and during
my UDF will be "Iter: [Iteration sequence number"]

So, either I would like to be able to read the actual text of the
statusbar _or_ have a way to find the actual Iteration Sequence
Number, which is really what I am currently looking for (reading the
statusbar is just a way I thought to try to get it)

Umf

Try this to get Statusbar value into a variable then do whatever with the
variable

Dim SB As String

Application.DisplayStatusBar = True
Application.StatusBar = "hello"
SB = Application.StatusBar
MsgBox SB

QUESTION

Now I can print.debug everthing I want except for one thing: The
iteration sequence number it is currently working on. When it
iterates, the statusbar will show "Iter: ##", and I want to print that
number with the other debug values each time the UDF is called.

Is there a way to do this? I would be satisfied if I could "read" the
text in the statusbar and print that to the Immediate window, but I
have not found a way to do it.

[Geesh, that's a lot of text for a simple query, sry]

 

[Umfriend]

Yes, that was what I was looking for. But the solution offered was
suficient: I have the iteration number on the worksheet now. I simply
pass that value on to the UDF I am using and thus I know the actual
Iter number in the UDF. I'm still pissed I did not think of this
myslef. I expended on it myself inthe sense that I can tell when the
Iteraions are doen, i.e., when they have found the solution. At that
time, the Cell showing the iteraitonnumber is reset to zero as well.

So you read it right, but the solution offered led me to where I
wanted to be. Thx all

I was under the impression from your orig post that you had a UDF that gave
you the iteration number:
----------------------------------
When it
iterates, the statusbar will show "Iter: ##",
-------------------------------------------------------
and that you were looking for a way to read whatever value was showing in
the status bar into a variable. something along the lines of the following.



Sub ShowStatus()

Dim SB As String
Dim IterNum As Long

'Your UDF which returns the IternUM
Application.StatusBar = "Working on Iteration..." & IterNum

SB = Application.StatusBar
Debug.Print SB
'or
Range("a1") = SB
End Sub


........Must have misread it. sorry

Gocush,

Thx for this, but I think it does not suffice. Your solution only
works when *I* have control of the statusbar, i.e., when I set the
value for it (But if I do that, then I would know what it is normally
).

The show my problem I expanded your solution somewhat:
Sub tst1()
Dim SB As String
Dim i As Integer
Application.DisplayStatusBar = True

Debug.Print "Setting the Statusbar myself:"
Application.StatusBar = "hello"
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar

Debug.Print "Having Excel Control the Statusbar"
Application.StatusBar = False
SB = Application.StatusBar
Debug.Print SB & Application.StatusBar
End Sub

This yields:
Setting the Statusbar myself:
hellohello
Having Excel Control the Statusbar
FALSEFALSE

What I want is to be able to read the actual text in the statusbar
(which with me is "Ready Calculate" but could be "Ready" and during
my UDF will be "Iter: [Iteration sequence number"]

So, either I would like to be able to read the actual text of the
statusbar _or_ have a way to find the actual Iteration Sequence
Number, which is really what I am currently looking for (reading the
statusbar is just a way I thought to try to get it)

Umf

Try this to get Statusbar value into a variable then do whatever with the
variable

Dim SB As String

Application.DisplayStatusBar = True
Application.StatusBar = "hello"
SB = Application.StatusBar
MsgBox SB

"Umfriend" wrote: [SNIP]

QUESTION

Now I can print.debug everthing I want except for one thing: The
iteration sequence number it is currently working on. When it
iterates, the statusbar will show "Iter: ##", and I want to print that
number with the other debug values each time the UDF is called.

Is there a way to do this? I would be satisfied if I could "read" the
text in the statusbar and print that to the Immediate window, but I
have not found a way to do it.

[Geesh, that's a lot of text for a simple query, sry]