Macro's checking for errors

B

Brad

Are there standard checks for errors that the macro's below should have?

An application is being built to be used by others and the following code
works when everything is entered correctly. I have tried to anticipate some
of the problems caused by individual not using valid information. Please
note that data validation is being used extensively on the input fields - to
ensure that valid data is being entered.


Sub Print_pages()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Input" And sht.Visible = True Then
sht.PrintOut
End If
Next
End Sub

Sub change_agent()
Sheets("Agent_information").Visible = True
ActiveWorkbook.Worksheets("Agent_information").Select
End Sub

Sub check_error()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Input" Then
sht.Visible = False
End If
Next

If Range("input_info!valid").value <> 0 Then
Sheets("input").[button 17].Visible = False
Beep
End If

If Range("input_info!valid").value = 0 Then
Sheets("input").[button 17].Visible = True
Sheets("input").[button 248].Visible = True
Select Case Range("product").value
Case Is = "Capital_Bonus_2"
Sheets("CB2_Disclosure").Visible = True
Sheets("CB2_Values").Visible = True
Case Is = "Maximum_Solutions_II"
Sheets("Max2_Disclosure").Visible = True
Sheets("Max2_Values").Visible = True
Case Is = "Expanding_Horizon_5"
Sheets("EH5_Disclosure").Visible = True
Sheets("EH5_Values").Visible = True
Case Is = "Expanding_Horizon_7"
Sheets("EH7_Disclosure").Visible = True
Sheets("EH7_Values").Visible = True
Case Is = "GPA_5Yr"
Sheets("GPA5_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "GPA_9Yr"
Sheets("GPA9_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "GPA_Seminole_County"
Sheets("GPASC_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "MY_Guaranteed_Solution_II"
Sheets("MYGS_Disclosure").Visible = True
Sheets("MYGS_Values").Visible = True
Sheets("input").[button 248].Visible = False
Case Else
MsgBox "Not a valid plan"
End Select
End If
End Sub

Sub monthly_income_benefit_accumulation()
Worksheets("input_info").Range("c50").GoalSeek
goal:=Worksheets("input_info").Range("c52"), _
Changingcell:=Worksheets("input").Range("c13")
Application.ScreenUpdating = True
Worksheets("input").Range("c13") =
Application.WorksheetFunction.RoundUp(Worksheets("input").Range("c13"), 2)
If Worksheets("input").Range("c13") < 0 Then
Worksheets("input").Range("c13") = 0
End If
End Sub
 
J

Jim Thomlinson

The biggest part of error handling is avoiding errors in the first place. As
soon as a error occures then everything that follows is potentially
problematic. The biggest thing I see in your code is that you are refering to
sheets by their tab name which will cause a problem if someone decides to
change a sheet name. You should change things (IMO) to reference the sheets
by their code name. In the VBE Explorer window you will see all of your
sheets listed something like this...

Sheet1(Sales)

Sheet1 is the code name and Sales is the name property of that sheet. If you
select that sheet by double clicking on it in the properties window you will
see that the first item is (Name) Sheet1. Change Sheet1 to something more
meaningful like shtSales. Now you can refer to the sheet directly by it's
code name something like this

msgbox shtSales.Range("A1").Value
Which is the same as
msgbox Sheets("Sales").Range("A1").Value
Except that the fist line is bullet proof as it does not rely on anything
that the user can change.

Additionally you will notice that when you type shtSales. you will get the
intellisence drop down of all of the properties and methods of the sheet
which you don't get with Sheets("Sales").

Beyound that you should consider adding an error handler to your code to
catch an error if it occures in your code and allow the code to exit
gracefully without causing damage. Check out this link for more info on error
handlers...

http://www.cpearson.com/excel/ErrorHandling.htm
--
HTH...

Jim Thomlinson


Brad said:
Are there standard checks for errors that the macro's below should have?

An application is being built to be used by others and the following code
works when everything is entered correctly. I have tried to anticipate some
of the problems caused by individual not using valid information. Please
note that data validation is being used extensively on the input fields - to
ensure that valid data is being entered.


Sub Print_pages()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Input" And sht.Visible = True Then
sht.PrintOut
End If
Next
End Sub

Sub change_agent()
Sheets("Agent_information").Visible = True
ActiveWorkbook.Worksheets("Agent_information").Select
End Sub

Sub check_error()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Input" Then
sht.Visible = False
End If
Next

If Range("input_info!valid").value <> 0 Then
Sheets("input").[button 17].Visible = False
Beep
End If

If Range("input_info!valid").value = 0 Then
Sheets("input").[button 17].Visible = True
Sheets("input").[button 248].Visible = True
Select Case Range("product").value
Case Is = "Capital_Bonus_2"
Sheets("CB2_Disclosure").Visible = True
Sheets("CB2_Values").Visible = True
Case Is = "Maximum_Solutions_II"
Sheets("Max2_Disclosure").Visible = True
Sheets("Max2_Values").Visible = True
Case Is = "Expanding_Horizon_5"
Sheets("EH5_Disclosure").Visible = True
Sheets("EH5_Values").Visible = True
Case Is = "Expanding_Horizon_7"
Sheets("EH7_Disclosure").Visible = True
Sheets("EH7_Values").Visible = True
Case Is = "GPA_5Yr"
Sheets("GPA5_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "GPA_9Yr"
Sheets("GPA9_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "GPA_Seminole_County"
Sheets("GPASC_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "MY_Guaranteed_Solution_II"
Sheets("MYGS_Disclosure").Visible = True
Sheets("MYGS_Values").Visible = True
Sheets("input").[button 248].Visible = False
Case Else
MsgBox "Not a valid plan"
End Select
End If
End Sub

Sub monthly_income_benefit_accumulation()
Worksheets("input_info").Range("c50").GoalSeek
goal:=Worksheets("input_info").Range("c52"), _
Changingcell:=Worksheets("input").Range("c13")
Application.ScreenUpdating = True
Worksheets("input").Range("c13") =
Application.WorksheetFunction.RoundUp(Worksheets("input").Range("c13"), 2)
If Worksheets("input").Range("c13") < 0 Then
Worksheets("input").Range("c13") = 0
End If
End Sub
Click to expand...
 
B

Brad

I appreciate the feedback - your suggestion about changing from the tab name
will be done. Will read the cpeason documentation on error handling.

Jim Thomlinson said:
The biggest part of error handling is avoiding errors in the first place. As
soon as a error occures then everything that follows is potentially
problematic. The biggest thing I see in your code is that you are refering to
sheets by their tab name which will cause a problem if someone decides to
change a sheet name. You should change things (IMO) to reference the sheets
by their code name. In the VBE Explorer window you will see all of your
sheets listed something like this...

Sheet1(Sales)

Sheet1 is the code name and Sales is the name property of that sheet. If you
select that sheet by double clicking on it in the properties window you will
see that the first item is (Name) Sheet1. Change Sheet1 to something more
meaningful like shtSales. Now you can refer to the sheet directly by it's
code name something like this

msgbox shtSales.Range("A1").Value
Which is the same as
msgbox Sheets("Sales").Range("A1").Value
Except that the fist line is bullet proof as it does not rely on anything
that the user can change.

Additionally you will notice that when you type shtSales. you will get the
intellisence drop down of all of the properties and methods of the sheet
which you don't get with Sheets("Sales").

Beyound that you should consider adding an error handler to your code to
catch an error if it occures in your code and allow the code to exit
gracefully without causing damage. Check out this link for more info on error
handlers...

http://www.cpearson.com/excel/ErrorHandling.htm
--
HTH...

Jim Thomlinson


Brad said:
Are there standard checks for errors that the macro's below should have?

An application is being built to be used by others and the following code
works when everything is entered correctly. I have tried to anticipate some
of the problems caused by individual not using valid information. Please
note that data validation is being used extensively on the input fields - to
ensure that valid data is being entered.


Sub Print_pages()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Input" And sht.Visible = True Then
sht.PrintOut
End If
Next
End Sub

Sub change_agent()
Sheets("Agent_information").Visible = True
ActiveWorkbook.Worksheets("Agent_information").Select
End Sub

Sub check_error()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> "Input" Then
sht.Visible = False
End If
Next

If Range("input_info!valid").value <> 0 Then
Sheets("input").[button 17].Visible = False
Beep
End If

If Range("input_info!valid").value = 0 Then
Sheets("input").[button 17].Visible = True
Sheets("input").[button 248].Visible = True
Select Case Range("product").value
Case Is = "Capital_Bonus_2"
Sheets("CB2_Disclosure").Visible = True
Sheets("CB2_Values").Visible = True
Case Is = "Maximum_Solutions_II"
Sheets("Max2_Disclosure").Visible = True
Sheets("Max2_Values").Visible = True
Case Is = "Expanding_Horizon_5"
Sheets("EH5_Disclosure").Visible = True
Sheets("EH5_Values").Visible = True
Case Is = "Expanding_Horizon_7"
Sheets("EH7_Disclosure").Visible = True
Sheets("EH7_Values").Visible = True
Case Is = "GPA_5Yr"
Sheets("GPA5_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "GPA_9Yr"
Sheets("GPA9_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "GPA_Seminole_County"
Sheets("GPASC_Disclosure").Visible = True
Sheets("GPA_Values").Visible = True
Case Is = "MY_Guaranteed_Solution_II"
Sheets("MYGS_Disclosure").Visible = True
Sheets("MYGS_Values").Visible = True
Sheets("input").[button 248].Visible = False
Case Else
MsgBox "Not a valid plan"
End Select
End If
End Sub

Sub monthly_income_benefit_accumulation()
Worksheets("input_info").Range("c50").GoalSeek
goal:=Worksheets("input_info").Range("c52"), _
Changingcell:=Worksheets("input").Range("c13")
Application.ScreenUpdating = True
Worksheets("input").Range("c13") =
Application.WorksheetFunction.RoundUp(Worksheets("input").Range("c13"), 2)
If Worksheets("input").Range("c13") < 0 Then
Worksheets("input").Range("c13") = 0
End If
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Workbook_BeforeSave 8
Unhide sheet, copy and rename new sheets from list, rehide sheet 8
there must be a more simple way of doing this? 2
Can't delete sheets 8
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA for Dependents shortcut menu 0
A simple if <> nn then Msgbox "No Match" 5

Top