Certificate course in Excel Marcos and VBA Programming

The Certificate course in Excel Macros and VBA Programming gives you a solid foundation to automate and analyse data in Excel. You will learn the essential and practical features via different real-world examples. After the course, you can start different Excel tasks. Whether they are combining tables from various sources, generating reports, scheduling daily tasks or preparing pivot tables. All those projects are taught in the class and with more.

It is an ideal course for anyone who is interested in Excel Macros and VBA programming.

What you’ll learn

Recording macros and using buttons

  • Absolute and relative recording
  • Storing and retrieving macros
  • Assigning macros to buttons

VBA Programming Basics

  • Getting help in VBA
  • Creating Subroutines and Functions
  • Data type and variable
  • Comment your codes

The VBA code editor

  • Insert modules
  • Using Project Explorer
  • Using Immediate Window
  • Using Locals Window
  • Using Watch Window
  • Using Step Into
  • Setting Breakpoint

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

CVB3122 - 廣東話 14 Dec enrol
CVB4012 - 廣東話 16 Jan enrol
CVB4011 - 廣東話 25 Jan enrol
CVB4021 - 廣東話 26 Feb enrol
  Python Programming
  Beginning Data Science with Python
  Python for Data Analysis
  Stock Trading Analysis with Python
  Excel VBA
  SQL for Data Analysis
  Stock Trading Analysis with Python

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 For Each….. Next Loop

Looping over collections

  • Object variables
  • Looping over collections

Automating Excel PivotTables using VBA

  • Creating an Excel Pivot Table report using VBA
  • Adding Fields to the Pivot Table
  • Reference Pivot Fields and Pivot Items using VBA
  • Filtering data using VBA
  • Removing fields from the pivot table
  • Updating and refreshing the pivot table
  • Changing Data source of the pivot table

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 Arrays

  • Declaring arrays
  • Declaring multidimensional arrays
  • Declaring dynamic arrays

Working with UserForm

  • Creating and calling a userForm
  • Better users experiences with form controls
  • Data validation
  • Hiding a userForm
  • Text box, button, label, optionbox, list box, checkbox, combo box
  • Adding Event-handler procedure
  • Introducing userForm control types
  • Verifying Field Entry
  • Collect userForm Controls input
  • Filling userForm Controls with user data

Events Programming

  • Disable/Enable Events
  • Events for Worksheet
  • Events for Workbook
  • Events for userForm Controls
  • Events for application
  • Events Not Associated with Objects
  • onTime Event / KeyPress Event

Reporting and Charting

  • Consolidating data from different files
  • Data cleansing and transformation
  • Sorting and filtering data
  • Automate the SUM and Vlookup functions
  • Formatting the Headers and Footers
  • Visualize the categorical data using charts
  • Automate the charts' formats

Error Handling Techniques

  • On Error statement
  • Resuming after an error
  • Identifying specific errors