Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Longer loop than expected
Hello,
I always think: oh I can do that quickly in a couple of minutes and then it seems to take me houres... So here's what happened this time: I programmed a small loop but excel seems to loop through it a more than the possible maximum and somtimes gives unexpected results. Maybe it has something to do with formulas: When the values of the array (range) are not formulas: it works ok. But if there are formulas... Function lastvalueinarray(table_array As Range) As Variant Dim i As Integer Application.Volatile i = 1 MsgBox (table_array.Rows.Count) While i < table_array.Rows.Count MsgBox (i) MsgBox (table_array.Item(i, 1).Value) i = i + 1 Wend lastvalueinarray = table_array(i, 1).Value End Function The messageboxes are there as a sort of debugging. One should expect that the maximum number of loops is 'count'. So there would appear 2xcount messageboxes. But it is a multiple of that. On top of that: If the array (range) contains #N/A as result of a formula the result is #Value! instead of the last value in the array. I know there are easier ways of getting the last value. But this provides an easy way of showing and explaining the problem whithout posting 2 pages of code. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Longer loop than expected
You can get to the last cell in a single area range with something like this:
dim myRng as range set myRng = range("a1:b99") msgbox myrng.cells(myrng.cells.count).value (if it doesn't have an error in it.) And if the range can contain multiple areas, you can go directly to end of the last area with something like: Option Explicit Function lastValueInArray(table_array As Range) As Variant Dim res As Variant With table_array With .Areas(.Areas.Count) If IsError(.Cells(.Cells.Count).Value) Then res = .Cells(.Cells.Count).Text Else res = .Cells(.Cells.Count).Value End If End With End With lastValueInArray = res End Function 'tested with: Sub testme() MsgBox lastValueInArray(Selection) End Sub Bart Deschoolmeester wrote: Hello, I always think: oh I can do that quickly in a couple of minutes and then it seems to take me houres... So here's what happened this time: I programmed a small loop but excel seems to loop through it a more than the possible maximum and somtimes gives unexpected results. Maybe it has something to do with formulas: When the values of the array (range) are not formulas: it works ok. But if there are formulas... Function lastvalueinarray(table_array As Range) As Variant Dim i As Integer Application.Volatile i = 1 MsgBox (table_array.Rows.Count) While i < table_array.Rows.Count MsgBox (i) MsgBox (table_array.Item(i, 1).Value) i = i + 1 Wend lastvalueinarray = table_array(i, 1).Value End Function The messageboxes are there as a sort of debugging. One should expect that the maximum number of loops is 'count'. So there would appear 2xcount messageboxes. But it is a multiple of that. On top of that: If the array (range) contains #N/A as result of a formula the result is #Value! instead of the last value in the array. I know there are easier ways of getting the last value. But this provides an easy way of showing and explaining the problem whithout posting 2 pages of code. Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Longer loop than expected
Try taking the application.volatile out of your function. I think excel wants
to recalculate each time you change something. And your formulas are putting it in a loop. Bart Deschoolmeester wrote: Oh just leave it: nowbody will read this thread again: so I'll have to repost: but please: READ!!! the message you reply to: getting the last value of a range was not my aim. My aim is that you would run the code and see what's wrong: why does it loops the code more than it's apparent maximum (number of rows). -----Original Message----- You can get to the last cell in a single area range with something like this: dim myRng as range set myRng = range("a1:b99") msgbox myrng.cells(myrng.cells.count).value (if it doesn't have an error in it.) And if the range can contain multiple areas, you can go directly to end of the last area with something like: Option Explicit Function lastValueInArray(table_array As Range) As Variant Dim res As Variant With table_array With .Areas(.Areas.Count) If IsError(.Cells(.Cells.Count).Value) Then res = .Cells(.Cells.Count).Text Else res = .Cells(.Cells.Count).Value End If End With End With lastValueInArray = res End Function 'tested with: Sub testme() MsgBox lastValueInArray(Selection) End Sub Bart Deschoolmeester wrote: Hello, I always think: oh I can do that quickly in a couple of minutes and then it seems to take me houres... So here's what happened this time: I programmed a small loop but excel seems to loop through it a more than the possible maximum and somtimes gives unexpected results. Maybe it has something to do with formulas: When the values of the array (range) are not formulas: it works ok. But if there are formulas... Function lastvalueinarray(table_array As Range) As Variant Dim i As Integer Application.Volatile i = 1 MsgBox (table_array.Rows.Count) While i < table_array.Rows.Count MsgBox (i) MsgBox (table_array.Item(i, 1).Value) i = i + 1 Wend lastvalueinarray = table_array(i, 1).Value End Function The messageboxes are there as a sort of debugging. One should expect that the maximum number of loops is 'count'. So there would appear 2xcount messageboxes. But it is a multiple of that. On top of that: If the array (range) contains #N/A as result of a formula the result is #Value! instead of the last value in the array. I know there are easier ways of getting the last value. But this provides an easy way of showing and explaining the problem whithout posting 2 pages of code. Thanks -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too few parameters. Expected 2 | Excel Discussion (Misc queries) | |||
How to find the Expected Value of a PDF | Excel Worksheet Functions | |||
Not sorting as expected | New Users to Excel | |||
Expected end of Statement | Excel Worksheet Functions | |||
Expected:expression | Excel Programming |