BUS-K 515 Application Development in VBA
- 7 weeks
- 1.5 credits
- Prerequisite(s): MBA Core
Most of the Excel files you have created in previous courses have probably been for your own use. In contrast, Excel developers in many companies create applications for others to use. You see these types of applications all the time, whenever you use any Windows program. There are toolbars, menus, and dialog boxes with a variety of controls (buttons, checkboxes, list boxes, and so on) that make an application user-friendly and provide a method for users to enter their desired inputs. The goal of this course is to introduce methods for creating user-friendly applications in Excel by taking advantage of the powerful macro language for Microsoft Office, Visual Basic for Applications (VBA). A relatively simple, but by no means trivial, example is the following. Suppose you have an Excel database of sales for your company's sales reps. Your boss wants an application that will enable her to do the following with a few button clicks:
- it will allow her to add a new record (a sale by some sales rep) by entering the appropriate information in a dialog box,
- it will allow her to delete a record by specifying the information to delete in a dialog box, and
- it will provide immediate summary information (total sales, say) on any sales rep or group of sales reps that she specifies in a dialog box.
This can be accomplished with VBA.
Application development in VBA can be divided into two parts: the design part and the logic part. The design part is the fun part, where you get to design dialog boxes and other Excel elements. From a technical point of view, this is easy-no programming is involved-although there are some design principles you need to be aware of. The logic part is the hard part, where you have to write some VBA code. For example, you typically have to write some code to respond correctly when a user clicks on an OK button. Because there is no computer programming prerequisite for this course and most of you have probably done little, if any, computer programming, we will keep the programming part as basic as possible. However, you will see that with only a small amount of programming, you will still be able to create useful and fairly powerful applications. The things you will learn in this course can be categorized in two ways: by programming techniques and by types of applications. You need to learn some programming essentials before you can do anything useful, but you also do not want to be programming in a vacuum, that is, you want to be creating applications that are similar to those you might be doing in a future job. In the limited time available, we will try to cover both of these:
- Programming skills and interesting applications.
Programming skills we will cover include:
- Learning the essentials of programming in any language: data types, looping, conditional statements (if-then-else), arrays, and so on
- Using the macro recorder and the VBA Help system to learn as you go Learning Excel's object model, which allows you to manipulate Excel behind the scenes with VBA code
- Creating UserForms (dialog boxes), complete with controls and event-handling code that responds when a user does something (like clicking on an OK button)
Typical applications we might cover include:
- Developing simple utilities that accomplish routine tasks (like putting a blue border around a given range, or
- changing all letters in a given range to lower case or upper case) with the click of a button
- Summarizing data from Excel lists (e.g., given monthly data on all sales reps, color each sales figure red if it exceeds a certain value)
- Consolidating all data in multiple sheets into a single sheet
- Providing a front-end user inte1face to a spreadsheet model that allows a user to specify (in a dialog box) inputs to the model
- Providing a back-end user-friendly report that summarizes the key results from a spreadsheet model