Product Description
Our Excel VBA 1 Week Intensive Course is designed for experienced Excel users who are planning to undertake Excel VBA projects. It provides a solid foundation for new VBA developers, showing them essential techniques, best practices and how to manipulate the main components of the Excel object model. The course covers the six main applications of VBA: the creation of macros, event programming, user-defined functions, user forms, ribbon customization and the creation of class modules.
Basics
Excel VBA overview, Understanding macro steps, Modifying macro steps, Storing values in variables, Adding comments to your code, Stepping through macros, Getting help on macros, Objects, properties and methods, About Excel objects
Range object
The Selection property, The Range property, Targetting cells, Counting the cells in a selection, Using the Offset statement, Retrieving the value of a cell, Retrieving a formula from a cell, Setting the value of cells
Using Visual Basic
Language structure, Using variables, Declaring variables, Data types, Making decisions, If statements, Looping structures
Interactivity
Assigning a macro to the Ribbon, Assigning a macro to the QuickAccess Toolbar, Assigning macros to other objects, Built-in dialogue boxes, Using form controls
Excel object model
About classes and objects, Objects and collections, The hierarchy of objects, Properties, methods and events, The Locals Window, The Immediate Window
Function procedures
Sub versus function, User-defined functions, Declaring a function, Calling a function, Private and public scope, Defining arguments, Optional arguments
The Application object
When to use, ScreenUpdating property, DisplayAlerts property, Setting CutCopyMode to false
Dialogue boxes
MsgBox function, InputBox function, Application.Dialogs
UserForms
Creating a userform, Displaying a userform, Adding Label controls, Adding a TextBox control, The CheckBox control, ComboBox and ListBox, Adding CommandButtons, Setting control properties
Workbooks and worksheets
The Workbooks collection, The Sheets collections, The Window objects, Workbook events
Collections, Class Module Events
Developing applications
Types of applications, Types of end-user, Analysing end-user needs, Planning an application, Planning the user interface
Working with events
Overview of event types, Event-handler procedures, Workbook events, Worksheet events, Chart events, UserForm events, Activate event, Save events, Change events, Focus events, Click events
Pivot tables
Pivot table code overview, Creating a pivot table, PivotField object, PivotItems collection, Modifying a pivot table, Using external data
Charts and Sparklines
About programming charts, Working with embedded charts Working with independent charts, Controlling chart formatting, Assigning a range to a chart, Positioning a chart, Making a chart active/inactive, Editing data series, Controlling chart labels, Creating sparklines, Handling hidden columns, Setting sparkline attributes
File Manipulation
Opening a text file, Writing a text file, Importing data from a text file, Searching for text in files, Verifying if a file exists, Listing all files in a given folder
Class modules
Class modules defined, When to use class modules, Creating a class module, Property procedures, Method procedures
