Excel VBA for Beginners
3.6 (19 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,072 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA for Beginners to your Wishlist.

Add to Wishlist

Excel VBA for Beginners

the basic concepts to get started and understand EXCEL VBA however which skill level you are and automate MS Excel
3.6 (19 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,072 students enrolled
Created by Daniele Protti
Last updated 9/2016
English
Current price: $10 Original price: $60 Discount: 83% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5 hours on-demand video
  • 5 Articles
  • 12 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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
View Curriculum
Requirements
  • basic knowledge of Microsoft Office products
  • be familiar with computers
Description

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.

In details:

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.

Who is the target audience?
  • 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
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 67 Lectures Collapse All 67 Lectures 04:51:32
+
Begin with VBA
10 Lectures 55:31

  • User-defined functions
  • Custom Excel functions
Preview 06:46


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.

The prerequisites: the developer ribbon and the Excel file format
05:05

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 VBA Editor: VBE
07:54

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 concept of Macro
02:56

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.

Record a macro
06:29

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.

Macro Security
05:37

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.

Understand the recorded VBA code
09:18

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.

Modify the code of a recorded macro
06:33
+
Understand the VBA Code and the Excel Object Hierarchy
3 Lectures 13:06

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.

The Workbook object
04:19

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".

The Worksheet object
03:35

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".

The Cell and Range objects
05:12
+
The VBA Project
6 Lectures 23:06

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.

The VBA Project structure and composition
03:29

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".

The Worksheet Module Code
03:27

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.

The Worksheet Properties
05:03

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.

The Worksheet Methods
02:44

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.

The General variables and subroutines
05:23

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.

The Workbook Methods
03:00
+
The VBA Programming Principles
8 Lectures 31:13

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.

Variables and Constants
02:30

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.

Subroutines and Functions
03:28

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.

User Defined Functions
01:38

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.

VBA Modules
03:06

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.

The Worksheet Module
03:03

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.

Public and Private Subroutines and Functions
08:13

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.

Public and Private Variables
05:46

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").

Variable Types and Declarations
03:29
+
Interact with the data
6 Lectures 27:28

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.

The Loop For .. Next and For each ...Next
08:32

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.

The Loop While
02:09

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.

The Loop Do Until
02:10

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.

The Conditional Instructions: If, Else, ElseIF
04:53

"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.

The Conditional Instructions: Select Case
01:46

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.

Strings and String Functions
07:58
+
Interact with the User
10 Lectures 44:26

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.

The UserForms
03:54

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.

Build a custom UserForm
06:34

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.

Customize the events and properties of commands in a UserForm
05:05

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.

Control the data in textboxes in a UserForm
08:29

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.

Customize the properties and methods of button controls
02:00

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.

Dynamically populate a combobox
02:14

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.

Exchange Data between UserForms and Worksheets
03:55

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.

MsgBox: Customize your messages and warnings for the User
03:02

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.

MsgBox: Customize the buttons of your message dialog
01:26

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.

MsgBox and InputBox: Manage User Queries
07:47
+
VBA commands on Worksheets
6 Lectures 19:41

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.

Trigger Actions on Worksheets with buttons
03:14

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.

DropDowns on Worksheets to select pre-options
02:16

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.

Trigger actions with VBA controls on a Worksheet
04:24

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

Filter data using VBA controls on a Worksheet (Part I)
03:07

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

Filter data using VBA controls on a Worksheet (Part II)
04:56

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

Improve the performances of subroutines of VBA controls on a Worksheet
01:44
+
Structure of Data
5 Lectures 26:14

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.

Arrays: fixed size Arrays
05:10

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.

Arrays: Array size
03:35

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.

Arrays: variable size Arrays
07:28

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.

Collections
03:09

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.

The Type Data Structure
06:52
+
Manage external files with VBA
4 Lectures 23:28

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.

Write to a text file
06:23

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.

Read from a text file
06:59

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.

Write to a binary file
05:06

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.

Read from a binary file
05:00
+
Errors
2 Lectures 07:52

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.

Manage errors
04:32

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.

Ignore errors
03:20
1 More Section
About the Instructor
Daniele Protti
3.5 Average rating
253 Reviews
7,541 Students
14 Courses
Project Manager and Software Architect

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.

Most of my tools have been written in VB, VBA, C++, C#, Java, PHP, Javascript using databases e.g. Informix, Postgres, Oracle and MySql.

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.