Error Handling Newbie Q (VBA 6)

Topic

New Reply

Folks,

I’m new at VBA programming, but I’m trying to do things right. Hence error handling. Maybe I’m obsessive, but I find that a lot of my routines are taken up by repetitive error handling code. My error handler looks like this:

Private Sub procErrorHandler( _
ByRef intErrorNumber As Integer, _
ByRef strErrorDescription As String, _
ByRef strUserError As String, _
ByRef blnErrorFlag As Boolean _
)

If intErrorNumber 0 Then

MsgBox “Runtime Error Number ” & intErrorNumber & ” occurred. ” & _
“Description: ” & strErrorDescription & “.”, _
vbOKOnly, “Error:”

End If

If strUserError “” Then

blnErrorFlag = True
MsgBox strUserError, vbOKOnly, “Error”

End If

In the calling routine I do the following:

On Error GoTo ErrorHandler

‘Set up error-related variables.
intErrorNumber = 0
strErrorDescription = “”
strUserError = “”
blnErrorFlag = False

ErrorHandler:

intErrorNumber = Err.Number
strErrorDescription = Err.Description

Call procErrorHandler(intErrorNumber, strErrorDescription, strUserError, blnErrorFlag)

GoTo LeaveFunction

LeaveFunction:
Exit Function

End Sub

This is a lot of overhead, since I do it for each procedure. Also, it makes the code messy, obscuring the real work the procedure does. Surely someone has come up with a better mousetrap. But although I have looked (really), I haven't found it.

I imagine, by the way, that I ought to declare ErrorHandler Public and maybe it and some of my variables so that they are less generic.

TIA

Reply | Quote

Replies

I don’t see the advantage of this method; it is a lot of overhead for little gain. For simple error handling, a few fixed lines are sufficient, and for more complicated error handling, you will probably need a specialized Select Case statement.

A utility such as MZ-Tools (free!) lets you insert error handling into a procedure with one mouse click; the text of the error handler is customizable.

Reply | Quote

Dear Hans,

Yes, I figured this was a kluge, hence my question. But really, I’ve been using it as an exercise to learn VB6, at which, as I said, I’m a beginner. I’ve downloaded MZ-Tools, and I’ll take a close look at it. It seems quite useful.

More generally, I wonder if you could recommend some additional resources. I can’t see much material between such beginner’s tools as Absolute Beginner’s Guide to VBA and Excel handbooks like Using MS Excel 2003 SE, both of which I’ve read, and the really advanced developer’s handbooks. The beginner’s stuff is a bit handwaving and terse. The advanced books are far beyond my needs and capabilities. Appropriate forums would be helpful, too.

Thanks for any help you can give.

Reply | Quote

I’m not really a VB6 expert, but I’m sure others will have useful suggestions.

Reply | Quote

I’m not really a VB6 expert, but I’m sure others will have useful suggestions.

Reply | Quote

The best way to learn is by studying code written by people who know what they are doing. Browsing the MVP web site, or Woody’s Lounge are good for this.

StuartR

Reply | Quote

The best way to learn is by studying code written by people who know what they are doing. Browsing the MVP web site, or Woody’s Lounge are good for this.

StuartR

Reply | Quote

A couple of comments about your code:

1- I agree with Hans in that since the code does approzimately exactly what the default error handler does, what you are doing is a lot of overhead for little or no gain.

2- You exit from your error handler by using Exit Function. You should NOT exit this way. You should use a Resume statement to get out of interrupt mode. What you are doing leaves VB in Interrupt mode and could cause major problems in other code. I am more of a VBA programmer than a VB programmer and it has been a number of years since I wrote anything in VB, however, I believe that VB uses the same Resume statement as VBA.

3- You should initialize your errorhandling variables BEFORE you use On Error to set error handling. That way, if an admittedly unlikely error occurs when initializing the variables, you won’t go to the error handler with uninitilized variables.

Reply | Quote

Folks,

Point taken on the Resume and on the initialization of errorhandler variables. I’ll keep this in mind in future.

Re VBA, I think we’re actually on the same page here: I’m using VB out of Excel 2003, which I presume is VBA. Sorry for the confusion, but Help|About in the VB editor identifies it as VB 6.3. Hence the confusion.

I’ve gone to the suggested websites, and I’m working through them. They look like they could be very helpful. But perhaps you could give me a hand with one really small but frustrating problem while I do. It’s hanging me up from further experimenting, and I can’t seem to find an answer anywhere.

What I want to do is set some values in the sheet from which I call a function.

The formula in the worksheet cell is:

=test(C10)

where test is the following user-defined function:

Private Function test(ByRef rngOne As Range) As Integer

test = 0
rngOne.Value = 91
test = 1

Exit Function

rngOne.Value= 91 triggers error 1004, which is an error in the underlying application. Comment out mgOne.Value=91, and the function works fine.

Obviously, I am doing something wrong, and that something is very elementary. Ordinarily, I’d track it down in the websites you suggested, but so far all of them use references in the form “AB33” (presumably to clarify things for the student) or make a reference to the ActiveSheet (which I presume may or may not be the one in which my function finds itself). Also, getting this thing to work soon is pretty important. So, at the risk of appearing really dumb, I’m asking how do I pass a value back to a worksheet cell other than the one calling the function?

Reply | Quote

I don’t think that a user defined function is supposed to manipulate the worksheet directly, it should just return a value.

I have done something similar to this using the Workbook_Sheet_Change event. This enabled me to look at the cell that had changed and everything else on the workbook to decide what to put in some completely different cell, but I also found that using this as well as User Defined Functions didn’t do what I expected!

StuartR

Reply | Quote

Stuart,

Thanks very much for this. Two thoughts. First, I’ve tried embedding a sub in the function. The function just passed the range to the sub which then did what I had the function do originally. Here’s the code:

Private Function test(ByRef rngOne As Range) As Integer

On Error GoTo test_Error

test = 0
‘rngOne.Formula = 91
test = 1

Call Test2(rngOne)

Exit Function

test_Error:

MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure test of Module Module1”

End Function

Private Sub Test2(ByRef rngOne As Range)

Dim intcheckit As Integer

On Error GoTo test_Error

intcheckit = 0
rngOne.Formula = 91
intcheckit = 1

Exit Sub

test_Error:

MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure test of Module Module1”

End Sub

Same 1004 error.

Also, this code does work:

Private Sub FirstProg()
Dim i, j As Integer
ThisWorkbook.Worksheets(“Sheet1”).Cells.Clear
With ThisWorkbook.Worksheets(“Sheet1”)
For i = 1 To 12
.Cells(i, 2).Value = Chr(96 + i) & “)”
For j = 3 To 6
.Cells(i, j).Value = CStr(i) & ” x ” & CStr(j) & ” = ” & CStr(i * j)
Next j
Next i
End With
End Sub

So, I’m confused.

Reply | Quote

Stuart is correct. You cannot directly change the values of other cells by using a formula. It doesn’t matter whether you try to change the value in the function itself, or in a procedure called from the function, even if that procedure works when called by itself. This is by intent; the way worksheet functions are evaluated blocks direct changes to the value of other cells.

To execute the code, you can assign a macro (= procedure without arguments) to a command button on the worksheet, or, if you want it to be executed automatically in specific circumstances, use the Worksheet_Change event. We can help you with that if you indicate what the specific circumstances are.

Reply | Quote

Stuart and Hans,

Okay. Then I do need help. This is what I am trying to do. I need to model an industrial process and payment for it. The process can take a varying length of days, payment periods depend on the producer, the event from which payment is calculated also varies (commencement/completion), currency of payment may change, whether payment is based on days consumed in the process or is per batch regardless of time required, etc. It seemed easier to code a function that calculated all of the above for a given batch than to do it in Excel. Also clearer and easier to debug.

This is especially true since to do this in Excel, you have to look back to determine if an event (commencement of the process) has occurred and then determine which payment initiation event, grace period, etc. is relevant. Just looking at whether a batch has been started and then using the values prevailing at that date for all the variables doesn’t work. You must also check if a batch has been completed, since this, too, is a possible payment initiation event. And, here’s what really drove me to VBA, what if payment was originally computed from batch production start, but terms changed before batch completion to charge from completion? How do I tell Excel to ignore the change until the next batch?

It just seemed vastly easier to code this process than to kluge it up in Excel. Which, by the way, is why I’ve been off the screen for a while. I’ve decided to ignore a few possible wrinkles and do it in Excel for the time being. But I really would like to get a coded version going. As I see it, instead of looking back a VBA version would be called from the current period cell, examine current variable values and set the values of cells in the future appropriately (e.g., put the appropriate payment amount in a cell corresponding to the appropriate payment date) and possibly set a flag that says don’t change the payment calculation event until further notice no matter what the current cell says re that issue.

Thanks again for all your assistance. It’s been massively helpful.

Reply | Quote

There is an example in post 347814 showing how to create and use a WithEvents class module.

In your class module you can create a SheetChange procedure

Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Call your procedure in a standard module here, pass it the values of Sh and Target
End Sub

You can then write code that looks to see which cell on which sheet was changed, and takes any action you like.

The App_SheetChange procedure will be called every time there is any change to any cell on any sheet in any workbook, so you need to make your code is very fast and efficient, especially the bit that detects this change was nothing to do with you and exits.

StuartR

Reply | Quote

If the cells you want to monitor are on one worksheet, you can use the Worksheet_Change event of that worksheet. This is simpler than writing the WithEvents code StuartR refers to, but is more limited.

To do so:
– Right-click the sheet tab at the bottom of the worksheet.
– Select View Code from the popup menu.
– This will activate the Visual Basic Editor and open the code module associated with the worksheet.
– Type code like this, or copy / paste it from this post and adapt it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Application.EnableEvents = False
' Code to perform if B2 has changed goes here
' ...
Application.EnableEvents = True
End If
End Sub

– Press Alt+F11 to return to Excel and test.
– StuartR’s admonition to keep the code short and simple holds here too.

Reply | Quote

Dear Stuart and Hans,

Thanks lots for this. I will give it a try as soon as I finalize the pure Excel version I’m working on now. Obviously this is going to be a bit more complicate than I hoped. But the help you’ve given me is very encouraging, and maybe I’ll get the real thing working after all!

Reply | Quote

Good luck! Don’t hesitate to ask questions if you need more help.

Reply | Quote

Good luck! Don’t hesitate to ask questions if you need more help.

Reply | Quote

If the cells you want to monitor are on one worksheet, you can use the Worksheet_Change event of that worksheet. This is simpler than writing the WithEvents code StuartR refers to, but is more limited.

To do so:
– Right-click the sheet tab at the bottom of the worksheet.
– Select View Code from the popup menu.
– This will activate the Visual Basic Editor and open the code module associated with the worksheet.
– Type code like this, or copy / paste it from this post and adapt it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Application.EnableEvents = False
' Code to perform if B2 has changed goes here
' ...
Application.EnableEvents = True
End If
End Sub

– Press Alt+F11 to return to Excel and test.
– StuartR’s admonition to keep the code short and simple holds here too.

Reply | Quote

Stuart and Hans,

Okay. Then I do need help. This is what I am trying to do. I need to model an industrial process and payment for it. The process can take a varying length of days, payment periods depend on the producer, the event from which payment is calculated also varies (commencement/completion), currency of payment may change, whether payment is based on days consumed in the process or is per batch regardless of time required, etc. It seemed easier to code a function that calculated all of the above for a given batch than to do it in Excel. Also clearer and easier to debug.

This is especially true since to do this in Excel, you have to look back to determine if an event (commencement of the process) has occurred and then determine which payment initiation event, grace period, etc. is relevant. Just looking at whether a batch has been started and then using the values prevailing at that date for all the variables doesn’t work. You must also check if a batch has been completed, since this, too, is a possible payment initiation event. And, here’s what really drove me to VBA, what if payment was originally computed from batch production start, but terms changed before batch completion to charge from completion? How do I tell Excel to ignore the change until the next batch?

It just seemed vastly easier to code this process than to kluge it up in Excel. Which, by the way, is why I’ve been off the screen for a while. I’ve decided to ignore a few possible wrinkles and do it in Excel for the time being. But I really would like to get a coded version going. As I see it, instead of looking back a VBA version would be called from the current period cell, examine current variable values and set the values of cells in the future appropriately (e.g., put the appropriate payment amount in a cell corresponding to the appropriate payment date) and possibly set a flag that says don’t change the payment calculation event until further notice no matter what the current cell says re that issue.

Thanks again for all your assistance. It’s been massively helpful.

Reply | Quote

Stuart is correct. You cannot directly change the values of other cells by using a formula. It doesn’t matter whether you try to change the value in the function itself, or in a procedure called from the function, even if that procedure works when called by itself. This is by intent; the way worksheet functions are evaluated blocks direct changes to the value of other cells.

To execute the code, you can assign a macro (= procedure without arguments) to a command button on the worksheet, or, if you want it to be executed automatically in specific circumstances, use the Worksheet_Change event. We can help you with that if you indicate what the specific circumstances are.

Reply | Quote

Stuart,

Thanks very much for this. Two thoughts. First, I’ve tried embedding a sub in the function. The function just passed the range to the sub which then did what I had the function do originally. Here’s the code:

Private Function test(ByRef rngOne As Range) As Integer

On Error GoTo test_Error

test = 0
‘rngOne.Formula = 91
test = 1

Call Test2(rngOne)

Exit Function

test_Error:

MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure test of Module Module1”

End Function

Private Sub Test2(ByRef rngOne As Range)

Dim intcheckit As Integer

On Error GoTo test_Error

intcheckit = 0
rngOne.Formula = 91
intcheckit = 1

Exit Sub

test_Error:

MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure test of Module Module1”

End Sub

Same 1004 error.

Also, this code does work:

Private Sub FirstProg()
Dim i, j As Integer
ThisWorkbook.Worksheets(“Sheet1”).Cells.Clear
With ThisWorkbook.Worksheets(“Sheet1”)
For i = 1 To 12
.Cells(i, 2).Value = Chr(96 + i) & “)”
For j = 3 To 6
.Cells(i, j).Value = CStr(i) & ” x ” & CStr(j) & ” = ” & CStr(i * j)
Next j
Next i
End With
End Sub

So, I’m confused.

Reply | Quote

I don’t think that a user defined function is supposed to manipulate the worksheet directly, it should just return a value.

I have done something similar to this using the Workbook_Sheet_Change event. This enabled me to look at the cell that had changed and everything else on the workbook to decide what to put in some completely different cell, but I also found that using this as well as User Defined Functions didn’t do what I expected!

StuartR

Reply | Quote

About the confusion: it is true that the version mentioned in Help | About… in the Visual Basic Editor is “Visual Basic 6.3”. But when people speak about VB6, they usually mean the Visual Basic compiler, part of Visual Studio. The underlying programming language is the same, but with VB6, you can make stand-alone applications, while the VBA versions for Office applications contain the object model for the Office app, and only work when that app is running.

Reply | Quote

About the confusion: it is true that the version mentioned in Help | About… in the Visual Basic Editor is “Visual Basic 6.3”. But when people speak about VB6, they usually mean the Visual Basic compiler, part of Visual Studio. The underlying programming language is the same, but with VB6, you can make stand-alone applications, while the VBA versions for Office applications contain the object model for the Office app, and only work when that app is running.

Reply | Quote

Folks,

Point taken on the Resume and on the initialization of errorhandler variables. I’ll keep this in mind in future.

Re VBA, I think we’re actually on the same page here: I’m using VB out of Excel 2003, which I presume is VBA. Sorry for the confusion, but Help|About in the VB editor identifies it as VB 6.3. Hence the confusion.

I’ve gone to the suggested websites, and I’m working through them. They look like they could be very helpful. But perhaps you could give me a hand with one really small but frustrating problem while I do. It’s hanging me up from further experimenting, and I can’t seem to find an answer anywhere.

What I want to do is set some values in the sheet from which I call a function.

The formula in the worksheet cell is:

=test(C10)

where test is the following user-defined function:

Private Function test(ByRef rngOne As Range) As Integer

test = 0
rngOne.Value = 91
test = 1

Exit Function

rngOne.Value= 91 triggers error 1004, which is an error in the underlying application. Comment out mgOne.Value=91, and the function works fine.

Obviously, I am doing something wrong, and that something is very elementary. Ordinarily, I’d track it down in the websites you suggested, but so far all of them use references in the form “AB33” (presumably to clarify things for the student) or make a reference to the ActiveSheet (which I presume may or may not be the one in which my function finds itself). Also, getting this thing to work soon is pretty important. So, at the risk of appearing really dumb, I’m asking how do I pass a value back to a worksheet cell other than the one calling the function?

Reply | Quote

A couple of comments about your code:

1- I agree with Hans in that since the code does approzimately exactly what the default error handler does, what you are doing is a lot of overhead for little or no gain.

2- You exit from your error handler by using Exit Function. You should NOT exit this way. You should use a Resume statement to get out of interrupt mode. What you are doing leaves VB in Interrupt mode and could cause major problems in other code. I am more of a VBA programmer than a VB programmer and it has been a number of years since I wrote anything in VB, however, I believe that VB uses the same Resume statement as VBA.

3- You should initialize your errorhandling variables BEFORE you use On Error to set error handling. That way, if an admittedly unlikely error occurs when initializing the variables, you won’t go to the error handler with uninitilized variables.

Reply | Quote

Dear Hans,

Yes, I figured this was a kluge, hence my question. But really, I’ve been using it as an exercise to learn VB6, at which, as I said, I’m a beginner. I’ve downloaded MZ-Tools, and I’ll take a close look at it. It seems quite useful.

More generally, I wonder if you could recommend some additional resources. I can’t see much material between such beginner’s tools as Absolute Beginner’s Guide to VBA and Excel handbooks like Using MS Excel 2003 SE, both of which I’ve read, and the really advanced developer’s handbooks. The beginner’s stuff is a bit handwaving and terse. The advanced books are far beyond my needs and capabilities. Appropriate forums would be helpful, too.

Thanks for any help you can give.

Reply | Quote