The basics needed to be a good Excel programmer

[xenou]

I know its been asked before but I am in a hurry...

What would a "student" need to know in order to be a good excel programmer. Imagine you have 3 weeks in which to teach it.

 

[Celly]

I've never seen anyone teach people to use On Error Goto -1. Most people don't even know it exists in VBA. (and I'd just clarify it's VBA error handling, not Excel ) IMO, if you're using that, you've dug yourself into a hole with some bad code and gone looking round the internet for a solution that didn't involve rewriting your code properly.

I actually have found a use for it. Here's an example:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    On Error GoTo NoFoo1
      
    Call CallByName(ws, "Foo1", VbMethod)

    Call Foo2()
      
NoFoo1:
    On Error GoTo -1
    On Error GoTo 0
          
Next ws

So this pattern iterates over the sheets and attempts to invoke a sheet function Foo1. If and only if successful, it then calls Foo2 (on error resume next won't accomplish this).

Traditionally the NoFoo1 handler is moved to the end of the subroutine, and you need to add a resume clause to skip Foo2. This is actually more difficult to follow than the above example, and much more difficult if there are several loops employing the same pattern all in the same function. This way encapsulates the logic better.

The on error goto -1 clears the active error handler state. I believe the on error goto NoFoo1 is active at that point, and the on error goto 0 removes that before the loop is exited. It's all very clumsy.

 

[Celly]

I did a quick test and the on error goto 0 is definitely needed following the on error goto -1. It is not redundant. The on error goto -1 clears the error handler state and restores the previous error handler. Then the on error goto 0 clears that error handler. Really a different command should have been built into the language, that's just lazy.

So in performing a code review it looks odd and non-intuitive. But it is still better than goto end of function, goto back into middle of function. Backward gotos (including Resume "line") are bad form and violate many coding standards including MISRA.

I'll take try/catch clauses over this any day, but whaddyagonnado.

 

[diddi]

i removed GOTO from my vocab when i retired my Fortran/Cobol/Basic/Pascal card reader

 

[RoryA]

There are simpler ways, such as moving your Callbyname code into a separate function that returns true if it succeeds.

 

[Celly]

There are simpler ways, such as moving your Callbyname code into a separate function that returns true if it succeeds.

Perhaps, but that adds wrapper functions and can create a bucket brigade of return code handling.

Here's a tighter version of my example which allows the possibility of extended error handling (Foo3 function):

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    On Error GoTo NoFoo1
      
    Call CallByName(ws, "Foo1", VbMethod)

    Call Foo2()
      
NoFoo1:
    
    If Err.Number <> 0 Then
        Call Foo3(Err.Number)  
        On Error GoTo -1    ' this clears Err.Number
    End If

    On Error GoTo 0
          
Next ws

Not too bad, only requires one branch label. If VBA had a continue statement, it could be employed to remove the err.number conditional branch.

Now in an alternate universe where VBA has exception handling, it might look something like this:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    try
    {
        Call CallByName(ws, "Foo1", VbMethod)
        Call Foo2()
    }
    catch (ErrObject Err)
    {
        Call Foo3(Err.Number)
    }
      
Next ws

Better, no branch labels or err.number conditional branch needed.

 

[xenou]

Hmmm I am one of those who didn't know on error goto -1 existed...

Is this the same as on error goto 0? I don't even see -1 mentioned in the docs: On Error statement (VBA)

 

[RoryA]

No, On Error Goto 0 clears the current error handler, whereas On Error Goto -1 clears the current exception.

 

[Celly]

Hmmm I am one of those who didn't know on error goto -1 existed...

Is this the same as on error goto 0? I don't even see -1 mentioned in the docs: On Error statement (VBA)

It's a bit arcane apparently.

"On error resume next" is odd. It basically prevents any entering of the exception state and subsequent branching. So you cannot "resume line" in that state. However, the exception info is left in the Err object and "on error goto -1" can still clear it.

So the "Err.number <> 0" test does not universally indicate you are in the exception state. It depends on how you got there.