Excel VBA for Beginners

In this lesson we will learn what the Workbook object is and how to access it.

We will see how to assign and customize and access the Workbook properties.

In this lesson we will see how to access the Worksheet object as sub-components of the Workbook.

We will see how to refer to a specific Worksheet within a workbook and read and set properties e.g. "count" and "name".

In this lesson we will see the concepts of cell and range.

We will see how to get the single cells or cluster of cells and how to access their properties and how to set them.

Among them "FormulaR1C1" and "Value".

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 understand how to manipulate the properties of a worksheet with VBA and the Worksheet module.

We will loop through the worksheets of a workbook and will display their names.

In this lesson we will see in details the Worksheet methods.

We will trigger actions when selecting a cell or by changing the value of a cell in a worksheet.

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 start to go into details to the VBA code in general.

We will understand and define variables and constants in VBA.

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 define functions in VBA and we will set them to be used directly on Excel worksheets as user-defined functions or formulas.

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.

We will recap the concepts of module and worksheet and we will see how to insert other objects to a VBA project, e.g. the UserForms and other modules.

We will see how to view/access the code of a module and of a worksheet.

In this lesson we will see the concepts of "public" and "private".

We will see when you need to define a subroutine or function as public and when you should define it as private.

We will see how to access private routines from public ones.

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 while...wend instruction to loop between a set of elements while a cretain criteria or condition is matched.

For example we will list the elements in a worksheet incrementing the row each time, while the row is less than a fixed value.

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.

"Select Case" is an alternative conditional instruction to the "IF, Else, ElseIf" one.

We will see when it is recommended to use this condition to compare values or properties of elements.

In this lesson we will focus on strings, i.e. text elements.

We will learn how to use functions to get part of a string e.g. Left, Right, Trim, Mid, RTrim and LTrim.

We will also functions to verify if a substring is contained in a given string, e.g. the Instr function.

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.

In this lesson we will see how to add a UserForm to a VBA project and how to design it.

We will resize the form and add elements e.g. textboxes and buttons.

We will focus on example where the user has to input the data of a single customer.

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 get back to our objects and their methods and will customize their properties and methods at our purposes.

We will set buttons as "default" and others as "cancel" buttons. We will also see in details these concepts.

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.

Finally, starting from what we have created so far in our UserForm (see the previous lessons) we will trigger an action to import the data input in the UserForm to specific cells or ranges in a worksheet.

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 more into details with MsgBox and we will customize the form to include other and more buttons to execute specific actions according to the displayed message.

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 see how to assign routines/methods to controls inserted in a worksheet to execute macros.

This is an example to understand how the user can interact with an Excel worksheet using the VBA controls.

In this example we will see how to use a dropdown to filter and format data in an Excel table.

From the results of the previous example we will improve VBA code to improve the format of the filtered ranges.

In this lesson we will see the function Application.ScreenUpdating to improve the performances when executing a macro on an Excel worksheet.

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.

In case you need to update continuously a list then array of variable size are needed.

We will see how to define flexible arrays and we will see how to increment the size of an array each time a new element must be added to the list.

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 start talking about files.

We will see how we can save the data in an Excel worksheet into a text or CSV file.

In this lesson we will see how we can read the text or CSV file saved in the example of the previous lesson and insert the data sequentially in the cells of an Excel worksheet.

In this lesson we will understand the concept of binary file and we will see an example where we will save the data in a binary file.

We will put the elements of an Excel table in a worksheet into an array of customer data type and we will save them as bytes in a binary file.

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.