VBA TUTORIAL 2016.pdf - VBA TUTORIAL Professor Doug...

This preview shows page 1 - 4 out of 38 pages.

VBA TUTORIAL Professor Doug Blackburn PURPOSE: For students with exposure to programming (which is assumed for incoming MSQF students) to learn the basic syntax of VBA programming. This tutorial is not intended for those with no programming background. PRELIMINARIES: VBA (Visual Basic for Application) is a programming language attached to all Microsoft products. We will be using VBA within the context of Excel. The Developer Tab: To allow for easy movement between VBA and Excel, it is useful to “turn on” the Developer Tab. To do this, in Excel, click on the Microsoft icon at the top left corner of Excel. Select ‘EXCEL OPTIONS’. Under the ‘POPULAR’ tab, check the ‘SHOW DEVELOPER TAB IN THE RIBBON’. After doing this, you will notice the new Developer tab in the ribbon (along with ‘Home’, ‘Insert’, ‘Page Layout’, etc.) Opening a VBA programming window: you can either click ‘VISUAL BASIC’ from the developer tab or simply press ‘ALT F11’ simultaneously. Now, click on the ‘INSERT’ tab and select ‘MODULE’. GETTING STARTED The method I will use to introduce VBA is through many examples. You can write as many routines within one Module so long as each routine is titled differently. Here we go …
WRITING TO CELLS IN EXCEL FROM VBA Our primary goal is to build financial models that allow the user to input preferences in Excel that are then used by VBA with results outputted back into Excel. Therefore, we need to understand how VBA communicates with Excel. The following code demonstrates several ways we can output results from VBA and into a single cell in the Excel spreadsheet. 1. Sub WriteOut() 2. ' You can write comments by using the apostrophe 3. ' Notice that comments are written in the color green 4. Cells(1, 1) = "Welcome MSQF Students!" 5. Cells(2, 1) = "Good Luck!" 6. Range("A3") = "and work hard!" 7. Range("B5").Select 8. ActiveCell.Value = 1 9. ActiveCell.Offset(0, 1).Value = 2 10. ActiveCell.Offset(1, 0) = 3 11. ActiveCell.Offset(1, 1) = 4 12. End Sub (4-5) Cells(i,j) refers to the row i and column j of the active worksheet in the active workbook. (7) Alternatively, we can use the more commonly used letter/number referencing system to indicate the desired cell using the Range function as demonstrated in line (7). I tend to find this a less desirable approach since I often want VBA to change the cell reference. We will see examples of dynamically changing cell references later. (8-11) We can use the OFFSET(i,j) function to instruct VBA to move from the active cell, B5 in this case, i cells in the downward and j cells to the right. For example, line (10) references the active cell B5 and then moves one cell down and zero cell to the right. As a result, the value ‘3’ is place in cell B6.
Notice that in line (9) I use the Value command but not in lines (10) or (11). These are two different ways to do the same thing.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture