Books at Amazon.com
Error Free Excel VBA
Now that you've had a good laugh at the title of this page ("Error Free VBA"), I'll point out that although you cannot make your VBA code error-free, you can at least try to make it error-resistant. In my Quick Chart VBA page and other pages on this site, I've presented some VBA procedures to help create and modify your charts. In this page I will outline a few basic techniques to reduce the effects of errors in the use of your code.
On Error Resume Next
For our first example, let's try to activate a chart sheet. The following code will activate the chart sheet named "Chart1".
Sub ActivateChartSheet1() Charts("Chart1").Activate End Sub
This works just fine, as long as there is a chart sheet named "Chart1". But if there is no such chart, you get the following error message:
If this message is unclear to you and me, it must be completely inscrutable to the unfortunate user who only thought he was activating a chart. You can use a simple statement, On Error Resume Next, which ignores the error and continues processing with the step after the line that caused the error. Then you can determine what the error was, and react accordingly.
Sub ActivateChartSheet2() On Error Resume Next Charts("Chart1").Activate If Err.Number <> 0 Then MsgBox "Requested chart does not exist.", _ vbExclamation, "No Such Chart" End If On Error Goto 0 End Sub
The If statement determines whether there was an error, and what the error was. If there is no error up until that point, Err.Number is zero, and the message box is not shown. All we did here was notify the user that the chart sheet was not found, as shown in the following message box:
The message box shows that the programmer gave at least some small thought to how the procedure might fall short, and might actually reduce the frustration felt by the user.
We certainly could get fancier; some statements might lead to more than one error, and we would need to provide a different message, or follow a different set of subsequent actions, depending on the error that was encountered. The following procedure illustrates the use of Select Case to provide error-specific messages to the user.
Sub ActivateChartSheet3() On Error Resume Next Charts("Chart1").Activate If Err.Number <> 0 Then Select Case Err.Number Case 9 MsgBox "Requested chart does not exist.", _ vbExclamation, "No Such Chart" Case Else MsgBox "Couldn't activate chart sheet.", _ vbExclamation, "I Don't Know Why!" End Select End If End Sub
Is There an Active Chart?
Many of the charting macros I've demonstrated on these pages work on the active chart. For example, to add a chart title and axis titles to the following chart:
I would write something like this macro:
Sub AddTitle1() With ActiveChart .HasTitle = True .ChartTitle.Text = "Chart Title" With .Axes(xlCategory, xlPrimary) .HasTitle = True .AxisTitle.Text = "Category (X) Axis" End With With .Axes(xlValue, xlPrimary) .HasTitle = True .AxisTitle.Text = "Value (Y) Axis" End With End With End Sub
with the following result:
If the user has not selected a chart prior to running the code, however, there is no ActiveChart, and the code will fail, with the following error message:
"Oh, no," thinks the user, "what is wrong with this awful code?" You can check for an active chart, and either work on the active chart, or remind the user that he needs to select a chart first.
Sub AddTitle2() If Not ActiveChart Is Nothing Then With ActiveChart .HasTitle = True .ChartTitle.Text = "Chart Title" With .Axes(xlCategory, xlPrimary) .HasTitle = True .AxisTitle.Text = "Category (X) Axis" End With With .Axes(xlValue, xlPrimary) .HasTitle = True .AxisTitle.Text = "Value (Y) Axis" End With End With Else MsgBox "Please select a chart and try again.", _ vbExclamation, "No Chart Selected" End If End Sub
When this message box pops up, the user now thinks, "I forgot to select a chart again!"
Is the Correct Object Selected?
Suppose we take the nice chart above, and change to series formatting so we have a thick blue line connecting large blue square markers. This macro will make short work of our task:
Sub BlueSquares1() With Selection .MarkerBackgroundColorIndex = 5 .MarkerForegroundColorIndex = 5 .MarkerStyle = xlSquare .Smooth = False .MarkerSize = 6 .Shadow = False With .Border .ColorIndex = 5 .Weight = xlThin .LineStyle = xlContinuous End With End With End Sub
And here's the chart:
But if no series has been selected, the code will crash:
Says the user, "What object doesn't support WHAT property or method??" Using the TypeName function, we can check whether a chart series has been selected.
Sub BlueSquares2() If TypeName(Selection) = "Series" Then With Selection .MarkerBackgroundColorIndex = 5 .MarkerForegroundColorIndex = 5 .MarkerStyle = xlSquare .Smooth = False .MarkerSize = 6 .Shadow = False With .Border .ColorIndex = 5 .Weight = xlThin .LineStyle = xlContinuous End With End With Else MsgBox "Please select a series and try again.", _ vbExclamation, "No Series Selected" End If End Sub
The subsequent message box reminds the user that he has forgotten an important step.
There is certainly more to error checking than always reminding the user that he forgot to do something. Well-considered code should follow a more organized process so that the user never feels he has done something to cause him to start over from scratch. This requires substantial testing and anticipation about how the user might actually use the macros. A typical approach might follow these steps:
' PseudoCode Is a Chart Selected? If not: Display UserForm with List of Charts on the Active Sheet User Selects a Chart Is a Series Selected? If not: Display UserForm with List of Series in the Active Chart User Selects a Series Perform the Intended Actions on the Selected Series of the Selected Chart ' Done
Short of a concerted error-proofing approach like this, using the techniques described above will still prevent a large proportion of the unexpected failures your code may encounter.
Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.