Excel VBA Training Course

Excel VBA or Excel Macros Training Course

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

目標

完成課程後,您將可以通過編寫VBA程序來自動化並簡化Excel日常任務


- 將不同工作簿中的表複製並合併到一個表中
- 比較來自兩個不同表格的數據並顯示不同之處
- 使用預定義的過濾器,數據和格式生成pivotTable報告
- 自動格式化Excel表格
- 使用其他表格的數據自動填充表格
- 創建客戶用戶表單以實現更好的用戶體驗
- 通過用戶表單控制,驗證和修復數據
- 執行冗長複雜的計算
- 編寫Excel公式

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
 
VBA7116 - 廣東話 24 Nov enrol
 
VBA7123 - 廣東話 22 Dec enrol
 
VBA8011 - 廣東話 01 Jan enrol
 
VBA8012 - 廣東話 13 Jan enrol
 
VBA8022 - 廣東話 02 Feb 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