Course Content
Primer in Excel VBA
- Using the Macro Recorder
- Running Macros
- The Visual Basic Editor
- User-Defined Functions
- Creating a UDF
- The Excel Object Model
- Objects
- Calling Functions and Sub
- Procedures
- Variable Declaration
- Scope and Lifetime of
- Variables Variable Type
- Object Variables
- Making Decisions
- Looping
- Arrays
- Run-Time Error-Handling
Workbooks and Worksheets
- The Workbooks Collection
- Getting a Filename from a Path
- Files in the Same Directory
- Overwriting an Existing Workbook
- Saving Changes
- The Sheets Collection
- Worksheets
- Copy and Move
Using Ranges
- Activate and Select
- Range Property
- Cells Property
- Offset Property
- Resize Property
- Special Cells Method
- Last Cell
- Current Region Property
- Union and Intersect Methods
- Empty Cells
- Deleting Rows
Using Names
- Naming Ranges
- Using the Name Property of the Range Object
- Working with Named Ranges
- Determining which Names Overlap a Range
Data Lists
- Structuring the Data
- Sorting a Range
- Sorting a Table
- AutoFilter Object
- Filter Object
- Date Custom Filter
- Adding Combo Boxes
- Copying the Visible Rows
- Advanced Filter
PivotTables
- Creating a PivotTable Report
- PivotCaches
- PivotTables Collection
- PivotFields
- CalculatedFields
- PivotItems
- Grouping
- Visible Property
- CalculatedItems
- PivotCharts
- External Data Sources
Charts
- Chart Sheets
- The Recorded Macro
- Adding a Chart Sheet Using VBA Code
- Embedded Charts
- Using the Macro Recorder
- Adding an Embedded Chart Using VBA Code
- Defining Chart Series with Arrays
- Converting a Chart to Use Arrays
- Determining the Ranges Used in a Chart
- Chart Labels
Event Procedures
- Worksheet Events
- Enable Events
- Worksheet Calculate
- Chart Events
- Workbook Events
Adding Controls
- Form and ActiveX Controls ActiveX Controls Scrollbar Control
Text Files and File Dialog
- Opening Text Files
- Writing to Text Files
- Reading Text Files
- Writing to Text Files Using Print
- Reading Data Strings
- Flexible Separators and Delimiters
- FileDialog
- FileDialogFilters
- FileDialogSelectedItems
UserForms
- Displaying a UserForm
- Creating a UserForm
- Activex components,Tool Box
RibbonX
- Adding the Customizations
- XML Structure
- RibbonX and VBA
- Control Types
- Basic Controls
- Container Controls
- Control Attributes
- Control Callbacks
- Managing Control Images
Interacting with Other Office Applications
- Establishing the Connection
- Late Binding
- Early Binding
- Opening a Document in Word
- Accessing an Active Word Document
- Creating a New Word Document
- Access and ADO
- Access, Excel, and, Outlook
Data Access with ADO
- An Introduction to Structured Query Language (SQL)
- The SELECT Statement
- The INSERT Statement
- The UPDATE Statement
- The DELETE Statement
- An Overview of ADO
- The Connection Object
- The Recordset Object
- The Command Object
- Using ADO in Microsoft Excel Applications
- Using ADO with Microsoft Access
- Using ADO with Microsoft SQL Server
- Using ADO with Non-Standard Data Sources
Managing External Data
- The External Data User Interface
- Get External Data
- Manage Connections
- The QueryTable and ListObject
- A QueryTable from a Relational Database
- A Query Table Associated with a ListObject
- QueryTables and Parameter
- Queries QueryTables from Web Queries
- A QueryTable from a Text File
- Creating and Using Connection Files
- The WorkbookConnection Object and the Connections Collection
- External Data Security Settings
Programming the VBE
- Identifying VBE Objects in Code
- The VBE Object
- The VBProject Object
- The VBComponent Object
- The CodeModule Object
- The CodePane Object
- The Designer Object
- Tool Management in VBA
