Excel VBA (Macros) with Projects

Learn to use VBA to enhance Excel's functionality to increase efficiency and productivity
3.9 (13 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.
573 students enrolled
$19
$50
62% off
Take This Course
  • Lectures 105
  • Length 8 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2014 English

Course Description

Microsoft Excel solves lot of our problems by simple use of formulae. However, lot of times you will notice that during moving or sorting data, we end up doing, we end up doing same tasks again and again. Sometimes, a simple program can automate the job and Excel has an easy-to-use programming language built into all versions, VBA - Visual Basic for Applications.

VBA is derived from Visual Basic 6 (the most used programming language until Microsoft .NET). However, VBA is still well suited for getting jobs done quickly and easily. It may not be be as fancy as building large applications but for it is superb for automating Excel and building Excel-based applications. Knowledge of VBA will also help you in other Microsoft Office products.

VBA (Macros) for Excel is one of the most sought after skill sets IF you work involves working with spreadsheets, a LOT of data and repetitive tasks. Professionals learning SAS Business Analytics, too, learn VBA as a pre-requisite.

This 8 hrs.+ program assumes that you are not a programmer and are at a Intermediate/Advanced level of Excel user. It will teach you how to write, edit and test Macros codes, which can be used to automate Excel based tasks and functions.

Macros - Nuts and Bolts

  • Programming concepts
  • Work Environment & Essential Settings
  • Components - Objects, Methods, Properties
  • Examples based Projects
  • Record/Run and Edit Macros
  • Procedures - Create, Call, Arguments
  • Variables & Constants - Data Types
  • Operators
  • Conditional Statements & Loops
  • Object Reference Model
  • User-defined functions (UDF)
  • Events to trigger Macros
  • Error and Debugging
  • Add-ins
  • User-forms... and more

What are the requirements?

  • Basic Excel knowledge
  • No Prior programming knowledge required

What am I going to get from this course?

  • Programming concepts
  • Work Environment & Essential Settings
  • Components - Objects, Methods, Properties
  • Record/Run and Edit Macros
  • Procedures - Create, Call, Arguments
  • Variables & Constants - Data Types
  • Operators
  • Conditional Statements & Loops
  • Object Reference Model
  • User-defined functions (UDF)
  • Events to trigger Macros
  • Error and Debugging
  • Add-ins
  • Advanced Concepts - Early binding vs. Late Binding
  • Advanced Concepts - User forms for user-input based tasks
  • Projects based Examples

What is the target audience?

  • MIS Analysis
  • Operations
  • Analytics Process automation
  • Database Administrators
  • Financial Analyst
  • Consulting

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: The Macro Recorder
03:34

Course overview to understand what you will learn from the course.

----

Join our community to get latest trends, tutorials and updates

Blog | Facebook | Twitter | YouTube | Google+

03:09

    Understanding what are macros

    Why are macros used?

06:15

    How are macros created?

    TECHNIQUES:RECORDING AND WRITING USING VBA

    How to save a macro?

    TECHNIQUES: .XLSM FILE EXTENSION

04:46

      How to record a simple macro?

    1. TECHNIQUES: USING DEVELOPER TAB TO RECORD A SIMPLE MACRO
    2. TECHNIQUES: ASSIGNING A SHORTCUT KEY TO A MACRO
    3. TECHNIQUES: USING RELATIVE REFERERENCE SO THAT MACROS ARE RECORDER WITH THE ACTIONS RELATIVE TO THE INITIAL SELECTED CELL.
    4. TECHNIQUES: RUNNING A MACRO
02:46

      How to edit a recorded macro?

    • TECHNIQUES:USING VBA EDITOR TO EDIT THE MACRO CODE
    • How to change the short-cut key of a recorded macro?

      TECHNIQUES:USING THE OPTION BUTTON TO CHANGE THE SHORT-CUT KEY OF A RECORDED MACRO

05:03

      How to save a recorded macro?

      TECHNIQUES:USE OF DIFFERENT FILE EXTENSIONS TO SAVE THE RECORDED MACRO E.g..XLSX, and XLSM

03:38

Learn how to apply Macro Security settings.

TECHNIQUES: USE OF TRUST CENTER TO APPLY MACRO SECURITY

07:10

    Understandwhat are the different ways in which you can run a macro.

    TECHNIQUES:

  1. USING A SHORTCUT KEY TO RUN A MACRO
  2. USING COMMAND BUTTON
  3. USING SHAPES TO RUN A MACRO
  4. USING F5 SHORTCUT KEY TO RUN A MACRO
  5. USING QUICK ACCESS TOOL BAR
05:34

    Defining a Storage Location for Macro

    TECHNIQUES:USE OF THE OPTION viz., THIS WORKBOOK, PERSONAL WORKBOOK AND NEW WORKBOOK.

Section 2: VBA fundamentals
05:41

Understanding the In and Out of programming

15:38
  • What is VBA?
  • Benefits of VBA
  • Limitations of VBA
  • Scope of VBA
  • What is VB Editor?
  • Understanding the concept of Objects, Properties and Methods
01:10

Learn to enable the developer tab on various versions of Microsoft Excel

08:29

    Understanding the Visual Basic Editor Window

    TECHNIQUES:

  1. Understanding the project explorer
  2. Understanding the properties window
  3. Understanding the code window
  4. Understanding the immediate wndow
08:32

    Writing a small VBA code

    TECHNIQUE:

  1. Understanding how to write a small vba code
  2. Using vba editor to write a macro code
  3. Running the macro code
Section 3: Building Procedures
04:39

Learn to create sub procedure using Sub Macroname()

03:28

    Understanding what Comments are and how to add comments in VBA procedure?

    TECHNIQUES: Using comment block and uncomment block to add and remove comments.

02:04

Learn to use private and public procedure

02:14

Understanding how to call sub procedure

03:38

Understanding how to pass arguments to a procedure by value and reference

Section 4: Working With Excel VBA Functions
03:28

Learn how to create functions in VBA

02:17

·Understand how to use conditions in Functions

17:19

·Learn how to use conditions If...Then....Else in VBA

03:06

    Learn how to create your own custom functions in Excel VBA and add it as a user defined functions

    TECHNIQUES:

  1. Create a function
  2. Add it using insert function option in the insert tab
Section 5: Basics of VBA coding
02:08

    Understand what variables are and how to assign them.

    TECHNIQUES: USING VARIABLE NAME = VALUE

15:57

    Scope of a variable

    TECHNIQUES:

  1. Procedure level
  2. Module level
  3. Public module level.

    Life of a Variable

    TECHNIQUES:

  1. Static
  2. Dynamic
06:24

    Understand what are data types and how are they used in the VBA procedure

    TECHNIQUES:

  1. Use the Syntax
  2. Dim variable As Double
  3. Static variable As String
  4. Public variable As Decimal = 0
02:33

Understanding what constants are and how to use them

14:05

    Understanding what operators are and types of operators

    TECHNIQUES:

  1. Arithmetic operators
  2. Boolean operators
  3. Relational operators
Section 6: Working with the dialog boxes
01:09

How to create and use MsgBox in VBA

01:48

    Understanding how to add arguments to a MsgBox in VBA

    TECHNIQUES:

    Here are the three arguments that we will use :MSGBOX([TEXT], [BUTTONS], [TITLE])

06:17

How to create and use Input Box in VBA.

Section 7: Working with the objects
07:53
  1. Understanding Application Object
  2. ·Understanding Workbook Object
  3. Understanding Worksheet Object
  4. Understanding Range Object
  5. Understanding Cell Object
05:24

    What is the Range Object and how to use it in VB Editor

    TECHNIQUES:

    Range(“A1”).Value = ”abc”

  1. You can use named ranges in Range object like this: Range(“myStoreList”)
  2. You can use square brackets [] to refer to ranges like this: [A10] refers to Range(“A10″)
  3. You can use variables in Range Object like this: Range(mylist) refers to whatever address is stored in mylist variable.
  4. You can use variables along with static text, like this: Range(“A1:A” &endPoint) refers to the range A1:A120, assuming endPoint variable is 120.
03:27

What is the Cell Object and how to use it in VB Editor

03:22

Understanding the Range Properties and Methods and how to use them.

    TECHNIQUES:-

  1. RANGE(“A1”).VALUE=”ABC”……EXAMPLE OF A RANGE RPROPERTY
  2. RANGE(“A1”).SELECT………………EXAMPLE OF A RANGE METHOD
01:36

Returns a Long value that represents the number of objects in the collection

03:14
  1. Learn how to merge the cell and range objects.
  2. Returns a Range object that represents the cells in the specified range.
00:47

Learn how to use Selection object.

04:19

    This example teaches you how to select entire rows and columns in Excel VBA.

    TECHNIQUES:

  1. Rows(Row index No.).Select
  2. Columns(Column index No.).Select
01:40

Dim sets a Variable (a value that can be changed depending upon the input) while Set is a constant value that should remain constant, unless changed by another program

01:58

Learn how to use Worksheet object

03:10

The following properties for returning a Worksheet object are described in this section:

  1. Worksheets property
  2. ActiveSheet property
02:06

·Learn how to use Workbook object

00:53

    ou can delete workbooks by using the KILL command.

    TECHNIQUES: Kill "C:\WorkbookName.xlsx"

02:45

    Learn how to use Application object.

    TECHNIQUES:-

    Use the Application property to return the Application object. The following example applies the Windows property to the Application object.

    APPLICATION.WINDOWS("WORKBOOKNAME.XLSX").ACTIVATE

05:21

    See a list of all the different objects with their methods, properties, events and constants using Object browser.

    TECHNIQUES:

  1. Use F2 key to view the object browser
  2. By selecting a class, you can display its members which are of three key types; properties,methods and events.
Section 8: Manipulating Excel Objects
10:18
  1. Learn and understand how to use offset function for making the range act as dynamic.
  2. Returns a Range object that represents a range that’s offset from the specified range.
04:23

How to use offset function in excel

02:19

How to use Range.Offset property

Section 9: Loops
02:11

    Learn and understand what Loops are

    You can write Visual Basic code that makes decisions and repeats actions.

    Looping allows you to run a group of statements repeatedly. Some loops repeat statements until a condition are False; others repeat statements until a condition are True. There are also loops that repeat statements a specific number of times or for each object in a collection.

    TECHNIQUES:-

  • Do...Loop: Looping while or until a condition is True
  • For...Next: Using a counter to run statements a specified number of times
  • For Each...Next: Repeating a group of statements for each object in a collection
12:07

Learn and understand For-Next Loop

05:35

Learn and understand Do-While Loop

01:56

Learn and understand Do Loop

10:15

Assignment on the Loops

04:35

Learn and understand For each-Next loop

Section 10: Events - Triggering code with Events
05:18

Learn and understand how event are actions performed by users to trigger the Excel VBA to execute code

01:18

·Learn and understand how to create Workbook Close Event

TECHNIQUES:-to execute instructions immediately before the workbook is closed, choose BeforeClose

02:04

Learn and understand how to create workbook activate and deactivate events

01:36

Additional chapter on Activate-Deactivate Events

01:46

Learn and understand how to create worksheets activate and deactivate

07:36

    Learn and understand how to create Application Events.

    TECHNIQUES:-

    The basic steps to using application level events are

  1. Create a public variable in a standard module.
  2. Create a class module with a public variable using the WithEvents keyword and dimmed as the Application object.
  3. Assign the class module variable to the object in a procedure (usually Workbook_Open).
Section 11: Creating Excel Add-ins
04:53

    An Excel Add-In is a file (usually with an .xlaor .xllextension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xlaAdd-In) that adds additional functionality to Excel, usually in the form of new functions.

    This Video shows you how to write a custom functions using Excel VBA and how to save and install it as an Add-In.

    TECHNIQUES: An Add-In can contain as many UDFs as you want, and you can add more later simply by opening and editing the Add-In file.

    Steps to Creating an Excel Add-In: Adding a Description to the Function ->.Save the Workbook as an Add-In -> Add a Description to the Add-In -> Install the Add-In

Section 12: Debugging And Error Handling
12:58

    Learn how to handle the errors in VBA.

    There are steps that developers can take to help reduce unwanted errors.

    TECHNIQUES:-

    Before understanding and applying error-handling routines, planning to avoid errors should be undertaken.

    There are three different types of errors:

  1. Design Time Errors: To switch off the error prompt
  2. Run Time Errors
  3. Logical Errors: Using Debugging Tools to find out such errors

07:20

    Debugging is the process of stepping through the code line by line and checking the reaction of each line to help trace errors that may be difficult to find at run time especially logical errors.

    TECHNIQUES:

  1. Learn how to use Debug Toolbar
  2. Use F8 key to step-by-step check the code.
  3. Using Breakpoints
  4. Using Debug.Print Command
07:59

In this section, learn some interesting tips & ideas to improve your VBA proficiency. Following are some interesting tips & ideas to improve your VBA skills:-

  1. Think Thru before Coding
  2. Use the Recorder
  3. Use Immediate Window
  4. Debug.Print is your Friend
  5. There is a method for that!
  6. Break Your Work in to Smaller Chunks
  7. Build Iteratively
  8. Keep a Good Reference Handy
  9. Take up Challenges
  10. Use VBA only when you need it
Section 13: Working with the Arrays in VBA
04:28
  1. An array is a single variable with many compartments to store values, while a typical variable has only one storage compartment in which it can store only one value.
  2. You can declare an array to work with a set of values of the same data type.
12:02

The Array function in Excel VBA can be used to quickly and easily initialize an array.

    TECHNIQUES:-

  1. Learn how to create Fixed and Dynamic Arrays
  2. Learn how to create Multi-dimensional Arrays
  3. Use the Array Function to assign an array to the variable
02:37

To get the size of an array in Excel VBA, you can use the UBound and LBound functions.

    TECHNIQUES:-

  1. Determining the Size of an Array
  2. Use of CTRL+SHIFT+ENTER
  3. UBound(Films, 1) gives the upper limit of the first dimension and LBound(Films, 1) gives the lower limit of the first dimension
Section 14: Working with the Userforms
04:51

·Use InputBox to display a simple dialog box so that you can enter information to be used in a macro.

01:19

A UserForm object is a window or dialog box that makes up part of an application's user interface.

03:26

    Create a userform canvas

  1. Name the UserForm
  2. Add the controls to the userform
  3. Set the properties of Each Control
  4. Add the code to the form controls
  5. Initializing the Form.
  6. Making Buttons work.
  7. Show userform code
  8. Code the interaction to the worksheet using worksheet events
  9. Add a button and attach a macro to it.
  10. Test the UserForm
00:48

·To create this Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below

06:44

Form Controls are objects which you can place onto an Excel Worksheet or User Forms, which give you the functionality to interact with your data. You can use these controls to help enter and select data.

By adding a control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the control.

TECHNIQUES:-Follow the following steps to add controls to the userform

  1. Select any userform control and draw it on the userform.
  2. Design the form as per your desire.
06:55

Properties can be viewed in an alphabetical order or category wise by clicking the "Alphabetic" or "Categorized" options in the Properties Window, after selecting the respective Userform or Control. To set/edit, click on that property and make the change in its right column. For help on a Userform or Control property, in the Properties Window select the right column of that property and press F1.

    TECHNIQUES:

  1. Change captions & settings of each control by using Properties window
  2. Tip: You can adjust properties of multiple controls by selecting them all and then making changes to their properties.
06:29

    Some controls (some included as above) can be set as the userform is running or before the form is displayed by setting properties with code.

    TECHNIQUES:

  1. Starting with the Close button which will simply close and end the user form.
  2. Lets add code (run time) to populate the ComboBox control (cboDept) which will dynamically create four fixed options to choose from.
01:22

    This tutorial will help you tu understand how to create a userform to select a range and highlight it.

    TECHNIQUES:

  1. Using RefEdit to select a range and highlight it.
  2. Using an Input Box to select a range and highlight it.

02:10

    This tutorial will help you tu understand how to create a userform to select a range and highlight it.

    TECHNIQUES:

  1. Using RefEdit to select a range and highlight it.
  2. Using an Input Box to select a range and highlight it.
05:28
  1. Scroll bar Scrolls through a range of values when you click the scroll arrows or when you drag the scroll box.
  2. Spin button Makes it easier to increase or decrease a value, such as a number increment, time, or date.
08:32

    Adding Images to the userform will help you extract the name of the file and creates its hyperlink in different columns according to its extension like jpg, gif etc.,

    TECHNIQUES: Creating a code that will replace the hyperlink with a image i.e instead of showing the full path of hyperlink , it should display any image and if I click on that image it should open the respective file.

01:45

    Create a List and apply the code that will help hyperlink the selected image name from the list box to the respective image.

    Adding Images to the userform will help you extract the name of the file and creates its hyperlink in different columns according to its extension like jpg, gif etc.,

    TECHNIQUES: Creating a code that will replace the hyperlink with a image i.e instead of showing the full path of hyperlink , it should display any image and if I click on that image it should open the respective file.

06:03

    Frames are also a passive control. Frames are used to improve the layout of the userform. You can use them around a group of controls that have something in common.

    Frames become more important to manage option buttons. If you have two sets of option buttons on a userform and you do not place them within a frame they all work together and you can choose only one. If you put each set within a frame you can choose one in each set.

    When you move a frame all its controls move with it.

    TECHNIQUES:-Use the following controls after adding a frame to the userform:-

  1. CheckBox: Allows the user to create a CheckBox where an item can only have a yes or no (true or false) answer.
  2. OptionButton: Allows you to display multiple options with a frame where only one can be selected at a time.
  3. ToggleButton: Like a CheckBox, but a button version
05:37

    Add a MultiPage control, by default it has 2 pages but you can add more if you want.

    TECHNIQUES:-

  1. Select the Multipages control from the toolbox in the userform.
  2. Assign the codes as required.
07:01

    The tab order determines the order in which the controls are activated when the user presses Tab or Shift+Tab keys. The alignment of controls provides several commands to help precisely align and space the controls in the dialog box.

    TECHNIQUES:-

  1. Use Format Tab in the VBE to change the alignment of controls in the userform.
  2. Use View Tab to change the Tab Order of the controls.
Section 15: Working on a Live project
03:38

    This tutorial will help you select the data from the list of topics and filter out the data using advanced filters in VBA.

    TECHNIQUES:

  1. Use data validation to create a list and
  2. Record the macro for Advanced Filters
  3. Create a code that will create a new sheet once the option is selected from the list in such a way that it will apply the same name as to what is selected and then copy/paste the data form the database after comparing the criteria
01:34

    This tutorial will help you compare the data based on the unique records that will act as a criteria and then compare the criteria to the database to filter the data and create multiple sheets and paste the data onto it.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Yoda Learning, Become 4X Faster | Join 32000+ Udemy Students

Yoda Learning is a team of 12+ industry professionals focused on developing “Project-based"learning solutions, which enable the learners explore real world situations and thus, make the learning process engaging and practical. Our Courses on Technology includes everything including Web Development, Mobile App Development, Data Analytics and Design.

The collective work-experience of 80+ yrs. spans across multiple industries like Manufacturing, IT, Sales, Finance, Marketing, Operations, HR and functional domains such as Office suites, Mobile applications, Digital Marketing, Database Technologies, Networking).

Ready to start learning?
Take This Course