| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro's checking for errors
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").Sel ect 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's checking for errors
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" wrote: 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").Sel ect 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's checking for errors
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" wrote: 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" wrote: 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").Sel ect 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I remove multiple 'errors' in Excel error checking, at the same time??? | Excel Discussion (Misc queries) | |||
| Hide Macro's in Toolbar / Macro's list | Excel Discussion (Misc queries) | |||
| Checking excel for errors/inconsistencies | Excel Worksheet Functions | |||
| checking errors in the formulas | Excel Programming | |||
| checking for errors in combobox's | Excel Programming | |||
Linear Mode
