Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
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] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
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: 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] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
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 gocush /delete wrote in message ... 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] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
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 "Umfriend" wrote in message m... 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 gocush /delete wrote in message ... 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] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
Oh man..... the simplicity, the beauty...Why did I not think of this myself?
Thx. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
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 "Umfriend" wrote: 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 gocush /delete wrote in message ... 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] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 - Iterations - Find the iteration sequence number
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 ****ed 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 gocush /delete wrote in message ... 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 "Umfriend" wrote: 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 gocush /delete wrote in message ... 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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How to fill group of of odd number in sequence ? | Excel Discussion (Misc queries) | |||
Excel 2002: How to find 0.00 in a column of number? | Excel Discussion (Misc queries) | |||
Iteration problem (Excel 2002) | Excel Worksheet Functions | |||
How do I find the next higher number in a sequence? | Excel Discussion (Misc queries) | |||
How do I find a missing number in a sequence of numbers? | Excel Worksheet Functions |