VBA Programming for Developing Business Applications in Microsoft Excel
Lead Author(s): Frederick Kaefer
Student Price: Contact us to learn more
Includes illustrations and videos to explain how the VBA programming language can be used to create applications building on the features of Microsoft Excel.
Chapter 1 Application Development in Microsoft Excel using Visual Basic for Applications (VBA)
- Learn about the benefits of creating applications with VBA
- Be introduced to Microsoft Excel features that are used for analyzing data
- See how VBA can be used to create an application that supports decision making using stored data.
- Learn about the Visual Basic Editor (VBE)
- Learn about Excel Objects, and properties, methods and events associated with them.
- Become familiar with the basic elements of VBA code.
Benefits of Learning to Create Applications with VBA
Visual Basic for Applications (VBA) can be used to create computer-based applications that utilize and build on the features of Microsoft Excel. Benefits of learning to create applications with VBA are numerous. One clear benefit is gaining a deeper understanding of the inner workings of Microsoft Excel. Becoming an expert in Microsoft Excel is highly valued in today’s workplace. A second benefit is learning how to code. Developing coding skills has been recognized as being important for opening many opportunities, both entrepreneurial and traditional in nature. Since Microsoft Excel is widely used in the business world, developing coding skills in VBA is particularly useful. It has been found that half of all programming openings are in industries outside of technology and that the highest demand is for programming languages with broad applicability1.
There are many advantages of computer-based applications that are developed in VBA. First, they can be used to automate processing so that it is faster and easier for users to complete their tasks. A second benefit of computer-based applications that are developed with VBA is that If they are developed and tested properly, both data entry and computational errors will be greatly reduced. Applications developed with VBA can also be used to work with external data to support decision making.
Microsoft Excel Features used for Analyzing Data
Experienced users of Microsoft Excel are familiar with numerous features within Excel that are used for working with and analyzing data, including Pivot Tables, Data Tables and the creation of Scenarios using the Scenario Manager. Figure 1.2 illustrates a UserForm that is used when creating a Pivot Table in Excel. In addition to these features that are built into Excel, Add-In programs can also be used. One example is the Data Analysis Toolpack, which enables users to create histograms, conduct regression analysis and perform hypothesis testing.
Each of these applications involve user interfaces that facilitate the specification by the user of actions they would like to have completed. These interfaces often contain controls including entries presented in list boxes for the user to select, and check boxes and option buttons that the user can utilize to indicate specific conditions and choices. Once indicated, the applications perform processes that involve various calculations and manipulation of various data elements. The data used is typically stored data, either within an Excel workbook or from an external data source such as a file or database. In the successful use of one of these applications, the user is presented with results, typically in the form of a table or chart, which is used to support decision making.
Visual Basic for Applications (VBA)
Visual Basic for Applications (VBA) is a computer programming language that comes with Microsoft Office Software. VBA can be utilized to automate many actions within Microsoft Office Programs, but also to develop applications which are user-friendly and can add value in business settings. These applications can guide users through the steps that they need to perform, reducing data entry errors and performing complex tasks such as retrieving and updating data from shared databases. The following video demonstrates an example application that enables users to utilize stored data to support decisions through the manipulation and analysis of that data. By the end of this textbook, you will be able to create applications of your choosing that use features like those demonstrated here.
The Visual Basic Editor (VBE)
To write code in VBA within Microsoft Excel files, a developer needs to utilize the Visual Basic Editor (VBE). The VBE is an Interactive Development Environment (IDE) which contains facilities for writing and editing code as well as testing and debugging code. The VBE also has resources for working with the objects used to create UserForms. UserFoms are customized interfaces that can contain a number of controls for the user to make selections or indicate which actions they would like to occur. In Microsoft Excel, the VBE can be accessed through the developer tab on the ribbon (Note that the developer tab may have to be added to the ribbon if it has not configured to be there by default), or by using the shortcut key combination ALT + F11 on windows – based systems. The following video demonstrates how to activate the VBE and illustrates the main features of the VBE.
A (n) ___ is a customized interface that can contain a number of controls to make selections or indicate actions to occur.
Integrated Development Environment (IDE)
Before developing code in VBA, it is necessary to understand that VBA is an object-oriented programming language. When specifically using VBA to develop applications in Excel, VBA works with Excel Objects. Users of Excel work with a variety of objects including ranges, charts, worksheets and workbooks. Each of these objects have distinct attributes and actions that can be done to or with them. The attributes of an object are referred to as that object’s properties. The actions that can be done to or with an object are referred to as that object’s methods. The object browser in the VBE can be used to see the list of properties and methods that any object type has. Within the VBE, the object browser can be accessed by clicking on the view tab on the main menu and selecting object browser or by clicking on the object browser icon on the standard toolbar.
In addition to
properties and methods, many objects in Excel have events that are associated with them. Events are things that occur or happen to an
object. For example, Figure 1.3 illustrates
a portion of the object browser and the members of the worksheet object. The first member in the list of worksheet
objects is the Activate event. This
event occurs when a worksheet is made active, such as when clicking on the
worksheet tab within Excel. The
lightning bolt icon is used to indicate that this first member is an event. The
second member is the Activate method, which causes a given worksheet to become
the active worksheet. The next several
members are properties, specifically Application, AutoFilter and
AutoFilterMode. In the following
chapters we will work with a number of Excel objects and utilize a variety of
their properties, methods and events.
Methods of an object are:
attributes of that object
things that occur or happen to that object
actions that can be done to or with that object
all of the above
VBA Code Basics
VBA code may be developed to work with any of the objects in Excel, and throughout our coverage we will see examples of code that is associated with workbooks, worksheets, and UserForms. In addition to being specifically associated with particular objects, VBA code is often written within modules. Wherever the code is developed, VBA code is organized within subroutines so that code can be referenced and executed. The following program illustrates a number of features related to the structure and format of a VBA program.
Several basic elements of VBA code are comments, reserved words, variables, and lines of code, which are illustrated in Figure 1.4. Comments always begin with a single quote “ ‘ “, and can be on their own line or at the end of a line of code. Comments are shown in green font and are not evaluated by the compiler when the code is executed. Reserved words are shown in blue font and are often expected to follow specific conventions. For example, in the code above, the word “Sub” is a reserved word. It is required that lines of code are placed within a sub, so the sub reserved word is expected to be encountered prior to any lines of code by the compiler when the code is executed. If a line of code were encountered before the sub keyword was encountered, an error would result. Variables are used to reference locations in memory where intermediate results are often stored and manipulated. Variable names are shown in black font, and it is deemed a best practice for developers to formally declare variables used in their code by specifying the name and data type of the variable. We will discuss variable data types in more depth in the following chapters. Lines of code are also shown in black font, unless there is a syntax error, in which case the syntax error will be identified by rendering the code in a red font. There will be more coverage on error types and how to resolve them in the following chapters as well.
Which of the following always begin with a single quote in VBA code?
Lines of code
Which of the following are shown in blue font in VBA code?
none of the above
Which of the following are shown in green font in VBA code?
Lines of code
Chapter One Endnotes
1. Lydia Dishman. (2016, June 14).Why Coding Is Still The Most Important Job Skill Of The Future.Retrieved May 3, 2018, from https://www.fastcompany.com/3060883/why-coding-is-the-job-skill-of-the-future-for-everyone
Screenshots taken by author using Microsoft Excel. Microsoft product screenshots used with permission from Microsoft Corporation.
Author Biographical Information
Frederick Kaefer is an Associate Professor of Information Systems in the Loyola University Chicago Quinlan School of Business. After completing a Bachelor’s degree in Mathematics and Computer Science, he worked as a mainframe programmer for several years before earning an MBA with concentrations in Finance and Information Systems and a PhD in Management Information Systems. Professor Kaefer has taught computer programming to business students for over 25 years. In addition to teaching courses involving learning programming and developing applications using Visual Basic for Applications, Professor Kaefer is also developing a course using the Python programming language.
In addition to teaching computer programming related courses, Professor Kaefer has taught a wide variety of courses including Business Statistics, Business Information Systems, Network Management, Systems Analysis and Design, and Telecommunications for Managers. Professor Kaefer has also been honored by the Quinlan School of Business as the Outstanding Graduate School of Business Faculty Member of the Year.