Computer Academy


Enquire Now

Course Information

  • 14 Feb 2022 (Mon) - 21 Feb 2022 (Mon) 10:00 AM - 5:00 PM
  • 10 Mar 2022 (Thu) - 31 Mar 2022 (Thu) 7:00 PM - 5:00 PM
Registration period
6 Dec 2021 (Mon) - 3 Mar 2022 (Thu)
HKD 3,580
Course Level
Study Mode
12 Hour(s)
Room 603 Dominion Centre, 43-59 Queen's Road East, Wanchai

Course Overview

This Excel VBA training course , VBA is commonly known as Macros, will teach users how to automate the Excel’s boring and repetitive work without human intervention. The training course will cover a wide range of concepts and techniques which are applicable to most daily spreadsheet task. Topics include the basic concepts of VBA programming, language syntax, controlling program flow, working with Excel’s objects and properties, looping over collections, creating custom functions, designing userforms, working with PivotTables and more.

The easy to follow exercises and small class size teaching approach can ensure the best learning result. It is an ideal course for people working in the accounting, financial analysis and data processing areas. Students can choose the daytime, evening and weekends classes to suit their learning needs.


After finishing the course, you will be able to write your own VBA to streamline the daily tasks which include the following :
- Merging tables from different workbooks or worksheets into one single table
- Generating PivotTables unattended with the designated rows and columns and filter the table
- Comparing and highlighting data dissimilarities of two tables
- Performing data lookup and filling
- Data cleansing and analysis
- Generating reports
- Formatting reports and tables
- Designing data entry forms
- Validating data through data entry forms

What you will get after the class

- Hard copy of codes used in the class
- Code and data files
- Ready to use VBA snippets
- Video account for lessons revision

What You’ll Learn

What you'll learn :

Recording macros and using buttons

  • Absolute and relative recording
  • Macros security settings
  • Assigning macros to buttons

VBA Programming Basics

  • Getting help in VBA
  • Create a subroutine
  • Create a function
  • Declare a variable
  • Create a constant
  • Comment your code

The VBA code editor

  • Insert modules
  • Using Project Explorer
  • Using Immediate Window
  • Using Locals Window

Range's properties and methods

  • The Cells property
  • The Offset property
  • The entirecolumn and entirerow
  • The Value property
  • The Text property
  • The Count property
  • The Column and Row properties
  • The Address property
  • The Font property
  • The Border
  • The interior property
  • The Formula and FormulaR1C1

Other useful object methods

  • The select method
  • The copy and paste methods
  • The clear method
  • The delete method
  • The workbook methods
  • The worksheet methods

Messages and variables

  • Input boxes and message boxes
  • Assign Values to Variables
  • Declaring variables

Working with Excel Object Model

  • An introduction to the Excel object model
  • Using object browser
  • Create an object variable
  • Objects and collections
  • Methods and properties
  • Display a built-in dialog box

Loops and conditions

  • Simple IF conditions
  • Multiple IF and SELECT … CASE
  • While Loop
  • Execute tasks a specific number of times
  • Using the For Each Next Loop

Looping over collections

  • Object variables
  • Looping over collections

Using built-in functions and statements

  • Using Excel worksheet functions
  • Using the MsgBox function
  • Using the InputBox function
  • Create and using custom functions

Working with UserForms

  • Creating and calling a userForm
  • Better users experiences with form controls
  • Using Label, text box, button, checkbox and optionbox
  • Data validation

Automating Excel PivotTables using VBA

  • Creating an Excel Pivot Table report using VBA
  • Reference Pivot Fields and Pivot Items using VBA
  • Filtering data using VBA

Start FollowingSee all

We use cookies to enhance your experience on our website. Please read and confirm your agreement to our Privacy Policy and Terms and Conditions before continue to browse our website.

Read and Agreed