课程介绍 （8小时）
This Microsoft Excel VBA training course is designed for someone who wishes to peruse a career as management accountant, financial analyst or business analyst & data analyst, users who want to learn how to use the inbuilt programming language in Microsoft Excel to enhance their worksheets and automate processes.
You should have intermediate Excel Skills; know how to use Excel formulas such as Vlookup, Sumifs, PivotTable and basic charting etc. Programming language skills is not compulsory in the training.
After you have learnt this course, you should be able to write VBA macros to build applications and automate your daily task so you can work much faster, you will know how to create dynamic chart to visualise the data using different VBA controls and build complicated dashboard for different purpose such as KPIs analysis, Sales Performance, Budget Analysis etc. The possibilities are endless.
价格
$600
讲课大纲
Introductions
• Introducing Visual Basic for Applications
• Enable the Developer Tab in the Ribbon
• Enable macros in Excel workbook and understand macro security
• Saving a Macro-Enabled Workbook
• How to run a Macro in different ways
• Editing a Macro in the Visual Basic code window
• Closing the Visual Basic Editor
Work with Expressions & Variables
• Understanding Expressions and Statements
• Declaring Variables
• Understanding Data Types
• Working with Variable Scope
• Using Message Boxes
• Using Input Boxes
• Declaring and Using Object Variables
Understand Procedures and Functions
• Understanding Modules
• Creating a Standard Module
• Understanding sub Procedures
• Public sub vs. private sub
• Creating a Sub Procedure
• Calling Procedures
• Creating a Function Procedure
• Naming Procedures
Understanding Objects
• Understanding workbook objects
• Extract workbook name, workbook full path
• Workbook method - open, save, close
• Understanding worksheet objects
• Worksheet name, worksheets count, rows & columns count
• Worksheet method – activate, add, delete, copy
• Range object – format, calculate, select, copy, paste
• Working with object property window
Understanding Event
• Workbook Open Event
• Worksheet Activate Event
• Worksheet Selection Change Event
• VBA Controls Event
• Application Event
Program Execution
• Using the If...End If Structures
• Using the Select Case...End Select Structure
• Using the Do...Loop Structure
• Using the For...To...Next Structure
• Using the For Each...Next Structure
Working with Forms and Controls
• Understanding UserForms, Setting Control Properties in the Properties Window
• Working with the Check Box Control
• Working with the Text Box Control
• Working with the Command Button Control
• Working with the Combo Box Control
• Working with Option Button Controls
• Adding Code to Controls and design report
Working with the PivotTable Object
• Understanding PivotTables
• Creating a PivotTable Using Worksheet Data
• Working with the PivotTable Objects
• Working with the PivotFields Collection
• PivotTable Related Event
Working with Images and Charts
• Understanding Image and Charts
• Working with Chart Properties windows
• Pictures copy, paste, delete
• Show image and hide image
• Show Charts and hide charts
VBA Tips:
• Assign Macros to Quick Access Toolbar
• Use shortcuts to run macro, open VBA editor window and return to Excel window
• Always activate target worksheet when workbook opens
• Permanently hide worksheets
• Make your macros work anywhere - Enabling Relative References
• How to customise ActiveX component
By the end of this course participants should be able to:
• Work with the VBA editor
• Understand VBA concepts and features
• Enable or disable VBA macros
• Create procedures and sub routines
• Record macros using macro recording and customise the VBA code
• Assign macros to object and shortcut to start macros in different ways
• Work with variables and Excel objects
• Work with different events (workbook, worksheet, controls, application)
• Use named range in VBA coding (highly recommended)
• Create user defined functions (such as user defined lookup functions to replace Vlookup)
• Create custom forms and design report
• Work with time & date, manipulate text string
• Handle errors and resume next
