028 3824 5819 traincert@tdt-tanduc.com
Tiếng ViệtEnglish

Excel 2010/2013: VBA & Macros

1 Unleash the Power of Excel with VBA

  • The Power of Excel
  • Barriers to Entry
  • Knowing Your Tools: The Developer Tab
  • Macro Security
  • Overview of Recording, Storing, and Running a Macro
  • Running a Macro
  • Using New File Types in Excel 2010/ 2013
  • Understanding the VB Editor
  • Understanding Shortcomings of the Macro Recorder

2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?

  • I Can’t Understand This Code
  • Understanding the Parts of VBA “Speech”
  • VBA Is Not Really Hard
  • Examining Recorded Macro Code: Using the VB Editor and Help
  • Using Debugging Tools to Figure Out Recorded Code
  • Object Browser: The Ultimate Reference
  • Seven Tips for Cleaning Up Recorded Code

3 Referring to Ranges

  • The Range Object
  • Syntax to Specify a Range
  • Named Ranges
  • Shortcut for Referencing Ranges
  • Referencing Ranges in Other Sheets
  • Referencing a Range Relative to Another Range
  • Use the Cells Property to Select a Range
  • Using the Cells Property in the Range Property
  • Use the Offset Property to Refer to a Range
  • Use the Resize Property to Change the Size of a Range
  • Use the Union Method to Join Multiple Ranges
  • Use the Intersect Method to Create a New Range from Overlapping Ranges
  • Use the ISEMPTY Function to Check Whether a Cell Is Empty
  • Use the Current Region Property to Select a Data Range
  • Use the Areas Collection to Return a Noncontiguous Range
  • Referencing Tables

4 User-Defined Functions

  • Creating User-Defined Functions
  • Sharing UDFs
  • Useful Custom Excel Functions

5 Looping and Flow Control

  • For…Next Loops
  • Do Loops
  • VBA Loop: For Each
  • Flow Control: Using If...Then...Else and Select Case

6 R1C1-Style Formulas

  • Referring to Cells: A1 Versus R1C1 References
  • Switching Excel to Display R1C1-Style References
  • The Miracle of Excel Formulas
  • Explanation of R1C1 Reference Style
  • Array Formulas Require R1C1 Formulas

7 What Is New in Excel 2010/ 2013 and What Has Changed

  • If It Has Changed in the Front End, It Has Changed in VBA
  • Learning the New Objects and Methods
  • Compatibility Mode

8 Create and Manipulate Names in VBA

  • Excel Names
  • Global Versus Local Names
  • Adding Names
  • Deleting Names
  • Adding Comments
  • Types of Names
  • Hiding Names
  • Checking for the Existence of a Name

9 Event Programming

  • Levels of Events
  • Using Events
  • Workbook Events
  • Workbook Level Sheet and Chart Events
  • Worksheet Events
  • Chart Sheet Events
  • Embedded Charts
  • Application-Level Events

10 Userforms: An Introduction

  • User Interaction Methods
  • Creating a Userform
  • Calling and Hiding a Userform
  • Programming the Userform
  • Programming Controls
  • Using Basic Form Controls
  • Using Labels, Text Boxes, and Command Buttons
  • Deciding Whether to Use List Boxes or Combo Boxes in Forms
  • Adding Option Buttons to a Userform
  • Adding Graphics to a Userform
  • Using a Spin Button on a Userform
  • Using the MultiPage Control to Combine Forms
  • Verifying Field Entry
  • Illegal Window Closing
  • Getting a Filename

11 Creating Charts

  • Charting in Excel 2010/ 2013
  • Referencing Charts and Chart Objects in VBA Code
  • Creating a Chart
  • Recording Commands from the Layout or Design Tabs
  • Specifying a Built-in Chart Type
  • Specifying a Template Chart Type
  • Changing a Chart’s Layout or Style
  • Using SetElement to Emulate Changes on the Layout Tab
  • Changing a Chart Title Using VBA
  • Emulating Changes on the Format Tab
  • Creating Advanced Charts
  • Exporting a Chart as a Graphic
  • Creating Pivot Charts

12 Data Mining with Advanced Filter

  • Replacing a Loop with AutoFilter
  • Advanced Filter Is Easier in VBA Than in Excel
  • Using Advanced Filter to Extract a Unique List of Values
  • Using Advanced Filter with Criteria Ranges
  • Using Filter in Place in Advanced Filter
  • The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only
  • Using Filter in Place with Unique Records Only

13 Using VBA to Create Pivot Tables

  • Introducing Pivot Tables
  • Understanding Versions
  • Creating a Vanilla Pivot Table in the Excel Interface
  • Building a Pivot Table in Excel VBA
  • Using Advanced Pivot Table Features
  • Filtering a Data Set
  • Using Other Pivot Table Features

14 Excel Power

  • File Operations
  • Combining and Separating Workbooks
  • Working with Cell Comments
  • Utilities to Wow Your Clients
  • Techniques for VBA Pros
  • Cool Applications

15 Data Visualizations and Conditional Formatting

  • Introduction to Data Visualizations
  • VBA Methods and Properties for Data Visualizations
  • Adding Data Bars to a Range
  • Adding Color Scales to a Range
  • Adding Icon Sets to a Range
  • Using Visualization Tricks
  • Using Other Conditional Formatting Methods

16 Reading from and Writing to the Web

  • Getting Data from the Web
  • Using Application.OnTime to Periodically Analyze Data
  • Publishing Data to a Web Page

17 Dashboarding with Sparklines in Excel 2010/ 2013

  • Creating Sparklines
  • Scaling the Sparklines
  • Formatting Sparklines
  • Creating a Dashboard

18 Automating Word

  • Early Binding
  • Late Binding
  • Creating and Referencing Objects
  • Using Constant Values
  • Using the Watch Window to Retrieve the Real Value of a Constant
  • Understanding Word’s Objects
  • Controlling Form Fields in Word

19 Arrays

  • Declare an Array
  • Fill an Array
  • Empty an Array
  • Arrays Make It Easier to Manipulate Data, but Is That All?
  • Dynamic Arrays
  • Passing an Array

20 Text File Processing

  • Importing from Text Files
  • Writing Text Files

21 Using Access as a Back End to Enhance Multiuser Access to Data

  • ADO Versus DAO
  • The Tools of ADO Adding a Record to the Database
  • Retrieving Records from the Database
  • Updating an Existing Record
  • Deleting Records via ADO
  • Summarizing Records via ADO
  • Other Utilities via ADO
  • SQL Server Examples

22 Creating Classes, Records, and Collections

  • Inserting a Class Module
  • Trapping Application and Embedded Chart Events
  • Creating a Custom Object
  • Using a Custom Object
  • Using Property Let and Property Get to Control How Users Utilize Custom Objects
  • Collections
  • User-Defined Types

23 Advanced Userform Techniques

  • Using the UserForm Toolbar in the Design of Controls on Userforms
  • More Userform Controls
  • Controls and Collections Modeless Userforms
  • Using Hyperlinks in Userforms
  • Adding Controls at Runtime
  • Adding Help to the Userform
  • Transparent Forms

24 Windows API

  • What Is the Windows API?
  • Understanding an API Declaration
  • Using an API Declaration
  • API Examples
  • Finding More API Declarations

25 Handling Errors

  • What Happens When an Error Occurs?
  • Basic Error Handling with the On Error GoTo Syntax
  • Generic Error Handlers
  • Train Your Clients
  • Errors While Developing Versus Errors Months Later
  • The Ills of Protecting Code
  • More Problems with Passwords
  • Errors Caused by Different Versions

26 Customizing the Ribbon to Run Macros

  • Out with the Old, In with the New
  • Creating the Tab and Group
  • Adding a Control to Your Ribbon
  • Accessing the File Structure
  • Understanding the RELS File
  • Renaming the Excel File and Opening the Workbook
  • Using Images on Buttons
  • Troubleshooting Error Messages
  • Other Ways to Run a Macro

27 Creating Add-Ins

  • Characteristics of Standard Add-Ins
  • Converting an Excel Workbook to an Add-In
  • Using a Hidden Workbook as an Alternative to an Add-In


Thời lượng: 05 ngày
Giảng viên: Microsoft Certified Trainer, MOS Master
Chứng nhận hoàn thành khóa học của Microsoft

KH 2
KH 3
KH 5