Home > Courses Certifications > Programming > Excel 2007 VBA synopsis

Excel 2007 VBA

Duration: 5 days

Chapter 1: Primer in Excel VBA

• Using the Macro Recorder
• User-Defined Functions
• The Excel Object Model
• The VBA Language

Chapter 2: The Application Object

• Globals
• The Active Properties
• Display Alerts
• Screen Updating
• Evaluate
• InputBox
• StatusBar
• SendKeys
• OnTime
• OnKey
• Worksheet Functions
• Caller

Chapter 3: Workbooks and Worksheets

• The Workbooks Collection
• The Sheets Collection
• The Window Object

Chapter 4: Using Ranges

• Active and Select
• Range Property
• Offset Property
• Resize Property
• SpecialCells Method
• CurrentRegion Property
• End Property
• Summing a Range
• Columns and Rows Properties
• Union and Intersect Methods
• Empty Cells
• Transferring Values between Arrays and Ranges

Chapter 5: Using Names

• Naming Ranges
• Special Names
• Storing Values in Names
• Storing Arrays
• Hiding Names
• Working with Named Ranges
• Searching for a Name

Chapter 6: Data List

• Structuring the Data
• Sorting a Range
• Creating a Table
• Sorting a Table
• AutoFilter
• Advanced Filter
• Data Form

Chapter 7: Pivot Tables

• Creating a PivotTable Report
• PivotFields
• PivotItems
• PivotCharts
• External Data Sources

Chapter 8: Charts

• Chart Sheets
• Embedded Charts
• Editing Data Series
• Defining Chart Series with Arrays
• Converting Chart Series with Arrays
• Determining the Range Used in a Chart
• Chart Labels

Chapter 9: Event Procedures

• Worksheet Events
• Chart Events
• Workbook Events
• Headers and Footers

Chapter 10: Adding Controls

• Form and AciveX Controls
• ActiveX Controls
• Forms Controls
• Dynamic ActiveX Controls
• Controls on Charts

Chapter 11: Text Files and File Dialog

• Opening Text Files
• Writing to Text Files
• Reading to Text Files
• Writing to Text Files Using Print

Chapter 12: Working with XML and the Open XML File Formats

• The Basic of Using XML Data in Excel
• Using VBA to Program XML Processes
• Using VBA to program Open XML Files

Chapter 13: UserForms

• Displaying a UserForm
• Creating a UserForm
• Directly Accessing Controls in UserForms
• Stopping the Close Button
• Maintaining a Data List
• Modeless UserForms
• Variables UserForm Name

Chapter 14: RibbonX

• Overview
• Prerequisites
• Adding the Customizations
• XML Structures
• RibbonX and VBA
• Control Types
• Control Attribtes
• Control Callbacks
• Managing Control Images
• Other RibbonX Elements, Attributes, and Callbacks
• Dynamic Controls
• CommandBar Extensions for the Ribbon
• RibbonX Limitations

Chapter 15: Command Bars

• Toolbars, Menu Bars, and Popups
• Excel’s Built-in Command Bars
• Controls at All Levels
• Creating New Menus
• Passing Parameter Values
• Deleting a Menu
• Creating a Toolbar
• Popup Menus
• Showing Popup Command Bars

Chapter 16: Class Modules

• Creating Your Own Objects
• Property Procedures
• Creating Collections
• Encapsulation
• Trapping Application Events
• Embedded Chart Events
• A Collection of UserForm Controls
• Referencing Classes Across Projects

Chapter 17: Add-ins

• Hiding the Code
• Creating an Add-in
• Closing Add-ins
• Code Changes
• Saving Changes
• Interface Changes
• Installing an Add-in
• AddinInstall Event
• Removing an Add-in from the Add-ins List

Chapter 18: Automation Add-Ins and COM Add-Ins

• Automation Add-Ins
• COM Add-Ins

Chapter 19: Interacting with Other Office Applications

• Establishing the Connection
• Opening a Documents in Word
• Accessing an Active Word Document
• Access and ADO
• Access, Excel, and Outlook
• Better than Mail Merge

Chapter 20: Data Access with ADO

• An Introduction to Structures Query Languages (SQL)
• An Overview of ADO

Chapter 21: Managing External Data

• The External Data User Interface
• The QueryTable and ListObject
• The WorkbookConnection Object and the Connections Collection
• External Data Security Settings

Chapter 22: The Trust Center and Document Security

• The Trust Center
• Automating Document Inspection

Chapter 23: Browsing OLAP Data Sources with Excel

• Analyzing OLAP Data via Pivot Tables
• Understanding the MDX behind OLAP-based Pivot Tables
• Browsing OLAP Data Sources without Pivot Tables
• Creating Offline Cubes

Chapter 24: Excel and the Internet

• What Can the Internet Do For You?
• Using the Internet for Storing Workbooks
• Using the Internet to Publish Results
• Using the Internet as a Communication Channel

Chapter 25: International Issues

• Changing Windows Regional Settings and the Office 2007 UI Language
• Responding to Regional Settings and the Windows Language
• Interacting with Excel
• Interacting with Users
• Excel 2007’s International Options
• Features That Don’t Play by the Rules
• The Range.Values, Range.Formula, and Range.FormulaArray Properties
• The Range.AdvancedFilter Method
• The Application.Evaluate, Application.ConvertFormula, and Application.ExecuteExcel4Macro Functions
• Responding to Office 2007 Language Settings
• Some Helpful Functions

Chapter 26: Programming the VBE

• Identifying VBE Objects in Code
• Starting Up
• Adding Menu Items to the VBE
• Working with Workbooks
• Working with Code
• Working with UserForms
• Working with References
• COM Add-ins

Chapter 27: Programming with the Windows API

• Anatomy of an API Call
• Interpreting C-Style Declarations
• Constants, Structures, Handles, and Classes
• What if Something Goes Wrong?
• Wrapping API Calls in Class Modules
• Some Example Classes
• Modifying UserForm Styles
• Resizable UserForms