Excel VBA for Beginners
- 5 hours on-demand video
- 5 articles
- 12 downloadable resources
- 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
- Register and reuse VBA macro in Excel
- Create your own VBA macros in Excel
- Understand the VBA code in Excel macros
- Generate automated reports in Excel with VBA
- Manipulate data in Excel with VBA
- Format data and worksheets in Excel with VBA
- Import and export external data from files to and from Excel using VBA
- Understand and use the VBA editor in Excel to write macros
- Understand the concepts of subroutines, functions, constants and variables in VBA for Excel
- Manage errors in VBA
- Understand and use dialog boxes e.g. msgbox and inputbox
- create interactions between worksheets in Excel using VBA
- basic knowledge of Microsoft Office products
- be familiar with computers
I created this corse since I have experienced during my live trainings and even from my students of other VBA courses that I released on the Internet, that most of my students are not IT-skilled people. They are just users of Excel and want to go beyond the basic functionalities of Excel and create automated report or automate daily tasks.
For this reason I have created a course with the right pace to make it clear to anyone.
In this course I start explaining what VBA is and why VBA. For example, if you are an user of Excel, e.g. in the Accounting Department, what VBA is and why you need it.
For this reason it is important to understand first of all the concept of object oriented programming and explain how Excel is an application structured in objects whose properties and actions/events can be managed, manipulated and updated using VBA.
I start with an overview of the Developer ribbon, which is the menu section where to record macros, execute macros, access the VBA editor and much more.
I will show and explain the structure of a VBA object and how to record a VBA macro.
Once recorded, I will explain how to read and interpret a VBA code and how to modify it for your goals.
I will show how to make the user interact with the data in the worksheets using userforms and how to format the reports automatically.
I have organized this course focusing on an example which is an Excel file or workbook composed of more worksheets.
At the end the user will be able to exchange data between the worksheets and import/export them to external text and binary files.
Begin with VBA: why VBA, understand the VBA editor, learn how to record and execute a VBA macro, learn to read and interpret the VBA code, modify the VBA code, modify the macro security level.
Understand the VBA code: the Excel objects and how and where to manage/edit their properties and actions/methods based on their events
The VBA project: what is a VBA project, how it is structure, the components, where to insert your code, how to set the properties of the objects
The VBA programming principles: variables, constants, functions and subroutines. What is the difference between private and public. What is a module. What are the variable types.
Interact with the data: read/retrieve data with loop instructions. Select data using conditional instructions. Strings and string functions.
Interact with the user: exchange data and information using userforms, msgboxes and inputboxes. Understand the VBA control objects on userforms.
VBA commands on worksheets: interact directly on a worksheet using control objects and directly on it and how to trigger actions and events.
Structure of data: understand arrays and their size, how to populate arrays and manage their size. The Type data structure to save data into records. Collections.
Manage external files with VBA: read and write to text and binary files.
Errors: manage errors caused by the interection of the user with Excel.
At the end you will find some exercise to improve your skill and verify your knowledge in VBA.
- familiarity with the function and basic use of the software
- Who is familiar with computers and the Internet
- familiarity with Windows or Mac computers
- Who owns a copy of Microsoft Office with Excel
When you want to start off with VBA for Excel you need to know what you need to do first.
First of all you need to know what a ribbon in Excel is and in particular the Developer ribbon.
You need to make it visible and know how to use the various functions.
The Developer ribbon is needed if you want to access the VBA editor, run and execute macros and select VBA controls to use on Excel worksheets.
But to make VBA executable on an Excel file it is important to save this file in a particular format.
In this lesson you will learn how to activate and use the Developer ribbon and how to save an Excel file in the right format in order to make VBA macros executable.
In this lesson you will get familiar with the VBA Editor.
You will know how to access it and how to setup the skeleton of a VBA project.
You will recognize other open projects and how to access the single modules of a project.
You will understand where to write the VBA code to interact with Worksheets and Workbook.
You will get familiar with the concept of VBA module
Macros are very important to improve Excel but they can be a critical elements if not used properly.
In this lesson you will learn how to set up and understand the various levels of security for an Excel file which will allow different level of access and execution to the VBA macros: how to enable automatically macros, how to disable all macros per default, how to ask the user if to enable or not macros.
Once you recorded your macros you need to understand the recorded VBA code in order for you to customize it and produce new and more flexible routines.
In this lesson we will go through the code of a recorded macro to understand the commands behind the code and how to modify the code in order to adapt the same for other similar situations.
After understanding the recorded VBA code we will go through our example from the previous lesson to customize the macro to be used to format ranges or cells no matter where they are.
The recorded macro is aimed to format some properties of a selected cell (border, background colof, etc.) and we will modify this code in order to re-use it also for other cells.
In this lesson we will go into details of the VBA Editor to understand the structure of a VBA project.
We will see how to add modules, how to access the area relative to the worksheets and the workbook and how to access to the properties of the single components.
We will understand the code area of a worksheet.
We will understand what is the "General" area and what the "Worksheet" area.
We will see what are native Worksheet methods.
We will see some examples based on the methods attached to the events "selection change" and "change".
In this lesson we will see how to define variables in a Worksheet module and how to access them outside and inside subroutines.
We will start to define our first general routines to be used in different scenarios. We will see a routine which will execute a simple formula to be used in any worksheet or other modules.
As for the Worksheet module we will understand how to manipulate the native methods of the Workbook object.
We will show an example based on the native routine "on workbook open", i.e. we will trigger an action when an Excel file will be opened.
We will also see how to access the single worksheets from the parent workbook and we will list their names.
In this lesson we will learn how to defined and customize user-defined subroutines and functions.
We will see the difference between subroutines and functions and we will see when to use subroutines and when to use functions.
We will see simple formulas and we will show them using subroutines and then using functions.
In this lesson we will insert a module in our VBA project and we will add variables, constants, subroutines and functions.
Then we will use them to interact with the worksheets in the workbook.
We will see how to insert, import and export modules as separate components to be used in other files.
As for the subroutines and functions we will see the difference between public and private variables.
We will see examples of public and private variables and we will see the consequences of defining variables in the wrong ways, i.e. when you have not to declare them as public and when not to set them as private.
In this lesson we will see the different types of variables.
We will see what and when to use types e.g. integer, long, single, boolean, string, etc.
We will see when you need to define the variables and how to transform some values when you want to compare them to the values of variables of specific types.
We will see what is an explicit definition of variables and what is an implicit definition ("option explicit" and "option implicit").
In this lesson we will get familiar with the for..next and for each loops.
We will see how to loop among a list of elements to display them or their properties or to retrieve one of some of them according to certain criterias.
We will see how to display one by one the elements listed in an Excel workshett.
We will see the difference between the for...next and for each.. loops and when it is appropriate to use one or the other.
In this lesson we will see the Do...Loop Until instruction to loop between a set of elements until a cretain criteria or condition is matched.
For example we will list the elements in a worksheet incrementing the row each time, until the row is greater then or equal to a fixed value.
In this lesson we will see the IF, Else, ElseIf instructions.
We will verify if some conditions are matched while looking for elements in a worksheet.
The condition If verifies for example if the value of an element of parameter is equal to another one or different. Based on the satisfaction of the condition we will trigger some actions or routines.
We will see how to structure and manage more conditions with the ElseIf and Else instructions.
Now that we know how to add modules and produce VBA codes in modules, worksheets and workbooks, it is the time to go one step ahead and learn how to insert and use UserForms.
UserForms are dialogs including objects, the controls or commands (buttons, textboxes, comboboxes, lists, etc.) which are used to make the user interact with your applications or Excel worksheets and workbooks.
Now that we have created our UserForm and set the appropriate layout it is time to add events to the controls displayed on it.
We will start adding methods of buttons to be triggered by clicking on them or pressing on a specific key on the keyboard once the focus is on them.
We will see how to show and hide UserForms by clicking on buttons.
In this lesson we will create code to manage and validate with conditional instructions learnt before the values inserted in textboxes displayed in the UserForm created so far.
We will start to create a "save data" routine to be triggered by clicking on a button and after validating the data input in the textboxes.
In this lesson we will add a combobox to our UserForm and will add elements when the UserForm is displayed.
We will need to do this modifying the code of the UserForm.
We will see also how to manipulate the properties of the combobox object to customize its look & feel and functionalities.
In this lesson we will learn what a msgbox is and we will learn how to use this feature to display ad hoc messages to the user while interacting with our macros.
We will customize the look & feel of the MsgBox dialog and display special messages.
In this lesson we will use MsgBox forms to display queries to the user.
We will see how to display questions and according to the choice of the User we may display another special form, the InputBox, to input additional data or information, needed to proceed with the execution of the macro.
VBA controls can also be set on Excel worksheets and not only on UserForms.
In this way a worksheet can be turned into an application.
For the user it is easier to execute macros.
Controls can be used to filter data or execute macros.
In this lesson we will see how we can use buttons to execute macros or display UserForms.
Comboboxes are also available to be displayed on worksheets.
They are objects or shapes identified by the name "DropDown".
In this lesson we will see how to add a combobox list in a worksheet, how to populate this list with data (which is listed in a worksheet) and how to get the selected item to be used e.g. for filtering or seraching purposes.
In this lesson we will start talking about arrays.
Arrays are data structure to store list of data.
We will talk about one-dimensional arrays with fixed size.
This means we will define an array e.g. of 6 elements (where 6 is the size) and we will assign values to the 6 elements of the array based on a list of data on an Excel worksheet and with a For..Next loop we will display them on a MsgBox.
In this lesson we will go more into details the concept of size for an array.
We will see the concept of LBound and UBound to get the starting point and end point of an array.
We will display the elements of an array using a For..Next loop based on the LBound and UBound functions.
An alternative to arrays are the collections.
Collections are very useful if the size of an array is unknown and may change every time a new element must be added to the current list.
We will go back to our example of arrays from the previous lessons and we will store the list of data inside a collection instead of inserting them into an array.
Types are data structure to store complex data composed of several elements e.g. in a matrix where the columns have a known name. We will for example how to store the information of a list of customer where for each customer we need to specify the id, the contact name, the city and the country.
Types match the concept of database table. In fact they are also known as records.
In this lesson we will read the binary file saved in the previous example and we will display the retrieved data into a table in an Excel worksheet.
We need to define a type for the customer information and an array of customers.
We will read each byte of the file and interpret every element as a field of the customer type.
Each line of the resulting Excel table will contain the information of one customer.
We already saw that errors may occur when executing a VBA macro in Excel.
For example we may enter a string in a textbox when a numeric value is expected.
VBA automatically generates an error.
We will see what to do to manage the situation when an error occur.
In fact we need to prevent that the final user is notified about the occurrance of an error without understanding what that error means and what to do in these circumstances.
We will see how to interpret the error, read the error number and description and how to customize the code to manage the next steps.
There are moments when the user should not be notified when an error occurs and the error must be ignore and the code should proceed on the next line/step.
This happens e.g. when someone types a text where a numeric value is requested and should be replaced by a "0" or a "N.A." string automatically without notifying the user.
We will see in this lesson how to do this.