VBA Macro Programming With Excel 2010

Excel VBA Course Duration: This is a 3 full day Excel 2010 VBA Course. Course starts at 9am and runs until 5pm, with a VBA Training in Excel 2010 at Intellisoft Singaporeone hour break for lunch, and two short snack breaks.

Minimum Requirements: You must have good Excel skills and understand key concepts of spreadsheets, or equivalent. It is not necessary to have programming knowledge. We cover VBA Macro programming concepts from the very basic level.


 Excel 2010 VBA Macro Programming: Course Objectives

Learn Excel VBA 2010 easily with our step by step, practical, hands on workshop in Singapore. After attending the VBA Macro Training course using Microsoft Excel 2010, you will be able to:

  • Record and Run Macros Easily
  • Write VBA Code Manually using Microsoft Excel 2010
  • Perform Loops, Controls and Error Handling in Excel Macros
  • Create User forms and Sheet Controls in Excel 2010
  • Use Events to trigger specific VBA code.
  • Write Useful Applications That Boost Productivity and Reduce Time on Routine Activities.

Go To Top

3 Day VBA Macro Training (Excel 2010): Detailed Outline

Module 1 Unleash the Power of Excel 2010 with VBA Programming

  • The Power of Excel 2010
  • Knowing Your Tools: The Developer Tab
  • Macro Security
  • Adding a Trusted Location
  • Overview of Recording, Storing, and Running a Macro
  • Understanding the VB Editor

Module 2 Understanding the basics of Macro Programming

  • Examining Recorded Macro Code: Using the VB Editor and Help
  • Optional Parameters
  • Defined Constants
  • Properties Can Return Objects
  • Using Debugging Tools to Figure Out Recorded Code

Module 3 Referring to Ranges with Excel 2010 VBA

  • The Range Object
  • Named Ranges
  • Use the Cells Property to Select a Range
  • Using the Cells Property in the Range Property
  • Use the Offset Property to Refer to a Range
  • Use the Resize Property to Change the Size of a Range
  • Use the CurrentRegion Property to Select a Data Range

Module 4 User-Defined Functions in Excel 2010 VBA

  • Creating User-Defined Functions
  • Sharing UDFs
  • Useful Custom Excel Functions

Module 5 Looping and Flow Control in Excel VBA

  • Using Variables in the For Statement
  • Variations on the For…Next Loop
  • Exiting a Loop Early After a Condition Is Met
  • Nesting One Loop Inside Another Loop
  • VBA Loop: For Each
  • VBA Loop : Do Loops
  • Flow Control: Using If…Then…Else
  • Flow Control: Using Select Case

Module 6 Event Programming in Excel 2010

  • Levels of Events
  • Using Events
  • Event Parameters
  • Enabling Events
  • Workbook Events
  • Worksheet Events
Module 7  Introduction to UserForms in Excel 2010 VBA

  • User Interaction Methods
  • Input Boxes
  • Message Boxes
  • Programming the Userform
  • Userform Events
  • Programming Controls
  • Using Basic Form Controls

Module 8 Creating Charts with Excel 2010 VBA

  • Charting in Excel 2010
  • Referencing Charts and Chart Objects in VBA Code
  • Creating and modifying the Chart.

Module 9 Data Mining with Advanced Filtering

  • Replacing a Loop with AutoFilter
  • Using New AutoFilter Techniques
  • Selecting Visible Cells Only
  • Advanced Filter in VBA

Module 10 Using VBA to Create Pivot Tables in Excel 2010

  • Introducing Pivot Tables
  • Understanding Versions
  • Building a Pivot Table in Excel VBA

Optional Topics

  • Module 11 Excel Power
  • Combining and Separating Workbooks
  • Separate Worksheets into Workbooks
  • Combine Workbooks
  • Filter and Copy Data to Separate Worksheets

Module 12 Handling Errors

  • What Happens When an Error Occurs?
  • Basic Error Handling with the On Error GoTo Syntax
  • Generic Error Handlers
  • Handling Errors by Choosing to Ignore Them
  • Suppressing Excel Warnings


Go To Top

Module 1: Realizing the potential of Excel using VBA

  • Recognizing the Power of Excel
  • Identifying workbooks that support macros
  • Showing the Developer Tab
  • Understanding Macro Security
  • Recording and Running a Macro

Module 2: Understanding the Basics

  • Exploring the VB Editor
  • Examining Recorded Macro Code
  • Introducing the Selection Object
  • Understanding Properties, Methods and Parameters
  • Using the Line Continuation Character
  • Using the With… End With statement
  • Writing Comments

Module 3: Working with Variables and Constants

  • Understanding the Scope of Variables
  • Identifying the different Data Types
  • Working with Variables
  • Exploring Object Variables
  • Declaring and using Constants

Module 4: User Interaction Techniques: Part 1

  • Using the InputBox
  • Using the Msgbox

Module 5: Overview of Objects

  • Introducing the Application Object
  • Exploring Workbooks and Workbook Object
  • Exploring Worksheets and Worksheets Object

Module 6: Referring to Ranges

  • Introducing the Range Object
  • Referencing a range using the CurrentRegion Property
  • Accessing a range using the UsedRange Property
  • Finding the First Cell with data
  • Referencing the Last Row and Column in the data range
  • Using the Cells Property
  • Utilizing the Offset Property while working with Range
  • Employing the Resize Property to Change the Size of a Range

Module 7: Control Structures

  • Using Loop Constructs: For Loop Statement
  • Using Loop Constructs: For…Next Loop
  • Using Loop Constructs: Do While…Loop
  • Using Loop Constructs: Do Until…Loop
  • Exiting a Loop Early
  • Nesting Loops
  • Working with Conditional Constructs: If…Then…Else
  • Working with Conditional Constructs: Select Case

Module 8: User-Defined Functions

  • Exploring Commonly Used VBA Functions
  • Creating User-Defined Functions
  • Executing User-Defined Functions
  • Sharing User-Defined Functions

Module 9: Event Programming

  • Creating a Custom Button to execute code
  • Understanding Event Parameters
  • Working with Worksheet Events
  • Working with Workbook Events

Module 10: Using VBA to Create Pivot Tables

  • Introducing Pivot Tables
  • Building and Editing Pivot Tables using VBA
  • Auto Refreshing Pivot Tables using VBA

Module 11: User Interaction Techniques: Part 2

  • Creating UserForms
  • Understanding and Using ActiveX Controls
  • Programming the ActiveX Controls

Module 12: Creating Charts

  • Charting in Excel
  • Referencing Charts Objects using VBA
  • Creating and Modifying the Chart

Module 13: Handling Errors

  • Identifying the Different Types of Errors
  • Debugging Tools: Stepping through Code
  • Debugging Tools: Breakpoint
  • Debugging Tools: Immediate Window
  • Debugging Tools: Watch Window
  • Handling Errors by Choosing to Ignore Them
  • Employing Basic Error Handling with the On Error GoTo Syntax

Module 14: Useful Macros

  • Selecting Special Cells
  • Using Autofilter instead of Loops
  • Combining Workbooks
  • Separating data into different Worksheets

Go To Top

Microsoft Training at Intellisoft

Claim SDF Grant

Companies sponsoring their staff for training can Claim SDF Funding, subject to SSG Eligibility Criteria.

Applicable for Company Sponsored Singaporeans & PRs.

Learn By Doing

You learn best when you Do It Yourself.

We teach you, step by step, how you can learn new skills, build your knowledge and enhance your career prospects quickly & easily, with Practical Tips & Tricks!

SkillsFuture Ready

Singaporeans can use $500 SkillsFuture Credits for this training to offset the course fees.

Contact us for advise on how to go about claiming your SkillsFuture.

Get Started Today!

How To Register

This course is scheduled to run at our VBA Training center at Fortune Centre in Singapore. View our Training Calendar for available dates.

Simply Register Online, and book your seat today. Or send an online enquiry if you have any questions. You can call us at +65 6250-3575.

SDF funding is available to ALL companies registered in SingaporeThe funding is NOT AVAILABLE TO INDIVIDUALS – only to Company Sponsored Singaporeans/PRs.

Custom In-House / Corporate Training
The course can also be run on specific dates that suits you.

Call our Corporate Training Hotline at +65 6250-3575 if you have a group of 5 or more participants.

You can email to training@intellisoft.com.sg for Corporate Training Rates.

Some of the companies that experienced our trainings

Get Creative With Illustrator
Accelerate Your Career

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!