Handling error in VBA

YasserKhalil
PlatinumLounger
Posts: 4795
Joined: 31 Aug 2016, 09:02

Handling error in VBA

Post by YasserKhalil »

Hello everyone

When I am using this line [On Error GoTo ErrHandler] to navigate to a specific point

Code: Select all

ErrHandler:
            If Err Then On Error GoTo 0: Err.Clear: Debug.Print sFileName
            sFileName = Dir
I can move into the point just once but if the error occurs again, I got an error and the code stops. How can I make the code moves the point `ErrHandler` every time the error occurs?

The question posted here too
https://www.excelforum.com/excel-progra ... n-vba.html

User avatar
HansV
Administrator
Posts: 76222
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Handling error in VBA

Post by HansV »

Has the problem been solved there by now?
Regards,
Hans

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Handling error in VBA

Post by DocAElstein »

It sort of been talked around a bit there at excelforum by the look of it, by a chap called karedog ….

Hello
Here is some more of the same ….
When an error occurs, the register holding info about the error in the object Err gets filled, but that is the least of what goes on. That is not particularly significant.
The more significant thing that goes on is that we are taken to a parallel dark net type world. (Sometimes this parallel world is called an exception State) In this parallel world, they don’t know about any error handlers, past or present. Our coding at the point of entry to this world gets tacked on or copied into this parallel world and coding goes on fairly normally, with a few exceptions. One of these exceptions is that any error handlers, past or present, or further occurred our ignored – remember in this parallel world they don’t recognise error handlers or remember any.

The most fundamental thing to bring us back to our original sane world is the statement On Error GoTo -1
Err.Clear only clears the register holding information about the error. On Error GoTo 0 will disable any user defined error handler in the real world. It won’t do anything in the parallel exception state world, since it’s a form or error handler, - it puts you back to the default error handler - and remember in this parallel world they don’t recognise error handlers, so that is ignored
So once an error has occurred, On Error GoTo 0 does nothing, and Err.Clear just empties some text held somewhere. Neither of those take ypou back into the normal real world, hence it appears to you that errors only were handled once.


I don’t know exactly what you are doing, Yasser. As you only give that bit, we can’t be sure what you are trying to do in total, so we are working a bit blind, but this might be worth a try to see if it does what you want

Code: Select all

ErrHandler:
            If Err Then On Error GoTo -1: Debug.Print sFileName
            sFileName = Dir
_.________________

What karedog has shown and played around with is variations of the Resume, and tried to explain what that is about. A by product of variations of the Resume is that they effectively do also the On Error GoTo -1 So it follows that if playing around with those in various codings may mean that error are likely to be handled more than once. Think of it as like pseudo a
Resume
, is like
Res[On Error GoTo -1]ume

Of course, it may require something different, depending on exactly what it is you are wanting to do
Some thing like this might be what you actually want

Code: Select all

'
'
 '
'
On Error GoTo ErrHandler:
----    Here the thing that might error
On Error GoTo 0    '  best is usually to go back to the default error handler if you are not expecting errors
 '
'
 '
'
 '
'
 '
'
ErrHandler:
Debug.Print sFileName
Resume Next  '   This also does the  On Error GoTo -1    before then taking you back to the real world just after where the error occured

 

Some extra notes
http://www.eileenslounge.com/viewtopic. ... 92#p278892
https://www.myonlinetraininghub.com/err ... ing-in-vba
Last edited by DocAElstein on 16 Mar 2023, 20:14, edited 2 times in total.
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

YasserKhalil
PlatinumLounger
Posts: 4795
Joined: 31 Aug 2016, 09:02

Re: Handling error in VBA

Post by YasserKhalil »

Thanks a lot Mr. Alan
I tried your code but I get into infinite loop. Can you provide me with a complete example so as to check the whole idea of using On Error GoTo -1?

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Handling error in VBA

Post by DocAElstein »

OK, three macros
They all do the same thing: They tell you what array indicia of the array element that had a number that caused an error
I have array, arrNumbers(1 To 5) = {1, 0, 7, 0, 1 } and I try to divide 1 by each number from the array. So I will have error with arrNumbers(2) and arrNumbers(4) because
1/1 - OK
1/0 - Error
1/7 - OK
1/0 - Error
1/1 - OK
The macros all do the same thing. They tell me count 2 and 4 errored

The first uses On Error GoTo -1 in an attempt just to show you what it does

The second uses Resume to do almost the same coding, but simpler

The third is my best guess for something like you may have originally been trying to do..

Code: Select all

Sub OnErrorMinusWon() '  https://eileenslounge.com/viewtopic.php?p=305624#p305624                                                                                                          I learnt it all from Mrs Rosy Archibald
Dim arrNumbers(1 To 5) As Long
 Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = 7: Let arrNumbers(4) = 0: Let arrNumbers(5) = 1
Dim Cnt As Long
    For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
    On Error GoTo Bed ' Get ready for stuff we might expect to error
    Dim Nmber As Double: Let Nmber = 1 / arrNumbers(Cnt)
NxtAfterError:  '  Note  On Error GoTo -1   does not also do  On Error GoTo 0 ,
    On Error GoTo 0
'
'
'' Other stuff not expected to error
'
    Next Cnt

Exit Sub

Bed: ' Here I am in the parallel dark net world in the glorius State of Exception where my coding is cloned like a zombie that walks around ignoring all error handlers
 Debug.Print "Error occured at count " & Cnt
 On Error GoTo -1 ' This takes me back to the real world
 GoTo NxtAfterError ' this tales me to  NxtAfterError
End Sub
' This next does the same as last macro
Sub OnErrorResumeNext() '
Dim arrNumbers(1 To 5) As Long
 Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = 7: Let arrNumbers(4) = 0: Let arrNumbers(5) = 1
Dim Cnt As Long
    For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
    On Error GoTo Bed ' Get ready for stuff we might expect to error
    Dim Nmber As Double: Let Nmber = 1 / arrNumbers(Cnt)
    On Error GoTo 0
'
'' Other stuff not expected to error
'
    Next Cnt

Exit Sub

Bed: ' Here I am in the parallel dark net world in the glorius State of Exception where my coding is cloned like a zombie that walks around ignoring all error handlers
 Debug.Print "Error occured at count " & Cnt
 Resume Next ' This both brings me back to the real world  and  takes me to the line after where the error occured
End Sub

Sub MaybeSomethingLikeYasserWants()
Dim arrNumbers(1 To 5) As Long
 Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = 7: Let arrNumbers(4) = 0: Let arrNumbers(5) = 1
Dim Cnt As Long
    For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
    On Error GoTo Bed ' Get ready for stuff we might expect to error
    Dim Nmber As Double: Let Nmber = 1 / arrNumbers(Cnt)
Bed:  If Err Then: On Error GoTo -1: Debug.Print "Error occured at count " & Cnt
    On Error GoTo 0
'
'' Other stuff not expected to error
'

    Next Cnt

End Sub

Se also here
http://www.eileenslounge.com/viewtopic. ... 96#p278892





_.__________________________________________________________________________________________

Maybe you wanted to do something like this… just another guess

Code: Select all

Do While sFileName <>  ""
'
'' Other stuff not expected to error
'
   On Error GoTo ErrHandler
    Let  sFileName = Dir
ErrHandler:  If Err Then: On Error GoTo -1: Debug.Print sFileName: Let  sFileName = Dir '  Note  On Error GoTo -1   does not also do  On Error GoTo 0    so we will loop back as many times as necerssary if consequtive attempts here at   Dir  cause an error
   On Error GoTo 0

   Loop
Last edited by DocAElstein on 17 Mar 2023, 09:07, edited 1 time in total.
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

YasserKhalil
PlatinumLounger
Posts: 4795
Joined: 31 Aug 2016, 09:02

Re: Handling error in VBA

Post by YasserKhalil »

Thank you very much for great support.

User avatar
rory
5StarLounger
Posts: 783
Joined: 24 Jan 2010, 15:56

Re: Handling error in VBA

Post by rory »

If you (think you) need to use On Error Goto -1 you almost certainly need to redesign your code.
Regards,
Rory

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Handling error in VBA

Post by DocAElstein »

rory wrote:
17 Mar 2023, 10:20
If you (think you) need to use On Error Goto -1 you almost certainly need to redesign your code.
I would have put money on that very reply appearing from you, Lol

A lot of people say things along those lines, I mean generally about not using error handling unless you really need to.
I am not sure myself in the meantime.
Perhaps to some extent the opinion could be influenced, at least to some extent, by most people not understanding how it works.
Apart from you, me and one other person who I think died, I know of no one that understands it all, I mean under-standing all about error handling in VBA. I am continually surprised how many very senior people, very much smarter than me, don’t quite understand it all.
I don’t think it is particularly difficult to learn it all, - if it was I would never understand it all, Lol.
Perhaps the amount to learn is just that little bit too much to fit in a tutorial that would not be too long for anyone to want to read. Also the wording chosen for the syntax comes across as a bit wacky
(I leant it all from you, so it’s partly your fault…. )
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Slightly compacter way to do simple error handling in VBA

Post by DocAElstein »

It’s nice when answering a question reveals an idea you had not noticed before, in this case, the last of the three demo macros I did a few posts up, Sub MaybeSomethingLikeYasserWants() , revealed for me a slightly more compact way to do something….
While it’s fresh in my mind, I thought it would be a useful contribution to re explain that a bit better for future reference….

first, to refresh what we often see already...
A very typical way seen of doing error handling in VBA
A very common way to handle errors for macros of that form is to have error handling code section, often towards the bottom. That’s OK, nice and well organised. Just as example, something like this following macro might be the more typical way seen currently, and it is very similar to my second demo macro of the three demo macros I did a few posts up

In this and the next example I changes the demo data slightly so that I loop through an array with this data in it ,
arrNumbers(1 To 5) = {1, 0, "a", 0, 1 }
, and I try to divide 1 by each element r from the array. So I will have error with arrNumbers(2) and arrNumbers(3) and arrNumbers(4) because
1/1 - OK
1/0 – Error, divide by zero
1/"a" – Error , type mismatch
1/0 – Error, divide by zero
1/1 - OK

So this is like a common way to error handle that sort of thing:

Code: Select all

 Sub ACommonErrorHandling() '
Dim arrNumbers(1 To 5) As String, Cnt As Long, Nmber As Double
 Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = "a": Let arrNumbers(4) = 0: Let arrNumbers(5) = 1

    For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
    On Error GoTo Bed ' Get ready for stuff we might expect to error
     Let Nmber = 1 / arrNumbers(Cnt)
    On Error GoTo 0   ' Finished with custon error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
    Next Cnt

Exit Sub
Bed: ' Here I am in the parallel dark net world in the glorius State of Exception where my coding is cloned like a zombie that walks around ignoring all error handlers
 Debug.Print Err.Description & " at count " & Cnt
 Resume Next ' This both brings me back to the real world  and  takes me to the line after where the error occured
End Sub
So that is perfectly OK,
But here is a slightly more compact form. A couple of examples, almost identical.

Code: Select all

 Sub MaybeSomethingLikeYasserWants2()  '   https://eileenslounge.com/viewtopic.php?p=305638#p305638
Dim arrNumbers(1 To 5) As String, Cnt As Long, Nmber As Long
 Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = "a": Let arrNumbers(4) = 0: Let arrNumbers(5) = 1

    For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
    On Error GoTo Bed ' Get ready for stuff we might expect to error
     Let Nmber = 1 / arrNumbers(Cnt)
Bed:  If Err Then: Debug.Print Err.Description & " at count " & Cnt: On Error GoTo -1
    On Error GoTo 0   ' Finished with custom error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
    Next Cnt

End Sub
Sub MaybeSomethingLikeYasserWants3()
Dim arrNumbers(1 To 5) As String, Cnt As Long, Nmber As Long
 Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = "a": Let arrNumbers(4) = 0: Let arrNumbers(5) = 1

    For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
    On Error Resume Next ' Get ready for stuff we might expect to error
     Let Nmber = 1 / arrNumbers(Cnt)
       If Err Then: Debug.Print Err.Description & " at count " & Cnt: On Error GoTo -1
    On Error GoTo 0      ' Finished with custom error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
    Next Cnt

End Sub
Just for future reference a more detailed explanation of what is happening in the last two macros
These next code bits are just demo-ing the usual good practice of not using the custom error handling unless we think we may need it.
So we only start it before the code line that we think ,may error,
'
'' Other stuff not expected to error
'

On Error ……………. ' Get ready for stuff we might expect to error


, and then also, we typically get rid of it after, going back to the default error handling using the On Error GoTo 0 thing,

On Error GoTo 0 ' Finished with custom error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'


So that is nothing new


Now the new compact stuff:
_... in the next post…._
Last edited by DocAElstein on 17 Mar 2023, 15:09, edited 5 times in total.
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Slightly compacter way to do simple error handling in VBA

Post by DocAElstein »

_ … continued from last post

Explaining this:
If Err Then: Debug.Print Err.Description & " at count " & Cnt: On Error GoTo -1

That is the code line(s) just after where an error might occur. It’s all we need in the slightly compacter way to do simple error handling

If the error does occur, then we can think of it as if like: We are no longer in our coding anymore.
We have been transported to a dark net parallel world in the State of Exception in Transylvania. A copy of our coding goes with us and continues running.
Most VBA coding works in this parallel world. For example, the register holding information about the error type( which gets put in the error object, Err , when an error occurs ) is still accessible to us via Err.Description. And also because there is information in it, this, If Err , will return True.
So this bit, If Err , is just a convenient way for us to see if we had an error, and Then we can do something if we did have an error, for example put a message in the immediate window via Debug.Print to tell us something about the error.


After this, and at this point, is where most people make a mistake. We are like still in the dark net parallel world in the State of Exception in Transylvania. Things work a bit differently here.
Perhaps more correctly we can say we are still handling an error, and furthermore,
_ we cannot change the error handling in all the ways we can in the normal world
_ any further error will get handled in the normal default way: The last error handler we made is still actively in use, it cannot be used again or started again. If you don’t believe me, start your car, then when its still happily running, try and start it again.

These are a couple of things that still work normally in the dark net parallel world in the State of Exception in Transylvania.
_ Err.Clear works, but only does what it is supposed to – clear the register of information about the error. Nothing else.
_ On Error Goto 0 works, but it only does what it is supposed to – change the error handler back to the default in the real world. We are not in the real world, we are still in the dark net parallel world in the State of Exception in Transylvania, running a copy of our coding.

The correct thing to do at this point in order to achieve what we want is the On Error GoTo -1.
The main thing this does is to take us back to the real world.
It seems also to empty the register of the information about the error, - I am not sure why it does that. But it means we don’t need to do a Err.Clear , and never the less it is empty again so we can use it again as before with If Err to see if we have a next error.


Effectively On Error GoTo -1 takes us back to the real world and things are likely to happen as we typically expect.
Without doing that On Error GoTo -1 , ( or some Resume……. thing which appears to also do the On Error GoTo -1 ) , we are in the strange dark net parallel world in the State of Exception in Transylvania, running a copy of our coding, and things may appear spooky, with things going on , happening, or rather most typically noticed not happening , as we might expect……. The most common thing occurring, which usually surprises most people, is that it appears to us that some error handling thing we did is only working once. Error handling things of the sort On Error GoTo …… only work once. ( Maybe think of some of these things working a bit like as a 2 way toggle switch. Depending on what is going on they may get toggled, either by you or for some reason automatically. But if they are in a certain position, attempting to put them in to that position, has no effect . (I guess toggle things are quite common as all a computer is , is a lot of 2 way toggle switches) )

Alan
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

User avatar
SpeakEasy
3StarLounger
Posts: 360
Joined: 27 Jun 2021, 10:46

Re: Handling error in VBA

Post by SpeakEasy »

I'd really advise against using On Error Goto -1 if you can.

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Handling error in VBA

Post by DocAElstein »

why?
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 14410
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Handling error in VBA

Post by ChrisGreaves »

DocAElstein wrote:
17 Mar 2023, 11:26
A lot of people say things along those lines, I mean generally about not using error handling unless you really need to.
Perhaps to some extent the opinion could be influenced, at least to some extent, by most people not understanding how it works.
Three points come to mind:-
(1) In business schools the On Error Statement is usually portrayed as "how to handle division by zero"
(2) Which leads to the thought that it should not be labeled "On Error" at all, but rather "On an error that could not be anticipated ahead of time"
(3) From which follows the maxim that we ought not to be programming around an event that - by example of programing around it - could have been tested in advance.
Thus the coder who uses On Error for handling the event that a file does not exist, where that coder should have tested for the file's existence before trying to use it. It's the same code in both cases, but the On Error guy assumes the event won't happen, and scrambles to write extra code to deal with the problem when it pops up. As it will and does.

The parallel on our roads is that there are no accidents, only collisions.

Back in the early days of VBA I recognized that WordVBA had no easy means of detecting if a file existed.
The trick lay in a simple function (which I stole from someone on a BBS ...):=

Code: Select all

Public Function blnFileExists(strFileName) As Boolean
    blnFileExists = False
    On Error GoTo Failed
    If FileLen(strFileName) = FileLen(strFileName) Then
        If GetAttr(strFileName) And vbDirectory Then ' we found a folder
        Else
            blnFileExists = True
        End If
    Else
    End If
Failed:
    'Sub TESTblnFileExists()
    '    Debug.Assert Not blnFileExists(" ")
    '    Debug.Assert blnFileExists(ThisDocument.FullName)
    'End Sub
End Function
This function made use of On Error and I suggested at that time that On Error should be the province of the systems programmer/tech support programmer, and that it was valid to have On Error in a utility library, but nowhere else.
Also that the systems programmer/tech support programmer could veto the idea and take the rest of the week off if he/she could come up with a way of avoiding the need for On Error.

The ICL 1903A/GEORGE III operating system language (early 1970s) had a "Whenever" command that we practically burned out until we were reined in.

In today's environment I find it hard to come up with an example of why one would need any breed of On Error, excepting as noted in deep internal system mechanisms.
Programmers coding user applications should not come across many unforeseeable errors at all, IMNSHO.

Cheers, Chris
At long last - A rhetorical answer!

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 14410
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Handling error in VBA

Post by ChrisGreaves »

rory wrote:
17 Mar 2023, 10:20
If you (think you) need to use On Error Goto -1 you almost certainly need to redesign your code.
Agreed! :clapping:
Cheers, Chris
At long last - A rhetorical answer!

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 14410
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Slightly compacter way to do simple error handling in VBA

Post by ChrisGreaves »

DocAElstein wrote:
17 Mar 2023, 14:30
1/0 – Error, divide by zero
1/"a" – Error , type mismatch
Much as I love you etc. etc. etc. (grin)
Neither of these cases should be handled by error-trapping.

If the application requires that division by zero should not be performed (and my survey of The Universe suggests that this is indeed a no-no), then the incoming data should be tested at source.
If the data comes from 80-column punched cards, then a pre-processor checks the data and rejects (one way or another) records that hold zero divisors.
If the data arrives by electronic transfer from stores across Canada to a centre in Mississauga, then the incoming data should be tested at source.
If the data is borne by radio waves (as in WiFi), then the incoming data should be tested at source.
This was true back in the days of ADP when it was all IBM accounting machines and no computers.

The same must hold true too for "type mismatch".

What is the application program doing when it decides to pass "a" to a numeric function?
Since (except as a signed-positive-one on Hollerith cards), the letter "a" has no numeric meaning, why is the application program sending it off to be calculated? test for it by all means:-

Code: Select all

If data is not strictly numeric Then
    Throw a fault
Else
    Use it as data
End
but i see absolutely no need at all for an On Error statement here.
Cheers, Chris
At long last - A rhetorical answer!

User avatar
DocAElstein
3StarLounger
Posts: 330
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Handling error in VBA

Post by DocAElstein »

Most of the actual coding given from me here were by means of, and to help with, explaining something, that's all. ( I certainly was not suggesting using error handling to handle a possible divide by zero or type mismatch. Even I am not that ignorant in how to do things in VBA!)

I also think that you should only use error handling if you really cannot find away to do what you want without it. Almost always in VBA there is some other way, but not always.

I don’t have the examples in my head, but occasionally using error handling has been the only way someone ever came up to do something. It is almost always possible to test for something that might cause an error, rather than expecting it, and then using error handling to handle it should it indeed occur. It is rare, and mostly error handling is used too much, IMO, but I have seen such occasions, where without the use of error handling something was not possible.
I should also say that all that refers to a final coding, IMO. In development I will personally happily do just about anything good, bad, even if it breaks things badly. Get the job done. I have no limits there. Recklessly I will try everything with a computer, the worst thing is that I would trash it. Good riddance half the time. Have to live with the dammed things but I don’t have a lot of love or respect for them personally.

There not Human, - but be careful or they will kill us all.
https://www.youtube.com/watch?v=L0Fw8TVYBKg
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 14410
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Handling error in VBA

Post by ChrisGreaves »

DocAElstein wrote:
17 Mar 2023, 19:15
I also think that you should only use error handling if you really cannot find away to do what you want without it. Almost always in VBA there is some other way, but not always.
Agreed! That nifty blnFileExists function was good because we didnt have FSO at that time, and the blnFileExists could be stored in a locked utility library template as a black-box used by the application programmer.
I should also say that all that refers to a final coding, IMO.
I am skeptical. Like you I make use of all sorts of trashy stuff to explore, to discover what is going on. My VBA coding is riddled with named variables so that as a developer I can inspect what is in Local Variables and make development decisions.
That said, I am as the years go by, more and more interested to learn of a real-world application that can't "look after itself".

I have worked on commercial applications where I was assured that the incoming data was pure as the driven snow, and then spent hours building up the evidence that this was not so.
One client paid ex-managers bonus checks because it was cheaper than running the 60-minute nightly program that checked for managers who no longer worked for the company. hard to believe, but true. (A rewrite got the program down to 60 seconds, so they started running it again, but the swine never paid me half the bonus amount they'd been giving away to (sometimes) fired managers for years!.

I would happily split the cost of On Error programming over the past twenty years with you Al. heck! I'd even fly to deliver the cheque by hand - at my own expense!
Cheers, Chris
At long last - A rhetorical answer!

User avatar
HansV
Administrator
Posts: 76222
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Handling error in VBA

Post by HansV »

Of course prevention is better than cure, but it's difficult to foresee all possible problems. If you don't have error handling, the parent application could be left in an undesirable, even unusable state. For example, it is quite common in Excel code to disable automatic calculation and event handling temporarily. If an unexpected error occurs and the user has to stop macro execution, they might with a bit of luck be able to enable automatic calculation again, but probably not event handling...
So a catch-all error handler is useful for unforeseen situations. After all, stuff happens...

And, also in Excel, using On Error Resume Next to find out whether a specific sheet does or does not exist is more efficient than looping through the sheets and testing the name.
Regards,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 14410
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Handling error in VBA

Post by ChrisGreaves »

HansV wrote:
17 Mar 2023, 19:43
... but it's difficult to foresee all possible problems. If you don't have error handling, the parent application could be left in an undesirable, even unusable state.
Thanks for this, Hans.
I think that there are two aspects to "foresee all possible problems".

(1) Proper analysis of the problem should provide acceptance tests before programming starts.
The acceptance test says "if this data is put in, then this information will come out".
If the analyst is doing their job, then many chats with the users and with management should ferret out what needs to be done. Better yet management bases payment on the analysts specs; then when the program fails because management didn't disclose exceptional cases, the analyst cannot be faulted.

(2) For events such as a mis-directed cosmic ray, by all means have some sort of error reporting. In the Good Old days operators were told to print a core-dump and put it on the programmers desk before 8am. Crude but effective.
I suspect that when an unanticipated crash occurs, it is faster to have a/the programmer track down why and then apply the fix.

I had a 14-year old payroll program in the UK in 1978. Kept crashing with an underflow of 83,886.08 UK pounds. No-one realised that that was two to the power 24, and the machine had a 24-bit word. When the program first was written labourers were paid a quarter to a half of what they earned in 1978, hence the overflow. Real solution: change all one-word COMPUTATIONAL to two-word COMPUTATIONAL in the data division and get on with life.

I could call this an unforeseeable error, but the first time it occurred and was fixed it ought to have been fixed in all data instances.

Spreadsheets are my special gripe (grin) I have handled so many spreadsheet where the users believe that because "the computer says so, and the computers never make mistakes, well, the profit prediction must be right" that I gag.
And, also in Excel, using On Error Resume Next to find out whether a specific sheet does or does not exist is more efficient than looping through the sheets and testing the name.
This sounds like a deficit in Excel's design. Like the blnFileExists example.
In this case I would recommend a utility function in a library (UT.XLA) and take On Error out of the hands of the application programmer.

I would love for someone to come up with a genuine case where an application programmer needs to make use of On Error. To the best of my experience, an On Error in an application program always has a solution up-stream of where the problem is said to be. Data validation on input is a classic example.


Cheers, Chris
At long last - A rhetorical answer!

User avatar
HansV
Administrator
Posts: 76222
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Handling error in VBA

Post by HansV »

"If you design something to be idiot proof, the universe will design a better idiot."
Regards,
Hans