Pre-requisites:
Must have a working knowledge of Excel. Power users of Excel and end-user specialist who need to develop macros for their departments.
Chapter 1. Make a Macro Do Simple Tasks
What’s the Difference Between VBA and a Macro?
Creating a Simple Macro o Format Currency by Using a Built-In Tool o Record a Macro to Format Currency o Run the Macro o Assign a Shortcut Key to the Macro o Look at the Macro o Save the Macro Workbook
Changing Multiple Properties at Once o Create Sidebar Headings with a Command o Record a Macro to Merge Cells Vertically o Eliminate Unnecessary Lines from the Macro
Manipulating Recorded Properties o Record a Macro to Remove Window Elements o Run the Macro from the Visual Basic Editor o Use a Macro to Toggle the Value of a Property o Eliminate Repeated Objects in a Recorded Macro o Run a Macro from the Quick Access Toolbar
Recording Methods in a Macro o Convert a Formula to a Value by Using Menu Commands o Convert a Formula to a Value by Using a Macro o Make a Long Statement More Readable
Trusting Macro-Enabled Workbooks o Designate a Trusted Location for Macros o Designate a Trusted Publisher for Macros o Key Points
:: Course VBA07 ::
Chapter 2. Make a Macro Do Complex Tasks
Task One: Opening the Report File o Open a Text File o Watch a Macro Run by Stepping Through It o Select a File While Running a Macro
Task Two: Filling In Missing Labels o Select Only the Blank Cells o Fill the Selection with Values o Record Filling In the Missing Values o Watch the FillLabels Macro Run
Task Three: Adding a Column of Dates o Add a Constant Date o Step Through the Macro o Prompt for the Date
Task Four: Appending to the Database o Append Data to a Master List o Step Through the AppendData Macro o Record a Relative Movement o Choose Whether to Save Changes While Closing a File
Task Five: Deleting the Worksheet o Create a Macro to Delete the Active Worksheet o Make the Macro Operate Quietly
Assembling the Pieces o Record a Macro That Runs Other Macros o Simplify the Subroutine Statements
Chapter 3. Explore Workbooks and Worksheets
What Is an Object?
o Objects Come in Collections o Objects Have Properties o Objects Have Methods o Methods Can Change Properties o Properties Can Involve Actions
Understanding Workbooks o Add a New Workbook o Count the Workbooks o Close the Workbooks o Refer to a Single Workbook o Refer to a Workbook by Name o Refer to a Workbook by Pointing o Change a Workbook Property Value
Understanding Worksheets o Add a New Worksheet o Rename and Delete a Worksheet o Look at the Return Value of the Delete Method o Look at the Result of the Add Method o Copy a Worksheet o Manipulate Multiple Worksheets
:: Course VBA07 ::
o Declare Variables to Enable Auto Lists
Chapter 4. Explore Range Objects
Referring to a Range o Refer to a Range by Using an Address o Refer to a Range as a Collection of Cells o Refer to a Range as a Collection of Rows or Columns o Refer to a Range Based on the Active Cell o Refer to Subsets of a Range o Refer to a Relative Range
Enhancing Recorded Selections o Simplify Select...Selection Pairs o Simplify Select Groups
Entering Values and Formulas into a Range o Relative References o Absolute References o R1C1 Reference Style o Put Values and Formulas into a Range o Construct Formulas to Fill a Grid
Formatting a Range o Add Borders to a Range o Format the Interior of a Range
Chapter 5. Explore Data Objects
Working with Excel Tables o Create a New File from an Existing Worksheet o Create a Table from an Internal Source o Create a Table from an External Source o Record a Macro to Manipulate a Table o Manipulate Table Columns o Manipulate Table Totals and Filters
Working with PivotTable Reports o Create a PivotTable Report from an Internal Source o Create a PivotTable Report from an External Source o Record a Macro to Set the PivotTable Structure o Set the PivotTable Structure o Record a Macro to Customize a PivotTable Layout o Customize a PivotTable Layout o Record a Macro to Customize a PivotTable Style o Customize a PivotTable Style o Key Points
Chapter 6. Explore Graphical Objects
Exploring Graphical Objects o Use Worksheet Cells as a Drawing Grid o Add a Gradient Fill to a Cell o Add a Gradient-Filled Shape o Reference a Selected Shape o Use an AutoShape to Create a Logo o Use Grouped Shapes to Create Macro Buttons
:: Course VBA07 ::
Exploring Chart Objects o Create a Chart o Synchronize Two Charts o Format the Plot Area of a Chart
Chapter 7. Control Visual Basic
Using Conditionals o Make a Decision o Make a Double Decision o Ask Yourself a Question o Test for a Valid Entry o Ask with a Message
Creating Loops o Loop Through a Collection by Using a For Each Loop o Loop with a Counter by Using a For Loop o Loop Indefinitely by Using a Do Loop
Managing Large Loops o Set a Breakpoint o Set a Temporary Breakpoint o Show Progress in a Loop o Key Points
Chapter 8. Extend Excel and Visual Basic
Creating Custom Functions o Use a Custom Function from a Worksheet o Add Arguments to a Custom Function o Make a Function Volatile o Make Arguments Optional o Use a Custom Function from a Macro
Handling Errors o Syntax Errors o Compiler Errors o Logic Errors o Run-Time Errors o Ignore an Error o Ignore an Error Safely by Using a Subroutine o Add Arguments to Generalize a Subroutine o Check for an Error o Loop Until an Error Goes Away o Trap an Error
Programme scope, delivery arrangements and suitability are confirmed during enquiry.