Lec02_IE212_IntroVBA_W16_stds-1.ppt - IE 212 Computational Methods for Industrial Engineering Lecture Notes#2 The Visual Basic Editor The Visual Basic

Marc Christman Fall 2016 Lecture Notes #2: The Visual Basic Editor The Visual Basic for Applications Programming Language IE 212: Computational Methods for Industrial Engineering

Outline  In this module, the following topics will be covered: – The basics of the Visual Basic Editor (VBE) environment  Project Explorer Window  Properties Window  Code Window – Introduction to the Excel Visual Basic for Applications (VBA) programming language  The Excel object model  The difference between a property, a method, and an event  Fundamental skills for reading, understanding, and writing basic Excel VBA code – Basic user interface development  Assigning sub procedures to shapes (i.e., buttons) 2

3 The Visual Basic Editor (VBE)

The Visual Basic Editor  The Visual Basic Editor (VBE) is the environment in which you work with Excel VBA code  Excel and the VBE have separate windows – You can toggle between as needed by pressing Alt+F11 4 Excel VBE

The Visual Basic Editor (cont.)  Access the VBE through the Developer Ribbon (Excel 2013) 1) Open Excel 2010 and click on the green tab labeled “File” 2) Click on “Options” (the window “Excel Options” will appear) 3) Click on “Customize Ribbon” 4) Make sure the entry in the combo box labeled “Customize the Ribbon” reads “Main Tabs” 5) Select the option “Developer” 6) Click OK 5

The Visual Basic Editor (cont.)  There are three main windows in the VBE – Project Explorer Window – Properties Window – Code Window  Two additional windows in the VBE are mainly used for debugging – Immediate Window – Watch Window  VBE Toolbars – Standard toolbar 6

The Visual Basic Editor (cont.)  The VBE’s windows 7 Project Explorer Properties Window Code Window Watch Window Immediate Window

Project Explorer  Lists all projects in any open workbook  Each workbook has a project, and each project can have several parts – Objects (e.g., workbook and worksheets) – Modules – Forms  In the VBE, you can use Insert > Module to add a new module to the current project – Can also use icon from standard toolbar 8

Properties Window  Contains detailed information about any selected part of a project in the Project Explorer  Some basic naming and formatting properties can be modified for worksheets and workbooks  Properties are very important for user forms – Formatting – Position – Picture – Scrolling – Behavior 9

Code Window  Displays the VBA code for the highlighted part of a project in the Project Explorer  When macros are recorded , VBA code is simultaneously created and can be edited in the VBE  You will write your own code in this window, once you are more familiar with Excel VBA 10

Immediate and Watch Windows  Both of these windows are used for debugging – The Immediate Window allows you to enter code that is executed immediately  As long as the syntax is correct – The Watch Window displays values of inserted variables  Use the View menu option to view or hide any window 11