Excel VBA Programming - The Complete Guide
- 18 hours on-demand video
- 3 articles
- 1 downloadable resource
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- Automate tasks and procedures in Excel using Visual Basic for Applications (VBA)
- Utilize the objects in the Excel Object Model to emulate user actions in Excel
- React dynamically to user events like entering a cell value or opening a workbook
- Feel comfortable with the fundamentals of computer programming
- A modern version of Microsoft Excel for Windows (2016 is strongly recommended)
- Intermediate knowledge of popular Excel features (functions, PivotTables, charts, etc.)
- Basic knowledge of data types (numbers, strings, Booleans, dates)
- A desire to learn!
Welcome to Excel VBA Programming - The Complete Guide, Udemy's most comprehensive VBA course!
Visual Basic for Applications (VBA) is a powerful language built on top of popular Microsoft Office applications like Excel, Access, and Outlook. It allows developers to write procedures called macros that perform automated actions. Anything that you can do in Excel, you can automate with VBA!
Over the course of more than 18 hours of content, we'll cover VBA from the ground up, beginning with the fundamentals and proceeding to advanced topics including:
- The Excel Object Model
- The Visual Basic Editor
- Objects and Methods
- Variables and Data Types
- Writing Your Own Procedures
- The Workbooks and Workbook Objects
- The Worksheets and Worksheet Objects
- Range References
- Range Actions
- Conditional Logic
- Configuring Excel Functionality
- Custom Functions
- Even Procedures
- User Forms
In addition to the video content, the course is packed with dozens of quizzes, challenges, and assignments to test your knowledge of your material as you progress.
No programming experience is required -- complete beginners are more than welcome! VBA is a great language to start with because you can master the fundamentals of programming in a familiar work environment.
No extra software is necessary - VBA is bundled with all modern versions of Excel.
Excel is the world's most popular spreadsheet software and is available on over 750 million computers worldwide. Whether you use it for professional or personal reasons, VBA can help you remove the redundancy in your workflow and accelerate your productivity drastically!
Thanks for checking out the course!
- Spreadsheet users who want to automate their daily workflow
- Business analysts who want to remove redundancy from their common tasks
- Excel users who are curious about exploring programming in a familiar work environment
In this lesson, we introduce Visual Basic for Applications (VBA), a powerful language for automating operations in Microsoft Office applications. We discuss:
- the benefits offered by the technology
- the tools and features available in VBA
- a brief history of the language including its precursors Basic and Visual Basic
- the prerequisites for the course
The Developer Tab is a secret Tab on the Ribbon that includes several options for working with VBA. Users can access the Visual Basic Editor, enable the Macro Recorder, and display a list of public macros. In this lesson, we expose the Developer Tab and introduce some of its available commands.
The Macro Recorder can record with absolute or relative references.
Absolute references (the default recording option) target specific cells (i.e. B1) -- the recorder is more concerned with the final destination.
Relative references record changes relative to a cell -- the recorder is more concerned with the steps of travel from a starting position.
In this lesson, we record one macro with each type of reference and compare the generated code in the Visual Basic Editor.
In this lesson, we continue our exploration of absolute vs relative references by recording a macro that deletes a row from the worksheet.
Absolute references explicitly target a specific row each time, which defeats the purpose of the macro. Relative references allow us to fix this bug.
In this lesson, we explore the components of the Visual Basic Editor, including the
- Project Explorer, which lists all open workbooks and worksheets as well as all modules (containers for code).
- Properties Window, which can be used to get or set properties on objects with Excel. An object represents an entity in the program (i.e. a Workbook, Worksheet, module, etc.)
- Immediate Window, a playground for executing single lines of VBA code
- Code Editor, where the developer writes the actual macro code
VBA is an object-oriented programming language that views an application as a series of interactions between data entities called objects. Objects have properties, which describe its characteristics, and methods, which are actions that can be taken upon them.
In this lesson, we explore OOP in the context of 3 real-life objects: a book, an apple, and a basketball.
Some objects in VBA are collection objects. Their purpose is to serve as a box or container for related objects. For example, the Worksheets collection object stores a Worksheet object for each worksheet in a Workbook.
In this lesson, we apply these single object / collection object principles to a real-life example of a bicycle shop.
It's easy to see object properties as simple, static data values -- integers, floating point numbers, or strings. However, object properties can also store other objects, complete with their own distinct properties and methods.
In this lesson, we apply this principle to both a real-life example of a Book and an Author, as well as several Excel-based examples.
In this lesson, we use the Visual Basic Editor's Immediate Window to explore the hierarchy of the Excel Object Model. We begin at the top with the Application object, which houses all of the Workbooks currently open in Excel, and navigate all the way down to the Range object, which represents one or more cells. The syntax for getting and setting property values is also explored. Finally, we introduce the concept of the ActiveWorkbook and the ActiveSheet.
Some objects in the Excel Object Model have a default property. If the object is referenced without any explicit property or method, VBA will assume the developer is targeting the default property.
In this lesson, we'll explore the default properties on the Range and Application objects and discuss the benefits and disadvantages of explicitly writing them out.
In this lesson, we explore the Name property on both a Worksheet and Workbook object. The Name property is only readable on a Workbook but is readable and writeable on a Worksheet. The key takeaway is that object design differs; just because two objects share a property or method does not mean it will function identically.
The Visual Basic Editor includes a complex set of options that cover everything from syntactical help with the code to visual aesthetics. In this lesson, we'll explore all of the available options and configure a setup that is ideal for new and experienced VBA developers alike.
A procedure is a grouped set of instructions / code that accomplishes some kind of goal. We'll also talk about some of the common syntactical errors made when writing out procedures. In this lesson, we'll dive into the syntax to declare a procedure within the Visual Basic Editor.
The Immediate Window is a console or playground within the Visual Basic Editor that allows the developer to test out snippets of VBA code. The IW can be used to get or set property values, invoke object methods and more. In this lesson, we'll practice writing sample code in the Immediate Window and also introduce the Debug.Print method, which outputs to the IW from the body of a procedure.
The MsgBox is a simple dialog box that prompts a user action or confirmation. In this lesson, we'll explore the syntax for the method as well as begin our discussion of inputs, parameters, and arguments. A parameter is the name given to an expected method input. An argument is the actual value the user provides for the parameter.
Methods are actions that can be performed on objects. They are commands that we give to an object. We frequently use the words call or invoke to signify the "execution" of a method.
In this lesson, we'll explore three methods:
- The Workbooks.Add method to create a new Workbook
- The Workbook.Save method to save a single Workbook.
- The Range.Clear method to clear a range of values, styling and formats.
All 3 of these methods do not require inputs, also known as arguments, to properly function.
In this lesson, we use the official Microsoft Developer Network documentation to look up the Worksheet object and the Protect method. We discuss the idea of method parameters, which are names we give to expected inputs. We also explore 2 syntactical options for passing parameters to methods, one using sequential arguments and the other using explicitly named parameters.
Some object methods can accept multiple arguments. In this lesson, we continue exploring the Workbooks.Open method and two of its parameters -- FileName and ReadOnly. Arguments can be fed in sequentially or with named parameters -- both options and their relative advantages / disadvantages are discussed.
The Object Browser is a lightweight documentation tool built into the Visual Basic Editor. It can be accessed with the keyboard shortcut F2 and takes the place of the Code Editor. The Object Browser allows the developer to search for objects, properties, and methods across the entire Excel Object Model hierarchy.
In this lesson, we play around with the Object Browser and see how we can use it access online documentation on the MSDN as well.
VBA allows multiple variables to be declared on a single line. The user has to be careful, however. If the wrong syntax is used, some variables can be assigned an automatic Variant data type. Variant is a chameleon data type that is memory-intensive and should only be used when necessary. In this lesson, we discuss how to avoid that pitfall and properly declare our variables.
VBA has 3 options for whole numbers: Byte, Integer, and Long. Each data type supports a different range of numbers; Byte only supports any number from 0 to 256 while Long can store numbers in the billions. In this lesson, we practice declaring different variables for each of the 3 data types.
The Boolean data type can only store one of two data types: True or False. It is used to model either-or relationships -- for example, something is either on or off, enabled or disabled, present or not present. It is named after the English mathematician George Boole. In this lesson, we practice declaring a procedure with Boolean variables and discuss the contexts in which they can be used.
Date is a versatile data type that can represent a date, a time, or a datetime. It also accepts a variety of formats and does its best to convert them to a standardized output. In this lesson, we practice declaring several dates and times in our procedure and outputting them to the spreadsheet.
Variant is a chameleon data type that can morph itself into any required data type. This can actually be a dangerous type if used improperly because it allows the developer to be lazy in their design of a program. In this lesson, we use the TypeName method from the VBA object to track the data type of a Variant variable as we assign different values to it.
A variable supports more than just primitive data types like numbers and strings. It can also be assigned to any object in the Excel Object Model -- a Worksheet, a Workbook, etc. The design allows for code to be tighter and leaner. In this lesson, we write a procedure that uses variables to store references to a specific workbook, worksheet and range.
Variables have scope, which describes the boundary or context in which the variable can be used. In this lesson, we explore the 3 types of variable scope:
- local / procedure / macro scope - variables declared in a procedure are limited to that procedure
- module scope - variables declared in a module are available to all procedures within that module
- application / global scope - variables declared with the Public keyword are available in all procedures across all modules.
The Exit Sub keywords terminate a procedure prematurely, before it reaches its last line of code. It's an effective technique to combine with conditional logic --- proceed if all the pieces fall into place, exit otherwise. In this lesson, we write a basic procedure with Exit Sub.
In this lesson, we dive into predefined constants or enumerations. These are constants built into the VBA language itself that evaluate to numbers. They are used internally by VBA whenever several options are needed that cannot be reduced to simple data values.
In this section, we'll explore the objects in the Excel object hierarchy in greater depth. We begin with the Application object at the top of the hierarchy. Its properties and methods deal with top-level, global Excel settings. They include:
- Name for the application name
- Path for the filepath location of Excel
- Version for the version number of Excel
- UserName for the registered Excel user
- Quit to exit the application
The Application.DisplayAlerts property is used to enable or disable user alerts in Excel. An alert is a dialog box that warns the user of an impending operation. It's a helpful feature but can be distracting when the user is executing a macro. In this lesson, we'll practice disabling and enabling the feature by assigning Boolean values to the DisplayAlerts property while closing a workbook.
In this lesson, we offer a review of the Workbooks.Open method and introduce the Workbook.Path property. The former is used to open a single workbook while the latter is used to identify the direction that a workbook exists in. It's often helpful to provide the full path to a workbook when opening it to avoid errors with VBA.
The Worksheet.Visible property is used to hide and unhide a worksheet. It accepts either a Boolean argument (True or False) or an XlSheetVisibility enumeration. One cool VBA feature is the ability to use the xlSheetVeryHidden option to hide a worksheet but prohibit the user from unhiding it in the Excel interface.
The Worksheet.Copy method copies the contents of a worksheet to a new worksheet. When invoked with no arguments, it will create the copy in a brand new workbook. With either a Before or After argument, the copy will be placed in the current workbook right before / after the specified worksheet.
In this section of the course, we take a deep dive into the Range, the most popular object in the Excel Object Model. We begin with the Select method, which highlights the range's boundaries on the spreadsheet.
Sometimes, the way data is presented in the Excel interface is not the same way it is stored internally. In this lesson, we explore the Value and Text properties on the Range object. The former returns the actual data value while the latter returns its presentation in the cell. The two can return different results for data types like dates and currencies.
R1C1 notation is a different way of thinking about cells in Excel. Instead of using a letter for the column and a row for the number, R1C1 relies on numbers for both. In this lesson, we enable R1C1 notation in our Excel interface and practice creating absolute and relative references to cell.
In addition to Value and Text, the Range object also includes Formula and FormulaR1C1 properties for writing formulas to cells with either A1 or R1C1 notation. In this lesson, we introduce a basic sum example to show how R1C1 notation can be helpful when duplicating formulas across multiple columns.
The Range.Offset property shifts the currently selected range to a new one. It accepts two arguments -- the number of rows and the number of columns to move. Positive arguments represent downward movements for rows and rightward movements for columns; negative arguments represent upward movements for rows and leftward movements for columns. In this lesson, we practice traversing from one Range to another using the Offset property,
The Range.Resize changes the size of a range based on the location of a single cell. It can be used to dynamically increase the size of a cell range in the vertical direction, horizontal direction, or both. In this lesson, we practice resizing some sample ranges with the property,
The Cells property allows VBA developers to target a Range with R1C1 notation. With no arguments, it can be also be used to select all cells on a spreadsheet. In this lesson, we practice selecting single-cell Range objects with the Cells property before applying the Resize property to expand the selection to multi-cell Ranges.
The Range.CurrentRegion property looks for the boundaries surrounding the range passed in as the argument. It returns a new Range representing the complete region surrounding the cell. In this lesson, we practice using the CurrentRegion property to target four colored Ranges on the spreadsheet.
The End key allows the user to navigate downward, upward, leftward or rightward with a press of an arrow key. Excel moves either (1) to the cell at the boundary of the current range or (2) the boundary of the spreadsheet. In this lesson, we write values to the four corners of the spreadsheet using the Range.End property and discuss the predefined constants (enumerations) passed in as arguments.
The Range.Row and Range.Column properties return numbers that represent the numeric position of the cell's row and column relative to the spreadsheet. If a multi-cell Range is used, VBA uses the top-left cell as its reference point. In this lesson, we practice using the two properties on a variety of Range objects.
In this quick lesson, we dive into 3 total methods on a Range object:
- ClearContents, which removes the value from a Range.
- ClearFormats, which removes the formatting of a cell (font, border, background, etc)
- Clear, which removes both the content and formats of a cell.