Excel VBA Programming - The Complete Guide | Discount Coupon for Udemy Course
Automate your Microsoft Excel workflow, accelerate your productivity, and master programming with VBA! Beginners welcome | Discount Coupon for Udemy Course
- 18 hours hours of on-demand video
- 4 article
- Full lifetime access
- Access on mobile and TV
- Certificate of completion
- 1 additional resources
- 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
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 ModelThe Visual Basic EditorObjects and MethodsVariables and Data TypesWriting Your Own ProceduresThe Workbooks and Workbook ObjectsThe Worksheets and Worksheet ObjectsRange ReferencesRange ActionsConditional LogicIterationAlertsConfiguring Excel FunctionalityCustom FunctionsArraysDebuggingEven ProceduresUser 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!Who this course is for:Spreadsheet users who want to automate their daily workflowBusiness analysts who want to remove redundancy from their common tasksExcel users who are curious about exploring programming in a familiar work environment
Sections are minimized for better readability, click the section title to view the course content
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
- About Me00:56
Get to know a little about your instructor.
- Download Course Materials00:42
This lesson contains all the Excel worksheets used during the recording of the course. Although it's a good idea to reference the material, the best strategy is to code alongside the videos to practice these concepts yourself.
- Enable the Developer Tab07:06
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.
- Excel File Types04:00
There are dozens of file types available in Excel. In order to work with macros, we need to save our files in a special format called .xlsm. In this lesson, we introduce an option for defaulting to a .xlsm format upon saving.
- Macro Security08:43
Macros have been used to package malicious code in the past. In this lesson, we explore the security options built into Excel that prevent macro code from executing automatically. We also add a directory to a Trusted Locations list of folders to mark the files in it as safe to run.
- The Macro Recorder15:12
The Macro Recorder is a powerful tool that converts your interface actions (i.e. clicks, navigations, aesthetic changes) into valid VBA code. In this lesson, we discuss the merits (and drawbacks) of the Macro Recorder and explore some of the code that it generates.
- Record Macro to Change Worksheet Orientation2 questionsExplore the benefits and drawbacks of the Macro Recorder by recording a procedure that changes the worksheet's orientation.
- Absolute vs. Relative References I08:12
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.
- Absolute vs. Relative References II08:14
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.
- The Visual Basic Editor09:01
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 Basics7 questions
Test your knowledge of the basics of VBA including file types, macro security, the macro recorder, absolute and relative references, and more!
- Object-Oriented Programming in Real Life11:29
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.
- Real Life Objects3 questionsApply the principles of object-oriented programming to the real life objects around you.
- Collection Objects in Real Life05:50
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.
- Objects As Properties09:08
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.
- The Excel Object Model13:34
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.
- Access Object from Collection by Name03:57
An object inside a collection can be accessed by either its
- index position (its numerical place in line)
- its name
In this lesson, we explore this with the context of Worksheet objects inside the Worksheets collection.
- Default Properties03:38
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.
- The Name Property on Workbook and Worksheet Objects04:03
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.
- Object-Oriented Programming6 questions
Review the concepts of object-oriented programming within VBA including the hierarchy of the Excel Object Model
- Visual Basic Editor Options13:39
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.
- Create and Delete a Procedure08:51
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.
- Procedures5 questions
Test your knowledge of procedure declaration syntax in VBA!
- The Immediate Window and Debug.Print Method08:45
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 Method13:34
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.
A comment is a line that is ignored by VBA when a procedure is executed. Comments are created with the apostrophe symbol ( ' ). In this lesson, we'll explore the benefits of comments to the developer and practice writing them in the context of a procedure.
- Practice with Procedures6 questionsPractice writing VBA procedures from scratch.
- The Visual Basic Editor11 questions
Review the concepts introduced in this section including procedures, comments, the Immediate Window, the MsgBox method and more!
- Methods without Arguments03:28
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.
- Methods without Arguments3 questionsPractice invoking methods and overwriting properties on various objects in the Excel object model.
- Methods with Arguments08:30
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.
- Methods with Arguments3 questionsPractice invoking methods on VBA objects and passing arguments to them.
- Methods with Multiple Arguments06:42
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 Browser05:28
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.
- Ways to Invoke A Procedure08:17
In this lesson, we explore a myriad of ways to execute a public procedure from both the code editor and the Excel interface, including:
- The green execute button in the VBE
- The F5 keyboard shortcut in the VBE
- Clickable buttons
- The Quick Access Toolbar
- The Ribbon
- The TypeName Method05:53
The VBA.TypeName method accepts an VBA object and returns its type as a string. In this lesson, we practice invoking the method in the Immediate Window with a variety of inputs including strings, numbers, and various Excel objects.
- Objects and Methods9 questions
Test your knowledge of object and method invocation syntax in VBA.
- Syntax Tips07:37
In this lesson, we'll explore some tips for writing cleaner and more elegant VBA code.
- Variable Declarations and Assignments10:01
A variable is a placeholder for a value or object to be used within our procedure. In this lesson, we explore the syntax for declaring a variable and its data type, as well as assigning it an initial value with the assignment operator ( = ).
- Multiple Variable Declarations04:14
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.
- The Option Explicit Setting05:52
The Option Explicit setting at the top of a code module mandates that all variables be declared with a valid data type before being assigned a value. In this lesson, we compare a code sample with and without this setting enabled to see its advantages.
- The Byte, Integer and Long Data Types07:54
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.
- Numeric Data Types3 questionsTest your knowledge of the numeric data types (Byte, Integer, and Long) by writing 3 procedures from scratch.
- Mathematical Operations06:06
In this lesson, we dive into the mathematical operators for
- addition ( + )
- subtraction ( - )
- multiplication ( * )
- division ( / )
- exponentiation ( ^ )
- modulo ( Mod )
- The Single and Double Data Types05:59
The Single and Double data types represent a floating point number or, in other words, a number with a fractional or decimal component. We discuss the advantages of the Double data type and use it in a procedure that calculates a circle's circumference.
- The String Data Type04:58
A string is a collection of characters --- in layman's terms, it's just text. In this lesson, we take a look at VBA's support for two types of strings -- variable-length and fixed-length -- as well as the advantages of both.
- String and Double Data Types5 questions
Test your knowledge of the String and Double data types!
- The Boolean Data Type04:24
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.
- The Date Data Type06:22
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.
- The Variant Data Type05:42
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.
- The Object Data Type08:04
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.
- Default Values for Declared Variables02:53
Variables are assigned a default value when declared. In this lesson, we take a look at the default values for the String, Long, Double and Boolean data types.
- Variable Declarations2 questionsTie together the concepts introduced in this section by writing 2 procedures and declaring a ton of variables!
- Variable Scope09:30
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.
- Call A Procedure from Another one03:54
- Procedures with Arguments07:15
- Procedure Scope (Public vs. Private)06:54
- The Exit Sub Keywords01:49
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 explore constants, an alternative to variables. A constant's value cannot change over the course of a macro's execution, making it optimal for static values.
- Predefined Constants11:00
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.
- Procedures6 questions
Test your knowledge of procedure declaration syntax.
- The Application Object04:14
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 Property06:26
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.
- The Workbooks.Count and Worksheets.Count Properties02:07
The Workbooks.Count property returns the count of open workbooks. The Worksheets.Count property returns the count of worksheets in the selected workbook. In this lesson, we play around with these properties in the Immediate Window.
- The Workbooks.Open Method and Workbook.Path Property06:59
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 Workbooks.Close Method01:47
In this lesson, we use the Workbooks.Close method to close every open workbook in Excel. We also configure the procedure to disable alerts temporarily to avoid halting execution.
- The Workbooks.Add Method05:56
In this lesson, we invoke the Workbooks.Add method to create a new workbook from scratch. We also pass it an optional Template argument to make a copy of an existing workbook.
- The Workbook.SaveAs and Workbook.Save Methods09:31
In this lesson, we walk through the complete process of creating a new workbook, saving it, making changes, saving it again, and closing it. We introduce the SaveAs and Save methods on the Workbook object and explore their different use cases.
- The Workbook.Activate Method06:58
In this lesson, we invoke the Workbook.Active method to simulate a user click on a workbook and make it the ActiveWorkbook. We also review the ActiveSheet property, which targets the currently highlighted worksheet.
- The Workbook.Close Method04:13
The Workbook.Close method accepts a SaveChanges parameter; pass it a Boolean value of True to save the workbook before closing. In this lesson, we practice this concept by writing values to our red and blue workbooks.
- The Application, Workbooks and Workbook Objects6 questions
Test your knowledge of the Application, Workbooks and Workbook objects in the Excel object model
- The Worksheets.Add Method05:27
The Worksheets.Add method creates a new worksheet in the current Workbook. It accepts either one of two optional arguments, Before and After, that determine where the new worksheet will be placed.
- Add a New Worksheet to End of Current List of Worksheets1 questionUse the concepts introduced in the previous lesson to write a procedure that adds a new worksheet to the end of the current list of sheets.
- The Worksheet.Visible Property05:11
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 Method03:34
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.
- The Worksheet.Delete Method02:24
The Worksheet.Delete method removes a worksheet. It is particularly effective to temporarily disable alerts before invoking this method to make the process as smooth and seamless for the user as possible.
- The Worksheet.Move Method02:58
The Worksheet.Move method moves a worksheet to a different position in the order of workbook sheets. In this lesson, we explore the signature of the method including its familiar Before and After parameters.
- The Range.Select Method08:42
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.
- The Value vs. Text Properties03:50
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, Part I09:53
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.
- R1C1 Notation, Part II07:55
In this lesson, we continue to expand on the R1C1 concepts introduced in the past lesson by applying them to several real-life examples.
- The Formula and FormulaR1C1 Properties06:47
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 Property05:05
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 Property03:53
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 Offset and Resize Properties10 questions
- The Cells Property04:43
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 Property05:00
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 Range.End Property08:24
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.Count and Range.CountLarge Properties03:20
In this lesson, we use the Range.Count and Range.CountLarge properties to find out the number of cells in a given range. The returned value counts all cells, not just those with values.
- The Range.Row and Range.Column Properties02:25
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.
- The Range.Rows and Range.Columns Properties06:59
In this lesson, we explore the Rows and Columns properties, which are available on both the top-level Application object and a specific Range. The two can be used to target complete rows or columns, within the spreadsheet or within a specific range.
- The Range.EntireRow and Range.EntireColumn Properties02:54
The Range.EntireRow and Range.EntireColumn properties are used to target a complete row or column from a single cell. In this lesson, we play around with the two properties in the Immediate Window.
- Get Last Row of Data in Worksheet06:23
Daily reports will often have a fluctuating number of rows. In this lesson, we introduce a convenient strategy to identify the last row of data in a sheet --- starting at the bottom of the spreadsheet and navigating upwards.
- The Range.FillDown Method05:01
The Range.FillDown method populates a formula or value downwards based on an existing's cell value; this is the VBA emulation of the feature available in the Excel interface. In this lesson, we employ the method to concatenate text values across two columns together.
- The Range.Replace Method04:42
The Range.Replace method acts as a Find-and-Replace search mechanism. In this lesson, we apply it to a range of values in column A and discover a unique quirk when it comes to replacing numbers.
- The Range.TextToColumns Method09:28
The Range.TextToColumns method splits a string based on a delimiter, a special symbol. In this lesson, we use this feature to separate several strings, applying a variety of delimiters (commas, spaces, even custom vertical pipes) along the way.
- The Range.Worksheet Property02:09
In certain cases, VBA allows us to traverse the Excel object model in reverse --- from the bottom up. One such example is the Range.Worksheet property, which returns a Worksheet object that encloses a Range. In this lesson, we practice this property in the VBE.
- The Range.Sort Method12:32
Sorting is one of the most popular operations in Excel. In this lesson, we utilize the Range.Sort method to sort both one and two columns at a time (in ascending or descending order) and discuss how we can ignore the values in the header rows.
- The Range.Font Property09:07
The Range.Font property reveals a Font object complete with its own properties and methods. In this lesson, we explore several ways we can modify a font with VBA -- its name, its size, its bolding, and more!
- The Range.Interior Property06:41
What the Font object is to the foreground, the Interior property is to the background. In this lesson, we use the Range.Interior property to modify the color of a cell in a variety of different ways including:
- VBA color constants
- The RGB function
- The ColorIndex property
- The Range.ColumnWidth and Range.RowHeight Properties06:27
The Range.ColumnWidth and Range.RowHeight properties can resize the row and height of one or more columns or rows. In this lesson, we play around with these features in the Immediate Window.
- The Range.AutoFit Method02:58
Why manually adjust the width of a column when Excel can do it for you? In this lesson, we employ the Range.AutoFit method to dynamically expand a column so that it is just wide enough to fit all of its internal text.
- The Range.Clear, Range.ClearContents and Range.ClearFormats Methods04:02
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.
- The Range.Delete Method05:53
Most deletion operations will involve removing entire rows or columns at a time. In this lesson, we discuss how to make that happen in VBA as well as what happens when we delete a single cell instead.
- The Range.Copy and Range.Cut Methods04:00
Copy and paste --- is there a more common task in Excel? In this lesson, we use the Range.Copy and Range.Cut methods to copy, cut, and paste text across a spreadsheet.
- The Paste and PasteSpecial Methods on the Worksheet Object04:56
Excel offers several paste options in its user interface -- pasting just the value, just the formats, and more. In this lesson, we walk through how to emulate this feature in VBA with the help of predefined enumerations.
- The Parent Property on All Objects04:23
Coming full circle, we navigate from a Range object all to the way to the top of the Excel object model (the Application object) by relying on each object's Parent property. The property returns the object enclosing the current one.
- Boolean Expressions06:27
A Boolean is a special data type whose value can only be True or False. In this lesson, we explore the concept of truthiness and falsiness with the help of common mathematical operations and string comparisons.
- Boolean Expressions9 questions
- The If Then Statement04:05
The conditional If statement is the heart of programming. It allows our macro to have multiple branches --- different pathways to take depending on a given condition. In this lesson, we explore the technical syntax for implementing an If statement in VBA.
- The ElseIf and Else Statements10:41
Multiple pieces of conditional logic can be chained together with the If, ElseIf and Else keywords. In this lesson, we write a procedure that takes 3 possible paths of execution.
- Select Case10:41
Multiple If statements can quickly clutter up a procedure. The Select Case construct offers a convenient alternative. In this lesson, we'll explore the syntax for Select across a variety of examples.
- The AND & OR Logical Operators06:34
Multiple conditions can be checked with the And and Or logical operators. In this lesson, we apply these principles to conditional logic across two columns.
- Logical Operators14 questions
Each quiz question consists of two or more Booleans tied together with an AND or OR keyword. Determine whether each expression will evaluate to a True or False.
- The NOT Operator07:27
The NOT operator reverses a Boolean value. In this lesson, we use it to design our own implementation of Excel's bolding feature.
- The For Next Loop13:51
The For loop is a fundamental building block of programming. It allows us to iterate, or to repeat a certain block of instructions a given number of times. In this lesson, we explore the syntax for For loops and implement a few procedures to write to the spreadsheet.
- The Step Keyword07:09
The Step keyword can iterate with a different increment or decrement after each cycle of the loop. In this lesson, we experiment with a syntax and write a procedure that colors every second row yellow.
- Deleting Rows10:50
Deleting rows from a spreadsheet can be a risky operation --- if moving downwards in direction, there's a risk that some rows will NOT be deleted. In this lesson, we explore iteration in reverse (from a larger number to a smaller one) to show how to properly remove rows.
- Fill Empty Rows1 questionUtilize your knowledge of for loops to solve a VBA challenge.
- The For Each-Next Construct07:49
The For Each construct iterates over every single object in a collection. In this lesson, we practice iterating over the Workbooks and Worksheets collections.
- Iterating over a Range of Cells with For Each06:52
The For Each construct can also be used to iterate over a range of cells. Each object iterated over will be a single cell, itself a Range object. In this lesson, we iterate over and modify the values in a single column.
- The With-End With Construct07:11
The With - End With construct is a shorthand syntax that allows for multiple properties to be overwritten on an object. In this lesson, we practice modifying the Name, Size, Bold and Italic properties on the Font object.
- Exit For and Review of Exit Sub12:46
The Exit For keywords prematurely terminate a For loop. The Exit Sub keywords prematurely terminate a procedure. In this lesson, we practice implementing these concepts.
- Iteration5 questions
- The MsgBox Method In Depth, Part I08:03
In this lesson, we dive deeper into the MsgBox method including its Buttons and Title parameter. We modify the aesthetics of the alert, including the clickable buttons and the information icon.
- The MsgBox Method In Depth, Part II06:58
Continuing where we left off in the previous lesson, we wire up the buttons in a sample MsgBox to follow different branches of logic in our VBA code using the If conditional.
- MsgBox Options00:28
This article includes a complete list of all the constants that can be used to display different buttons on a MsgBox.
The StatusBar is an information bar located on the bottom left of the Excel interface. In this lesson, we run a long procedure and use the StatusBar to provide updates to the user on its completion status.
- The Application.ScreenUpdating Property04:18
The Application.ScreenUpdating property can be set to False to disable updates to the Excel interface. One way to optimize the speed of a macro is to turn the property off at the beginning of execution and reenable it at the end. In this lesson, we use a nested for loop to create a times table in our Excel spreadsheet and utilize ScreenUpdating to accelerate its execution.
The Range.SpecialCells method extracts a subset of cells from a Range based on a criteria -- empty cells, cells with constants, cells with formulas, etc. In this lesson, we populate a column with various values and use the method to target specific cells.
- The InputBox Function08:43
In this lesson, we use the InputBox function to collect a new worksheet title from the user. The InputBox is a basic dialog box with a single text entry field, a prompt, a title, and OK / Cancel buttons.
- The Application.InputBox Method09:28
The Application.InputBox method offers some additional flexibility when collecting user input. In this lesson, we provide a custom Type argument to collect a Range input from the user. We then color the range with an assorted collection of random colors.
- Intro to Arrays06:27
In this section, we'll explore the array, the most popular data structure in computer science. An array is a sequenced collection of elements, each of which is assigned an index position. In this lesson, we create a fixed-size array of strings and access its elements with a special notation.
- Alternate Syntax for Fixed-Size Arrays04:49
The LowerBound To UpperBound syntax can be used when declaring an array to modify the starting and ending index position. In this lesson, we declare an array of 6 whole numbers.
- The Option Base 1 Syntax and Write Array Values to Cells04:24
If zero-based indexing is confusing, the Option Base 1 keywords at the top of a module force an array to start at an index position of 1. In this lesson, we enable this setting and see its results in a newly declared array.
- Initialize Arrays within a For Loop05:58
Arrays can easily be populated with values from the worksheet using a basic For loop. In this lesson, we perform a popular string cleaning operation --- trimming the leading and trailing whitespace of a string -- on a column of values and then paste the results in an adjacent column.
- The LBound and UBound Methods06:12
The LBound and UBound methods return the smallest and greatest index positions of an array. In this lesson, we utilize these values to write a flexible, dynamic loop procedure.
- Dynamic Arrays09:34
A dynamic array can be resized repeatedly throughout a procedure to hold a different number of elements. This can be helpful when we cannot predict an array size in advance. In this lesson, we use a dynamic array to contain a list of values, modify them, and paste them back to the worksheet.
- The Range.RemoveDuplicates Method04:24
The Range.RemoveDuplicates method removes the repeating occurrences of a value in a given range. In this lesson, we use it to clear a list of duplicate names and show how arrays can be fed as arguments to methods.
- VBA Functions, Part I09:31
The VBA object includes many helper methods to assist the developer with common utility operations. In this lesson, we explore the LCase, UCase, Len, Trim, and InStr methods for working with strings.
- VBA Functions, Part II06:48
In this lesson, we explore 5 additional utility functions available in the Visual Basic Editor:
- Left for pulling out characters from the beginning of a string
- Right for pulling out characters from the end of a string
- Mid for pulling out characters from the middle of a string
- StrReverse for reversing a string
- Replace for replacing occurrences of a character in a string with another character.
- The Split Function04:23
The Split function splits a string based on the occurrence of a specific character (called a delimiter) and returns the results in an array. In this lesson, we split a cell's value by the presence of slashes and paste the results in adjacent cells.
- The Is Family of Functions09:55
VBA package several functions to determine if a value is of a certain data type. In this lesson, we explore the IsNumeric, IsDate, IsEmpty, and IsError functions and use them to populate a table on our spreadsheet.
- Date and Time Functions04:37
In this lesson, we explore the Date, Time and Now functions for retrieving the current date and/or time as well as the DateSerial function for generating a specific date in time.
- More Date and Time Functions06:07
In this lesson, we continue our exploration of additional datetime functions on the VBA helper object including Year, Month, Day, Hour, Minute, and Second. We also dive into the Weekday, WeekdayName and MonthName functions to discover information about a specific date.
- Excel Worksheet Functions08:27
Many of Excel's popular worksheet functions are available for use in the Visual Basic Editor. In this lesson, we implement the popular VLOOKUP function using code.
- Custom Functions10:36
In this lesson, we explore one of VBA's coolest features -- defining public custom functions that appear directly in the Excel interface.We dive into the syntax for declaring the function, identifying its expected arguments, and returning a final value.
- Intro to Error Handling07:19
Programmers of all experience levels make mistakes --- it's part of the job. Error handling is the ability to avoid errors in our code by catching them before they happen. In this lesson, we discuss some common errors we can run into in our VBA code.
- The OnError and GoTo Keywords07:06
The On Error Go To keywords redirect the pathway of a procedure to a specially designated section. In this lesson, we set up an example and discuss why the Exit Sub keywords have to be used to avoid running into the code automatically.
- The OnError Resume Next Keywords05:28
Some errors are not catastrophic to the health of a procedure --- some, in fact, are completely ignorable. In this lesson, we utilize the On Error Resume Next keywords to tell an iteration macro to continue in spite of any errors encountered along the way.
- Error and Err.Number08:03
The Err object collects information about errors encountered during macro execution. In this lesson, we explore its helpful Number property and take a look at over 100 different VBA error messages.
- Stepping Through Code08:24
The F8 key allows us to step through the lines of a procedure step by step. In this lesson, we see how this functionality grants tremendous power when debugging a complex piece of code.
Breakpoints force a procedure to halt execution at one or more specified lines. In this lesson, we practice enabling and disabling breakpoints in our code.
- Introduction to Events06:56
An event is a regular Excel action --- opening a worksheet, entering a cell value -- that can automatically trigger a VBA procedure. In this lesson, we compare event procedures to regular procedures and setup our VBE environment for writing them.
- The Worksheet_SelectionChange Event06:20
The Worksheet_SelectionChange event is triggered when the user navigates to a different cell with a spreadsheet. In this lesson, we react our first Excel event and also explore the ByVal keyword in the automatically generated procedure.
- Review of Application.EnableEvents03:27
The Application.EnableEvents property is used to enable and disable Excel events (automatic actions that are triggered by user interactions). In this lesson, we discuss the benefits of temporarily disabling this feature in an event procedure --- this helps avoid an infinite loop where a single event sets off a chain react of other events.
- The Worksheet_Change Event12:28
The Worksheet_Change event shoots off when the user edits or deletes a value from a Range in the worksheet. In this lesson, we use it to create a basic pounds-to-kilograms converter that takes a numeric value from column A, applies a formula, and writes it to column B.
- The Worksheet_Activate Event02:47
The Worksheet_Activate event is triggered when the user navigates to a different worksheet. It can be used to provide the user with instructions or warnings. In this lesson, we write a quick procedure to MsgBox out the activated sheet's name.
- Highlight Row and Column Up to Cell Where Cursor Is Placed1 questionHighlight the ActiveCell with the help of worksheet events.
- Workbook Events and The Sh Argument08:16
The Workbook event has its own set of event procedures. One common trend in their signatures is the presence of a Sh argument, which represents the sheet on which the event has been triggered. In this lesson, we construct a dynamic macro that tracks all of a user's entered cell values and writes them to a separate worksheet.
- The Workbook_Open Event02:51
The Workbook_Open event is triggered when a workbook is opened -- it can be an effective way to welcome a user or run a procedure. In this lesson, we utilize this feature in a new event procedure.
- Procedures with Boolean Arguments + The Workbook_BeforePrint Event03:54
Some event procedures feature a Cancel Boolean argument that can cancel a specific operation by being set to True in the body of the procedure. In this lesson, we try this out with the BeforePrint event procedure.
- Create UserForm, Toolbox, Properties, Controls07:00
The UserForm is a custom form that can be configured with labels, textboxes, buttons and more. In this lesson, we create our first UserForm and begin our discussion of popular naming conventions in the VBA community.
- The Label and TextBox Controls04:46
The Label form control adds a static piece of text to a UserForm. The Textbook form controls is an input box for the user to enter text. In this lesson, we add these two form controls to our UserForm.
- Naming Conventions03:14
The VBA community follows a popular convention for naming form controls. It begins with a 3-letter prefix for the type of form control, followed by the name of the entity or data point that the form control is related to. In this lesson, we review this conventions in relation to our existing UserForm.
- Naming Conventions00:08
This article includes the 3-letter prefix for all of the form controls available on a UserForm.
- Design Aesthetics06:14
The Format menu in the Visual Basic Editor offers options for aligning, grouping, and resizing one or more form controls. In this lesson, we apply these techniques to the controls in our employees UserForm.
- The CommandButton Control03:40
The CommandButton is a clickable button that trigger an event procedure. In this lesson, we add 3 buttons to our form -- Submit, Cancel and Hide.
- Add Event Procedure to Control05:14
In this lesson, we create our first event procedure to react to the Change event on our Textbox. Upon any user edit, it outputs the current entry to the Immediate Window.
- Unload and Hide a UserForm06:37
There are two ways to "shut down" a UserForm: unloading, which clears all of its data and hiding, which hides it from the screen but preserves the user's inputs. In this lesson, we wire up our two Command Buttons to utilize these two techniques.
- Submit the UserForm08:13
In this lesson, we wire up the final Command Button on our UserForm to capture the user's input and populate it on the spreadsheet.
- Activate a UserForm from Procedure03:37
The more we can abstract away the complexity of VBA from the regular Excel user, the better. In this lesson, we set up a graphic on our Excel interface to trigger the UserForm to appear.
- The initialize Event08:18
The initialize event shoots off before the UserForm is rendered. It can be used to calculate configure dynamic values. In this lesson, we create a new form that displays the current day of the week when it loads.
- The ListBox Control I - Wire up the Form08:16
In the first of a 3-part series, we introduce the ListBox form control for displaying a list of predefined values for the user to choose from. We hardwire our list to show the names of all of the workbook's sheets.
- The ListBox Control II - React to User Selection07:19
In this lesson, we wire up a Command Button event procedure to delete the single worksheet the user has selected from the ListBox.
- The ListBox Control III - Select Multiple Items09:24
The ListBox offers a way for the user to select more than one option. In this lesson, we enable this setting and configure our code to delete all the worksheets that a user selects from the list.
- The ComboBox Control I05:59
The ComboBox is a dropdown of predefined options that can optionally accept a new user input. In this lesson, we create two ComboBoxes to accept a font name and a font size.
- The ComboBox Control II10:37
In this lesson, we wire up the UserForm to iterate over all of the workbook's worksheets and normalize the font name and size based upon the user's ComboBox selections.
- The CheckBox Control09:59
The Checkbox is an ideal form control for simple Yes or No questions. In this lesson, we create a new UserForm that allows the user to choose to clear cells and / or save the workbook. We also dive a bit into modifying the aesthetics of a UserForm.
Explore the bonus content for the course!
JOIN OUR WHATSAPP GROUP TO GET LATEST COUPON AS SOON AS UPDATEDJOIN WHATSAPP
JOIN OUR TELEGRAM CHANNEL TO GET LATEST COUPONJOIN TELEGRAM
JOIN OUR FACEBOOK GROUP TO GET LATEST COUPONJOIN FACEBOOK
Free Online Tools And Converters for your use
Input a string of text or a URL and encode the entered stringTry it
Input an encoded string of text or a URL and decode the entered stringTry it
Color Contrast Checker (WCAG)
Calculate the color contrast ration for your website (WCAG)Try it
Paste or upload an XML and have it formatted to a beautiful XML formatTry it
URL Slug Generator
Convert any title or sentence into a variety of slugs for your pages URLTry it
Draw an e-signature or type a signature for your online signatureTry it