VBA data layer error handling

Question

I have some legacy Excel files with tons of calls to stored procedures and db connections all done in the old fashion with recordsets...

since in VBA there is no concept of try-catch-finally like in .NET is there any best practice to apply to have a bit more solid data access layer? I would like to have a nice example on how to open connection, execute a stored procedure which requires some parameters and then in any case of success or errors, close the connection and release resources.

@Charles Williams has already provided a fine answer. Here are some links that help flesh out how EH in Excel works in general. In short, it's *syntax* is quite different from e.g. .NET, but the concepts involved are mostly the same. See http://stackoverflow.com/questions/4429965/handling-errors-in-math-functions/4432413#4432413 for example. You can also do RAII, as in e.g. C++. See: http://stackoverflow.com/questions/3792134/get-name-of-current-vba-function/3792280#3792280 (RAII isn't used directly in .NET but the concepts behind it are covered by IDisposable and 'Using'.) — jtolle, Feb 02 '11 at 16:42
You can simulate a TRY CATCH block in VBA Please see here http://stackoverflow.com/q/30991653/4413676 — HarveyFrench, Jun 22 '15 at 23:47

score 8 · Answer 1 · answered Feb 02 '11 at 15:09

8

In VBA you have to use On Error blocks: its messy but it works

On Error Goto ConnectionFail
' open connection
on Error GoTo 0
...
On Error GoTo QueryFail
' execute querys etc
on Error goto 0
...
Cleanup:
' close connection destroy objects etc
Exit Sub
ConnectionFail
On Error GoTo 0
' handle failure
GoTo Cleanup
QueryFail
On Error GoTo 0
' handle failure
GoTo Cleanup
End Sub

answered Feb 02 '11 at 15:09

Charles Williams

23,121
5
38
38

@Davide, 'On Error Goto 0' "turns off" a previous 'On Error Goto '. Think of the latter as roughly like a .NET 'Try'. The is roughly like 'Catch', except that it catches all errors and you have to discriminate in the code you put there. (See the first link I gave you in the comment above.) Lots of time you'll avoid 'On Error Goto 0' by putting, say, your connection code in a separate routine from your query code. Of course this is good practive in .NET or any other system too... – jtolle Feb 02 '11 at 16:53

score 4 · Accepted Answer · answered Feb 02 '11 at 17:06

I am actually using a different approach now, I created a .NET managed data layer and exported it as COM class, following the instructions here: Calling .NET from VBA in this way I have to deploy and register the COM assembly together with the excel file and it's a bit painful I admit, but at least I can handle data acces in a proper way and use C#, ADO.NET and so on...

VBA data layer error handling

2 Answers2

Linked