Advanced Excel Macros and Automation PDF

Advanced Excel Module 4: Macros and Automation Learning Objectives Participants Record and edit Run macros should be able macros to: Work with Use Excel Handle error...

Advanced Excel Module 4: Macros and Automation Learning Objectives Participants Record and edit Run macros should be able macros to: Work with Use Excel Handle errors in macro-enabled functions in VBA VBA workbooks code  Module 4 Topics Module 4: Macros and Automation 1. Introduction to macros and VBA (Visual Basic for Applications) 2. Recording and editing macros 3. Running macros and assigning them to buttons 4. Automating tasks with macros 5. Working with macro-enabled workbooks 6. Using Excel functions in VBA code 7. Error handling in VBA 8. Advanced VBA programming techniques What are Excel macros? A macro is a small program or set of actions that you can run repeatedly. Excel macros are used to automate repetitive tasks to save a lot of time and hassle.. What are Excel macros? Businesses would often have lists like this one. These are potential customers they might want to reach out to and market their products. Notice how Columns C to H are just pieces of information extracted from Columns A & B. To streamline the worksheet, you can hide Columns A & B. You can also hide the rest of the columns on the right starting from Column I. Let’s do this using Excel macros! Recording and editing macros How to record Excel macros How to record Excel macros How to record Excel macros How to EDIT Excel macros Automating tasks with macros  Using Developer Tab Let’s record another macro to Unhide the hidden columns. This time, you can record the macro from the Developer tab. The Developer tab gives you access to a lot of useful Microsoft Excel features such as the Visual Basic Editor. It also allows you to quickly insert form controls such as buttons and checkboxes. However, the Developer tab is not visible in the Excel ribbon by default. To add the Developer Tab To start recording the Unhide macro:  Running macros and assigning them to buttons How to run an Excel macro from the View tab How to run an Excel macro (Developer Tab) Run Excel macro from a button For this next example, you will assign macros to buttons which will be located on top of the table. 1. Insert 2 rows above the table headers. Select Row 1 then press Ctrl + Shift + Plus Sign(+) twice. Run Excel macro from a button Run Excel macro from a shortcut key Run Excel macro from a shortcut key Working with macro-enabled workbooks Macro-enabled Workbooks Excel macro-enabled workbook, also known as Excel Macro-Enabled Workbook (.xlsm) is a file extension used by Excel which allows you to save Excel documents with macros and Visual Basic for Applications (VBA) code. Excel macros are created using Visual Basic for Applications (VBA), allowing users to automate tedious tasks in Excel such as creating charts, creating pivot tables and more. They’re also incredibly useful for automating Excel tasks and can significantly save time and effort when working with large data sets or Excel formulas. Saving macro-enabled workbooks Using Excel functions in VBA code Using Excel functions in VBA code Visual Basic for Applications (VBA) has many built-in functions that help you work with spreadsheet data. But “normal” Excel is the undisputed king when it comes to useful functions. Fortunately, you can use worksheet functions to take advantage of that power when you’re writing macros in VBA. VBA functions vs. worksheet functions VBA functions are built into Visual Using worksheet functions saves Basic for Applications, the scripting you time overwriting your own language that you use to create functions in VBA. macros. You can use VBA functions in any To call a worksheet function, you’ll program that supports VBA need to use the following syntax: (including Microsoft Word and Access). Application.WorksheetFunction. [function name] Worksheet functions are specific to Let’s walk through an example of a Excel. They’re the functions that very useful function that isn’t you’re used to using in spreadsheets already—things like present in VBA: the VLOOKUP SUMIF, IF, and VLOOKUP. function.  Using the VLOOKUP function in VBA We’ll use a simple VLOOKUP formula example to see how worksheet functions can be called in VBA. VLOOKUP is a very powerful function that’s great for finding information in big spreadsheets. VLOOKUP searches for a lookup value in a specified dataset. If it finds it, it returns a corresponding value from the same row. Unfortunately, it’s not built into VBA. So we’ll have to call it with a worksheet function.  Using the VLOOKUP function in VBA Open the example workbook to follow along. It contains a list of product numbers and descriptions. We’ll use VBA code to look up descriptions based on product numbers. Here’s the VBA code we’ll use: Sub findProduct() Dim prodNum As Integer, prodDesc As String prodNum = Range("F2").Value prodDesc = Application.WorksheetFunction.VLookup(prodNum, Range("A1:B51"), 2, FALSE) MsgBox prodDesc End Sub The first two lines of the script are simple; they declare prodNum as an integer variable and prodDesc as a String variable, then assign the value in cell F2 to prodNum. Using the VLOOKUP function in VBA Using the VLOOKUP function in VBA Then you’ll need to include the standard arguments. In the VLOOKUP function, those are: – lookup_number – table_array – col_index_num – and range_lookup You enter them similarly to how you would in Excel (if you’ve forgotten how to do that, read my guide here). But there are a few differences ⚠️ In our case, lookup_number is the variable prodNum, which is similar to selecting a cell in Excel. The table_array, however, needs to be presented in a format that VBA can handle. Here we’ve used Range(“A1:B51”), which selects the cells in A1:B51. Using the VLOOKUP function in VBA Error handling in VBA Error Handling with Err.Number The true power of Err.Number lies in the Sub TestWS() MsgBox DoesWSExist("test") ability to detect if an error occurred End Sub (Err.Number 0). In the example below, we’ve created a function that Function DoesWSExist(wsName As String) As Boolean will test if a sheet exists by using Dim ws As Worksheet Err.Number. On Error Resume Next Note: We’ve added a On Error GoTo -1 Set ws = Sheets(wsName) to the end which resets Err.Number to 0 'If Error WS Does not exist (see two sections down). If Err.Number 0 Then DoesWSExist = False Else With On Error Resume Next and DoesWSExist = True Err.Number, you can replicate the “Try” End If & “Catch” functionality of other On Error GoTo -1 programming languages. End Function Advanced VBA programming techniques Error Handling with Err.Number https://www.excel- easy.com/vba/ userform.html  References https://spreadsheeto.com/make-a-macro/#introduction https://spreadsheeto.com/make-a-macro/ https://spreadsheeto.com/vba/#intro https://www.excel-easy.com/vba/msgbox.html https://www.automateexcel.com/vba/error-handling/#vba- error-handling Thank you Maria Crystal E. Orozco, DIT Faculty, College of Computer Studies University of Perpetual Help System