Chapter 1 - VBA - University of Alberta

1 MINE 325 Mine Planning and Design VBA Compiled by: Hooman Askari Chapter 1-1 Chapter 1 - VBA Introduction .. 1-2 Visual Basic for Applications with Excel .. 1-2 The VBA Integrated Development Environment (IDE) .. 1-2 programming Components within Excel .. 1-5 Getting Help with VBA .. 1-9 Constructing the Colorful Stats Program .. 1-10 Beginning Programs with VBA .. 1-13 Variables, Data Types, and Constants .. 1-13 Simple Input and Output with VBA .. 1-21 Procedures and Conditions .. 1-23 VBA Procedures .. 1-24 Manipulating Strings with VBA Functions .. 1-28 Procedures and Conditions.

2 1-31 VBA Procedures .. 1-31 Loops and Arrays .. 1-46 Looping with VBA .. 1-46 ARRAYS .. 1-51 programming Formulas into Worksheet Cells .. 1-58 R1C1-Style References .. 1-60 Basic Excel Objects .. 1-60 VBA and Object-Oriented programming .. 1-61 VBA Collection Objects .. 1-61 EXCEL OBJECTS .. 1-63 The Worksheet Object .. 1-63 The Range Object .. 1-64 Working with Objects .. 1-66 Basic File I/O & Debugging .. 1-68 Debugging .. 1-68 File Input and Output (I/O) .. 1-71 More on File Handling .. 1-79 MINE 325 Mine Planning and Design VBA Compiled by: Hooman Askari Chapter 1-2 Chapter 1 - (VBA) Visual Basic for Applications Introduction Visual Basic for Applications (VBA for short) is a programming environment designed to work with Microsoft's Office applications (Word, Excel, Access, and PowerPoint).

3 Components in each application (for example, worksheets or documents) are exposed as objects to the programmer to use and manipulate to a desired end. Almost anything you can do through the normal use of the Office application can also be automated through programming . VBA is a complete programming language, but you can't use it outside the application in which it is integrated. This does not mean VBA can be integrated only with Office programs. Any software vendor that decides to implement VBA can include it with their application.

4 VBA is relatively easy to learn, but to use it in a new application, you must first become familiar with the object model of the application. For example, the Document and Dictionary objects are specific to the Word object model, whereas the Workbook, Worksheet, and Range objects are specific to the Excel object model. As we proceed, you will see that the Excel object model is fairly extensive; however, if you are familiar with Excel, you will find that using these objects is generally straightforward. Visual Basic for Applications with Excel In this section we introduce you to the programming tools available in Excel.

5 These tools include the VBA IDE (Integrated Development Environment), controls and functions available through the main Excel application, and VBA on-line help. After your introduction to the VBA programming environment, I take you through a very short and simple program that calculates some basic statistics from a sample data set. Specifically this section will cover: The VBA IDE and components within programming tools within Excel Using VBA on-line help The VBA Integrated Development Environment (IDE) Before learning how to program in VBA, you have to learn how to use the software required for creating your projects.

6 The VBA development software is included with each component of the Microsoft Office suite of programs, including Excel. Starting the VBA development software places you in the VBA programming environment IDE, which provides you with a number of tools for use in the development of your project. Getting to the IDE from Excel Before you begin creating projects with VBA you must know your way around the IDE. You can access the IDE from Excel in a couple of different ways. In Excel: select Tools, Macro, Visual Basic Editor (as shown in Figure ); or use the keystroke Alt + F11.

7 MINE 325 Mine Planning and Design VBA Compiled by: Hooman Askari Chapter 1-3 Figure - Accessing the VBA IDE from the Tools menu in Excel. Alternatively, select the Visual Basic toolbar from the View/Toolbars menu item in Excel. When the toolbar is displayed, select the Visual Basic Editor icon found in the middle of the toolbar (see Figure ). Figure - Accessing the VBA IDE from the Visual Basic toolbar Components of the IDE After opening the VBA IDE you may find yourself looking at a window similar to what is shown in Figure This figure shows the VBA IDE and some of the tools that can be used to create projects.

8 MINE 325 Mine Planning and Design VBA Compiled by: Hooman Askari Chapter 1-4 Figure - Accessing the Project Explorer and Properties windows. Once the Project Explorer window is displayed, find the project that represents the workbook you opened while in Excel (probably Book1 or Book2). If the components of the workbook you opened in Excel are not displayed, click the + sign next to the Microsoft Excel Objects folder directly underneath the project name. Next, find the object labeled Sheet1, select it with your mouse and then turn your attention to the Properties window.

9 Scroll down the Properties window until you come to the Name property (the one without the parentheses around it). Delete the text entered to the right of the Name property and enter MySheet. Figure illustrates how to find the Name property. Toggle back to Excel by pressing Alt+F11, or select it from the taskbar in Windows. You will note that the name of Sheet1 has now been replaced with MySheet in your Excel workbook, as shown in Figure See how easy it is to alter the properties of a worksheet in Excel using VBA? As VBA developers, however, we will seldom, if ever, alter the properties of a workbook or worksheet at design time.

10 Figure - An edited worksheet name in Excel. Figure - Accessing the Name property of a worksheet. The bulk of the work affecting workbooks and worksheets will occur at run time; however, we will alter properties of ActiveX controls at design time. MINE 325 Mine Planning and Design VBA Compiled by: Hooman Askari Chapter 1-5 HINT Design time refers to project development and the manipulation of object properties using the VBA IDE prior to running any code. Conversely run time will refer to the manipulation of object properties using a program; thus, the properties of the object do not change until the code is executed.