Certificate in Excel Automation with Macro and VBA

課程內容
UNDERSTANDING VBA AND MACRO

• Enable the Developer Option
• Record and view a Macro
• Code Analysis
• Understanding Relative Reference
• Saving Macros
• Adding Form Controls
• Trusting Macros
• Introduction to Visual Basic Editor (VBE)
• Creating a Macro Manually

VBA OBJECT MODEL, VARIABLES AND COLLECTIONS

• Recognizing object hierarchy
• Collections and its properties
• Understanding "Active" object
• Identifying a Range and addressing cells using R1C1
• Creating Variables
• Reserved Keywords
• Defining data types
• Using Option Explicit
• Managing Strings
• Examples of using String and Date
• Producing Arrays and Dynamic Arrays
• Multi-dimensional Array
• Representing Objects
• PROGRAMMING TECHNIQUES
• Arithmetic and Comparison Operators
• Logical Operations
• Understanding precedence
• Choosing a branch
• Example of Using if-then conditional test
• Branching alternatives
• Selecting branches
• Example of Selecting Cases
• Performing loops
• For Loop Syntax
• Example of Using For Loop
• Do-While Loop
• Example of Using While Loop
• Iterating for each
• Example of Using For Each Loop
• Using With

PROCEDURE, FUNCTIONS AND EVENTS

• Sub Procedure
• Modifying scope
• Passing arguments
• Handling Errors
• Example of Error Handling
• Defining Functions
• Example of Functions
• Recognizing eventsg
• Event Handler’s Locations
• Opening Workbook Events
• Closing workbook events
• Acquiring input
• Showing messages
• Customizing the Message Box
• Example of using Message Box

BUILDING FORMS FOR DATA COLLECTION

• Inserting a User Form
• Adding Controls
• Different Types of Controls
• Adjusting properties
• Displaying forms
• Handling form events

INTERACTING WITH EXTERNAL ENVIRONMENTS

• Sending Emails from Excel With VBA
• Using the FileSytemObject to manage files