On Error - Only executing once.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NateBuckley - 27 Mar 2008 11:54 GMT

Hello I have a On Error Goto statement, yet it only works once, then It just
ignores and tries to execute although this causes it to crash.

I'm attempting to grab stuff from a collection

Set tmp = lookUpCollection.Item("Monkey"), there is no object with the key
monkey, and it bypasses it on the first go by following my error handling line

On Error Goto ShouldSkip

The ShouldSkip label contains the following

ShouldSkip:
skip = True
GoTo CheckSkip

Why is it only doing it once, I've used this method before and it worked
fine, just all of a suddent I'm getting the error

Invalid Procedure Call or argument.

Thanks in advance.

Reply to this Message

paul.robinson@it-tallaght.ie - 27 Mar 2008 12:03 GMT

Hi
Not enough info.
"Only works once" - are you in a loop? Let's see it.
What are the scope of the variables? Is skip a public or private
variable being tested in a loop??
Is tmp really an object in a collection of objects, or a variant???

regards
Paul

On Mar 27, 10:54 am, NateBuckley
<NateBuck...@discussions.microsoft.com> wrote:

> Hello I have a On Error Goto statement, yet it only works once, then It just
> ignores and tries to execute although this causes it to crash.
[quoted text clipped - 18 lines]
>
> Thanks in advance.

Reply to this Message

Per Jessen - 27 Mar 2008 12:05 GMT

Hi Nate

It's not easy to see what's wrong without the entire code!

Do you set
skip = False
before second loop?

Regards,

Per

> Hello I have a On Error Goto statement, yet it only works once, then It
> just
[quoted text clipped - 20 lines]
>
> Thanks in advance.

Reply to this Message

NateBuckley - 27 Mar 2008 12:18 GMT

My Apologies for not posting the code,
please excuse the mess that is the code :)

Option Explicit
Private FoundProducts As Collection
Public Sub lookUp()
Dim skip As Boolean
Dim SingleProduct As C_ProductCode
Dim foundProduct As C_EntireBay
Dim lookUpCollection As Collection
Dim str As String
Dim binCode() As String
Dim prodCode() As String
Dim binNum As Long
Dim prodNum As Long
prodNum = Sheets("LI").Cells(Rows.Count, "K").End(xlUp).Row
binNum = Sheets("LI").Cells(Rows.Count, "A").End(xlUp).Row

Set lookUpCollection = New Collection
Set FoundProducts = New Collection

Dim i As Long
Dim j As Long
For i = 2 To prodNum
prodCode = Split(Sheets("LI").Cells(i, 11).Value, "/")
str = ""
For j = 0 To UBound(prodCode)
str = str & prodCode(j)
Next j
Set SingleProduct = New C_ProductCode
SingleProduct.AtRow = i
SingleProduct.ProductCode = str
On Error Resume Next
lookUpCollection.Add SingleProduct, SingleProduct.ProductCode
On Error GoTo 0
Next i
For i = 2 To binNum
skip = False
str = ""
binCode = Split(Sheets("LI").Cells(i, 6).Value, "/")
For j = 0 To UBound(binCode)
str = str & binCode(j)
Next j
On Error GoTo ShouldSkip
Set SingleProduct = lookUpCollection.Item(str) 'PROBLEMS
CheckSkip:
If skip = False Then
Set foundProduct = New C_EntireBay
foundProduct.Aisle = Sheets("LI").Cells(i, 1).Value
...
...
foundProduct.Allocation = Sheets("LI").Cells(i, 9).Value
foundProduct.Code = SingleProduct.ProductCode
'Sheets("LI").Cells(SingleProduct.AtRow, 12).value = "Exists
within -" & Sheets("LI").Cells(i, 1) & Sheets("LI").Cells(i, 2) &
Sheets("LI").Cells(i, 3)
FoundProducts.Add foundProduct
Else
End If
Next i
Call clearKAndL
Call foundProductsToNewSheet
Exit Sub
ShouldSkip:
skip = True
GoTo CheckSkip
End Sub

Reply to this Message

Nigel - 27 Mar 2008 12:39 GMT

Simple one, what is your error trapping level set to in the VBE project?

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

> My Apologies for not posting the code,
> please excuse the mess that is the code :)
[quoted text clipped - 63 lines]
> GoTo CheckSkip
> End Sub

Reply to this Message

NateBuckley - 27 Mar 2008 12:52 GMT

Hello,

It's currently set to "Break on unhandled errors"

Maybe I should just do some kinda test to see if the object exists within
the collection already, it's just the java equivlent of if(object!=null)
doesn't seem to be around in VBA.

> Simple one, what is your error trapping level set to in the VBE project?
>
[quoted text clipped - 65 lines]
> > GoTo CheckSkip
> > End Sub

Reply to this Message

Jim Cone - 27 Mar 2008 13:15 GMT

I would try replacing "GoTo CheckSkip" with "Resume CheckSkip"

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"NateBuckley" <
wrote in message
Hello I have a On Error Goto statement, yet it only works once, then It just
ignores and tries to execute although this causes it to crash.

I'm attempting to grab stuff from a collection
Set tmp = lookUpCollection.Item("Monkey"), there is no object with the key
monkey, and it bypasses it on the first go by following my error handling line

On Error Goto ShouldSkip
The ShouldSkip label contains the following

ShouldSkip:
skip = True
GoTo CheckSkip

Why is it only doing it once, I've used this method before and it worked
fine, just all of a suddent I'm getting the error
Invalid Procedure Call or argument.
Thanks in advance.

Reply to this Message

NateBuckley - 27 Mar 2008 13:36 GMT

Cheers Mate that one little change fixed it all,

Why is that?

Thank you!

>
> I would try replacing "GoTo CheckSkip" with "Resume CheckSkip"

Reply to this Message

Jim Cone - 27 Mar 2008 13:54 GMT

Any form of "Resume" (in an error handler) clears the error and enables error handling again.
'--
Jim Cone

"NateBuckley"
wrote in message
Cheers Mate that one little change fixed it all,
Why is that?
Thank you!

"Jim Cone" wrote:

> I would try replacing "GoTo CheckSkip" with "Resume CheckSkip"

> "NateBuckley" <
> wrote in message
[quoted text clipped - 16 lines]
> Invalid Procedure Call or argument.
> Thanks in advance.

Reply to this Message