Excel VBA (Macros) with Projects
3.5 (15 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.
588 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA (Macros) with Projects to your Wishlist.

Add to Wishlist

Excel VBA (Macros) with Projects

Learn to use VBA to enhance Excel's functionality to increase efficiency and productivity
3.5 (15 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.
588 students enrolled
Created by Yoda Learning
Last updated 9/2014
English
Current price: $10 Original price: $50 Discount: 80% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 8 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I Learn?
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
View Curriculum
Requirements
  • Basic Excel knowledge
  • No Prior programming knowledge required
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
Who is the target audience?
  • MIS Analysis
  • Operations
  • Analytics Process automation
  • Database Administrators
  • Financial Analyst
  • Consulting
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 105 Lectures Collapse All 105 Lectures 08:07:27
+
The Macro Recorder
9 Lectures 41:55

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+

Preview 03:34

    Understanding what are macros

    Why are macros used?

Preview 03:09

    How are macros created?

    TECHNIQUES:RECORDING AND WRITING USING VBA

    How to save a macro?

    TECHNIQUES: .XLSM FILE EXTENSION

Preview 06:15

      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
Recording a Macro
04: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

Edit the recorded Macro and Shortcut key
02:46

      How to save a recorded macro?

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

Saving a macro file
05:03

Learn how to apply Macro Security settings.

TECHNIQUES: USE OF TRUST CENTER TO APPLY MACRO SECURITY

Macro Security settings
03:38

    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
Running Macros
07:10

    Defining a Storage Location for Macro

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

Storage Location
05:34
+
VBA fundamentals
5 Lectures 39:30

Understanding the In and Out of programming

What is Programming
05:41

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

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

Enable Developer Tab
01:10

    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
VBE
08:29

    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
Writing a small VBA code
08:32
+
Building Procedures
5 Lectures 16:03

Learn to create sub procedure using Sub Macroname()

How to create a sub procedure
04:39

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

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

Adding Comments
03:28

Learn to use private and public procedure

Types of procedures
02:04

Understanding how to call sub procedure

Calling Procedure
02:14

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

Passing Arguments to the procedure
03:38
+
Working With Excel VBA Functions
4 Lectures 26:10

Learn how to create functions in VBA

How to create functions
03:28

·Understand how to use conditions in Functions

How to use conditions in functions
02:17

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

Using conditions If...Then....Else
17:19

    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
User Defined Functions
03:06
+
Basics of VBA coding
5 Lectures 41:07

    Understand what variables are and how to assign them.

    TECHNIQUES: USING VARIABLE NAME = VALUE

Assigning a variable
02:08

    Scope of a variable

    TECHNIQUES:

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

    Life of a Variable

    TECHNIQUES:

  1. Static
  2. Dynamic
Different ways of assigning variables
15:57

    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
DataTypes
06:24

Understanding what constants are and how to use them

Constants
02:33

    Understanding what operators are and types of operators

    TECHNIQUES:

  1. Arithmetic operators
  2. Boolean operators
  3. Relational operators
Operators
14:05
+
Working with the dialog boxes
3 Lectures 09:14

How to create and use MsgBox in VBA

MsgBox
01:09

    Understanding how to add arguments to a MsgBox in VBA

    TECHNIQUES:

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

Adding arguments to the msg box
01:48

How to create and use Input Box in VBA.

Input Box usage
06:17
+
Working with the objects
15 Lectures 47:55
  1. Understanding Application Object
  2. ·Understanding Workbook Object
  3. Understanding Worksheet Object
  4. Understanding Range Object
  5. Understanding Cell Object
Object Reference Model
07:53

    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.
Range
05:24

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

Cell objects
03:27

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
Range Properties and Methods
03:22

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

Range Count Property
01:36

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

Learn how to use Selection object.

Range Select
00:47

    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
Selection of Rows and Columns
04:19

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

Difference between Dim and Set
01:40

Learn how to use Worksheet object

Understanding Worksheet Object
01:58

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

  1. Worksheets property
  2. ActiveSheet property
Worksheet properties and methods
03:10

·Learn how to use Workbook object

Understanding workbook object
02:06

    ou can delete workbooks by using the KILL command.

    TECHNIQUES: Kill "C:\WorkbookName.xlsx"

How to Delete a workbook
00:53

    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

Application Objects
02:45

    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.
Object Browser
05:21
+
Manipulating Excel Objects
3 Lectures 17:00
  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.
Offset
10:18

How to use offset function in excel

Application of Offset
04:23

How to use Range.Offset property

Offset property in VBA
02:19
+
Loops
6 Lectures 36:39

    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
Loops
02:11

Learn and understand For-Next Loop

For-next loop
12:07

Learn and understand Do-While Loop

Do while-loop
05:35

Learn and understand Do Loop

Do-loop while
01:56

Assignment on the Loops

Assignment
10:15

Learn and understand For each-Next loop

For Each…Next
04:35
+
Events - Triggering code with Events
6 Lectures 19:38

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

Creating Events(Open Event)
05:18

·Learn and understand how to create Workbook Close Event

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

Close Events
01:18

Learn and understand how to create workbook activate and deactivate events

Workbook Activate Sheet Events
02:04

Additional chapter on Activate-Deactivate Events

Workbook Activate Events
01:36

Learn and understand how to create worksheets activate and deactivate

Worksheet ActivateDec Events
01:46

    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).
Application Events
07:36
6 More Sections
About the Instructor
Yoda Learning
4.2 Average rating
1,099 Reviews
31,755 Students
42 Courses
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).