I am getting unexpected behavior and an error with a For Next loop for a listbox with VBA.  

When the listbox has no items, the For condition seems to be ignored.
Does For-Next ALWAYS execute the loop at least once?

For intExistingRow = 0 To (listBoxCurrentInventoryItems.ListCount - 1)
   ' When the list is empty and the for loop erroneuosly executes, the following error occurs at the assignment on the next line of code
    ' Run-time error 94 - Invalid use of Null
    ' I understand that I can put a IsNull check here,
    ' but why does the code even get here if the ListCount is 0
    strExistingItem = listBoxCurrentInventoryItems.ItemData(intExistingRow)
    
    If (strQueuedItem = strExistingItem) Then
        ' Duplicate - do nothing
    Else
        bNewItem = True
    End If

Next intExistingRow            

I appreciate and comments on this
- Bill
                

For-Next is executed zero or more times.
However, ensure that the list is EMPTY. Having even 1 item in it will execute this statement once.
Your invalid use of null indicates that there is at least 1 item in the list (otherwise you get an index out of range error(ish)).
Sugest you print the listboxcurrentinventoryitems.list(0). Is this Access? That may return a blank row from the rowsource.

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Kieron - Thanks for your response.

I am using Access, which might be the explanation. Perhaps it is returning a blank row. However, I am still perplexed as to why the loop even iterates one time. According to the lisbox's ListCount property (at runtime), the listbox is empty (count is 0)....yet the code inside the loop still executes for this condition.

I am leaning towards belief that this is some kind of weird hidden "feature" in Access's VBA.


As a workaround, I added a condition test above the loop to check the ListCount value. If it is 0, then I won't try the for loop.  

Happy Thanksgiving !!!


  

quote:

---

BlackDuck603 wrote:
Kieron - Thanks for your response.

I am using Access, which might be the explanation. Perhaps it is returning a blank row. However, I am still perplexed as to why the loop even iterates one time. According to the lisbox's ListCount property (at runtime), the listbox is empty (count is 0)....yet the code inside the loop still executes for this condition.

I am leaning towards belief that this is some kind of weird hidden "feature" in Access's VBA.


As a workaround, I added a condition test above the loop to check the ListCount value. If it is 0, then I won't try the for loop.  

Happy Thanksgiving !!!


  

---

OK - I see.

This for loop behavior is a bit different than what I have used in c#.

The first statement initialises the iterator, the condition evaluates it against an end value, and the second statement changes the iterator value.
If I wrote this in C# it would have been
for (idx = 0; idx < ListCount; idx++)
{
}

My understanding (erroneous be it may) was that the TO in the FOR statement was acting as a LESS THAN (the upper limit), but iI realize now that TO is more like EQUAL TO.  

I see the error in my ways.
Makes sense to me now.

Thanks for the clarification Nick2k3

Why not use Do While loops? e.g.

bNewItem = True intExistingRow = 0 Do While intExistingRow < listBoxCurrentInventoryItems.ListCount    strExistingItem = listBoxCurrentInventoryItems.ItemData(intExistingRow)    if strQueuedItem = strExistingItem then       bNewItem = False       Exit Do    end if    intExistingRow = intExistingRow + 1 Loop

You should stop comparing once it's found - to cut meaningless iterations: "Exit Do" (Assume new, then stop searching if found! Consider a match on item 1 on a 100,000 items list. We need to consider performance while coding!)

One more thing! In case your list is so large that it's more than 65536 items (or the max of integer), you should use the type long instead of int, to reduce chance of overflow.

____________________________
Knowledge is there to share, not to hide!
-< PATKAM >-