Excel VBA Training Course

Excel VBA or Excel Macros Training Course

Excel VBA / Macros 是一款功能強大的工具,它使Excel形成了獨立的編程環境。使用VBA和巨集,可以把手工步驟自動化,VBA也允許創建窗體來獲得用戶輸入的信息。課程內容深入介紹 Macro 程式語言,幫助你建立、分析、並共享試算表。常用於Data分析, Budget Planning及財務匯報。

Excel VBA or Excel Macros Training Course

What you'll do : Exercises Explained

Exercise : Comparing data from two tables

Occasionally, users may want to compare data from two tables and find out cells with different values. The act can tell whether data has been modified or not.

In this exercise, student will learn how to compare cells from two tables and colourize those cells with their contents are not identical.

Exercise: Table Formatting and Calculation

In this exercise, students will write a VBA to automate the repetitive work, calculating columns total and formatting the table, which are the tasks commonly found in preparing reports.

Through the exercise, students can practise the programming techniques by modularizing a big task into small tasks, write codes for the small ones and integrate them afterwards for a more complex projects.



Exercise: Data Extraction

It is fairly common to find and extract data. For example, you may want to separate sales records from a master file and distribute them to each salesperson.

In this exercise, students will learn how to find and extract rows of data and place them on same worksheet or on different worksheets. The provision of additional features (e.g extraction with or without format, extraction on separate worksheet ) not only can strengthen students' programming skills but also can demonstrate the power of VBA in the context of tasks automation.



What you’ll learn

Recording macros and using buttons

  • Absolute and relative recording
  • Where macros can be stored
  • 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
  • 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
  • The Name 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
  • Jump to a specific location in a Macro

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

SCHEDULES
 
VBA7092 - 廣東話 30 Sep enrol
 
VBA7113 - 廣東話 04 Nov enrol
 
VBA7111 - 廣東話 07 Nov enrol
 
VBA7112 - 廣東話 21 Nov enrol
 
VBA7121 - 廣東話 02 Dec enrol
RELATING COURSES
  Access VBA Programming
  Advanced Excel and Excel VBA
  Excel Dashboards and Reports
  Excel VBA - Advanced
  Financial Accounting with Excel
  Mastering Excel PivotTables and PivotCharts