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

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

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