Visual Basic for Applications in Microsoft Excel (1)

1. Visual Basic for Applications in Microsoft Excel (1) Week 10

2. Week 10 Protecting Code Protecting Worksheets Error Handling Charts

3. Last Week Using form tools Changing the QAT Revision Exercises

4. Data Input Form - Example • Run the Add Customer form from the button on the Data Sheet • Features: • Auto number Customer ID • Accommodates other ranges in the worksheet • Design Issues: • Uses Named Range to place details on the sheet • Code attached to Initialize event generates an autonumber • The text box’s Enabled property is set to False to disallow editing • Need to update the named range after input • some data below the named range which we don’t want to overwrite

5. Protecting VBA projects • Locking the project for viewing means that the macros will still run but that users will need a password to access the code in the editor. • Close the file and reopen it. • To access the code of a protected project, from the Project Window, double click on the appropriate project and enter the password at the prompt.

6. Protecting sheets • You can password protect sheets using the Review tab in excel where you can specify the limits of the protection • You can exclude selected cells from the protection by highlighting and using the protect cells tab of the format cells dialog

7. Protecting Sheets • A line of code to protect the current sheet might look like this. ActiveSheet.Protect "password" True, True, True • Where ActiveSheet is the object and Protect the method. • Protect has many arguments. • The first is the password string, followed by a series of True/False arguments resembling the tickbox list found in Excel. • As with the Excel interface, by default most options are set to True (protected) whilst a few are false (unprotected)

8. User input - Customising the Protect sheets macro • Instead of always protecting the active sheet, you might want the user to choose which sheet to unprotect Sub protect_choose() Dim mySheet As Worksheet Dim sheetName As String sheetName = InputBox("Please type Sheet name", "non-robust sheet selector", "sheet1") Set mySheet = Worksheets(sheetName) mySheet.protect “password" End Sub

9. Protecting all the sheets using for each Adapt previous macro to protect each sheet in turn Sub ProtectSheets() Dim mySheet As Worksheet mySheet.Protect “password" End Sub

10. Error Handling • If you try to run a sub to add a worksheet named “Results” where a worksheet named “Results” already exisits, • Sub will stop running • A message box containing an error code will appear Error handling seeks to write code that deals with the issue and moves on to the next line

11. Error handling • Use On Error Resume Next to allow the macro to continue when an error is encountered. • Use Err.Number to find out if there is an error or not. If the number property of the Err object is 0, then no error occurred. For example: If err.number <> 0 Then -- do something to cope with eventuality -- End if

12. Testing to see if worksheet exists • Function SheetExists • This function uses the Err object to see if an error occurs when a sheet is referenced, i.e. it doesn’t exist. If an error occurs, then the Resume command clears the error and allows the program to continue. • Can be used within another sub to check for a sheet name before trying to use the name for another sheet

13. Sub make_report() Dim mysheet As WorksheetDim myBase As StringDim mySuffix As IntegerSet mysheet = Worksheets.AddmyBase = "Report"mySuffix = 1 On Error Resume Next mysheet.Name = myBase & mySuffix Do Until Err.Number = 0Err.Number = 0mySuffix = mySuffix + 1mysheet.Name = myBase & mySuffixLoop End Sub

14. Charts Record a macro to create a chart (start with a simple column chart) in a new sheet for the exam results on the exams sheet Good example of instance where recorded code and reference to the object map can help

15. Charts Sub MakeChart() Selection.CurrentRegion.Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Exams").Range("A1:B10")_ , PlotBy:= xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Exams" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Exam Marks" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveChart.HasLegend = False End Sub

16. Charts Adapt your code to produce a pie chart Rename the chart Label the axis Allow the user to select the range of the chart data

17. Some final points on using the VBA editor • Module level variables • Use module level variables only when two or more subs need access to the same variable; otherwise it’s better practice to use variables local to the procedure they are in. • To declare a module level variable, you declare it in the Declarations section (where the Option statements are), before any subs. • You can use Dim as previously, but it is recommended you use the keywords Private or Public to determine the scope of the variable. Private variables are accessible only to the subs in the same module, Public variables are accessible to all the subs in the workbook.

18. Boats Example An example of a longer application Uses Module level variables Has three main Subs which call on combinations or smaller subs Uses Workbook_Open event handler to reset workbook

19. Good Luck in your future VBA endeavors