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.
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
The concept of macro is the core of VBA for Excel.
You will learn in this lesson what a macro is, how to generate a macro and how to execute it.
The easiest way to start off with VBA is by recording a macro.
You will learn how to record a macro and how to read and interpret the resulting VBA code.
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 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.
I have 20+ years experience in IT project management, software development and software architecture.
I have led several teams of software developers as project manager, quality manager and as team leader in different companies and different countries in sectors e.g. telecommunication, research & development, travel business, Internet marketing and Internet business, consultancy and services.
I have been responsible to collect the requirements of projects, applications and processes, design the databases and specify the final objects.
During my career I have accumulated a lot of experiences dealing with processes of any kind and the need to define the tools to manage them and collect the information.
I have organized all my experiences in courses which are not only a list of functions and methods but contain also my practical experiences and considerations about how to cope with the different situations and which solutions to suggest.