Students learn the Visual Basic for Applications language as well as the Excel classes and objects needed to create VBA scripts to automate Excel. Participants create procedures and functions using the VBA language and Excel objects. The PivotTable object is used to create macros that add interactivity to the standard Pivot Table. As well, debugging and error handling techniques are reviewed.Prerequisite:Proficient knowledge of Microsoft Excel. Knowledge of PivotTables helpful but not required.Getting StartedIntroducing Visual Basic for ApplicationsDisplaying the Developer Tab in the RibbonRecording a MacroSaving a Macro-Enabled WorkbookRunning a MacroEditing a Macro in the Visual Basic EditorUnderstanding the Development EnvironmentUsing Visual Basic HelpClosing the Visual Basic EditorUnderstanding Macro SecurityWorking with Procedures and FunctionsUnderstanding ModulesCreating a Standard ModuleUnderstanding ProceduresCreating a Sub ProcedureCalling ProceduresUsing the Immediate Window to Call ProceduresCreating a Function ProcedureNaming ProceduresWorking with the Code EditorUnderstanding ObjectsUnderstanding ObjectsNavigating the Excel Object HierarchyUnderstanding CollectionsUsing the Object BrowserWorking with PropertiesUsing the With StatementWorking with MethodsCreating an Event ProcedureUsing Expressions, Variables, and Intrinsic FunctionsUnderstanding Expressions and StatementsDeclaring VariablesUnderstanding Data TypesWorking with Variable ScopeUsing Intrinsic FunctionsUnderstanding ConstantsUsing Intrinsic ConstantsUsing Message BoxesUsing Input BoxesDeclaring and Using Object VariablesControlling Program ExecutionUnderstanding Control-of-Flow StructuresWorking with Boolean ExpressionsUsing the If...End If Decision StructuresUsing the Select Case...End Select StructureUsing the Do...Loop StructureUsing the For...To...Next StructureUsing the For Each...Next StructureGuidelines for use of Control-of-Flow StructuresWorking with Forms and ControlsUnderstanding UserFormsUsing the ToolboxWorking with UserForm Properties, Events, and MethodsUnderstanding ControlsSetting Control Properties in the Properties WindowWorking with the Label ControlWorking with the Text Box ControlWorking with the Command Button ControlWorking with the Combo Box ControlWorking with the Frame ControlWorking with Option Button ControlsWorking with Control AppearanceSetting the Tab OrderPopulating a ControlAdding Code to ControlsLaunching a Form in CodeWorking with the PivotTable ObjectUnderstanding PivotTablesCreating a PivotTable Using Worksheet DataWorking with the PivotTable ObjectsWorking with the PivotFields CollectionAssigning a Macro to the Quick Access ToolbarDebugging CodeUnderstanding ErrorsUsing Debugging ToolsSetting BreakpointsStepping through CodeUsing Break Mode during Run modeDetermining the Value of ExpressionsHandling ErrorsUnderstanding Error HandlingUnderstanding VBA's Error Trapping OptionsTrapping Errors with the On Error StatementUnderstanding the Err ObjectWriting an Error-Handling RoutineWorking with Inline Error Handling
Overview
Taught by
ONLC Training Centers
