Certificate course in Excel Marcos and VBA Programming

The Certificate Course in Excel Macros and VBA Programming is a comprehensive program designed to provide participants with the necessary skills to automate tasks and unleash the full potential of Microsoft Excel. This hands-on course explores the world of Excel Macros and Visual Basic for Applications (VBA) programming, equipping participants with the expertise to streamline processes, increase productivity, and become proficient Excel automation specialists.

Throughout the course, students will gain a solid foundation in VBA programming by learning the fundamental concepts and techniques necessary to create powerful macros. The course emphasizes hands-on learning, combining theoretical instruction with practical examples, exercises, and real-world scenarios.

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.

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

SCHEDULES
 
CVB4121 - 廣東話 27 Dec enrol
RELATING COURSES
  Python Programming x ChatGPT/GPT
  Beginning Data Science with Python
  Python for Data Analysis
  Stock Trading Analysis with Python
  Excel VBA X ChatGPT/GPT
  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