Excel 在实际工作AA/FA的威力无穷，但是Excel VBA会使你的工作越来越简单

Nicole会计培训送福利 8小时的课程只需$380同时送免费重听，真正学懂弄通，能自己独立制作VBA

VBA Course Outline

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

Part 2 - Dynamic Dashboard Design

1.       Design Dynamic Dashboard Using VBA Check Box

2.       Design Dynamic Dashboard Using VBA Radio Button

3.       Design Dynamic Dashboard Using VBA Spin Button

4.       Design Dynamic Dashboard Using VBA Combo Box

5.       Design Dynamic Dashboard Using VBA Scroll Bar

6.       Design Dynamic Dashboard Using VBA ActiveX Control

7.       Design Dynamic Dashboard Using Pivot Table Slicer

8.       Design Dynamic Dashboard Using Named Range and Picture

9.       Design Dynamic Dashboard Using Named Range and Picture Link (or Camera tools)

10.   Design Dynamic Dashboard Using For..Next Loop and Image Object