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 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

SCHEDULES
 
VBA7113 - 廣東話 04 Nov enrol
 
VBA7112 - 廣東話 21 Nov enrol
 
VBA7121 - 廣東話 02 Dec enrol
 
VBA7122 - 廣東話 27 Dec enrol
 
VBA8011 - 廣東話 01 Jan 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

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