vba error handling

[Jollyfrog]

hi!

have code that's something like:

For a = (3 + r - 1) To (21 + r - 1)

cntry = Cells(a, 3).Value
On Error GoTo bob
rst.Open "something something"
   temp = rst!subs
   
         
   calc = final * temp
   
   Cells(a, 25).Value = calc
   

bob:  rst.Close
   Next a

works for a bit and they says, invalid use of null... why is this happening, why does this not trigger the error handler?

[DonkeyOte]

I'd suggest a quick read through of: http://www.cpearson.com/excel/ErrorHandling.htm

trust me - it's worth it - understanding error handling is important.

[Jollyfrog]

Ok, read through that. as a matter of fact i'd seen it before but re-read to see if i missed something, i'm afraid i still can't see why it does not work... it seems to operate correctly for the first error but not the second... very odd

[DonkeyOte]

To quote from the link:

Resume is the only way, aside from exiting the procedure, to get out of an error handling block.
Do not use the Goto statement to direct code execution out of an error handling block.
Doing so will cause strange problems with the error handlers.

I think that pretty much covers it, no ?

Once you invoke the handler unless you Exit or use a Resume Statement you remain within the Handler... it follows that if a subsequent error occurs then there is no error handler (you're already in it).

[Jollyfrog]

aaaaah i see, i was confused about remaining inside the handler..

so i should have my error handler outside of the loop and then use a resume label to return to the loop at the desired point. i shall go work on this


thanks for your assistance

Jonathan

[romperstomper]

Even better to just test for rst.eof before you attempt to read the record. If true, there's no record to read.