Excel 2013 VBA and Macros
Request a Class
9:00am - 4:30pm
This three-day course takes you through Visual Basic for Application in Excel, all the way from recording Excel VBA macros through to designing your user forms. On the way you will learn what variables are, how to write object-oriented programs, how to create loops and much more besides. VBA enables you to achieve tremendous efficiencies in your day-to-day use of Excel. Without your waiting for resources from IT, VBA helps you figure out houw to import data and produce reports in Excel.
AUDIENCE AND PREREQUISITES
You must be an Experienced Excel user who wish to automate repetitive tasks or simplify more complex tasks using Excel 2013 Macros and Visual Basic for Applications (VBA) programming language. You must have good Excel skills and understand key concepts of spreadsheets or equivalent. It is not necessary to have prior programming knowledge. We cover VBA Macro programming concepts from the very basic level. Excel 2013 Level 1, 2 & 3.
*Course cost listed does not include the cost of courseware (required) or lunch (optional). Please contact us at firstname.lastname@example.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to minimum enrollment. Course may run as a live distance learning class if minimum is not met.
Introduction Getting Results with VBAUnleash the Power of Excel with VBA The Power of Excel Barriers to Entry Knwoing your Tools: The Developer Tab Understand Which File Types Allow Macros Macro Security Overview of Recording, Storing, and Running a Macro Running a Macro Understanding the VB Editor Understanding Shortcomings of the Macro RecorderThis Sounds like BASIC, So Why Doesn't It Look Familar? I Can't Understand This Code Understanding the Parts of VBA "Speech" VBA is Not Really Hard Examining Recorded Macro Code: Using the VB Editor and Help Using Debugging Tools to Figure Out Recorded Code Object Browser Seven Tips for Cleaning Up Recorded CodeReferring to Ranges The Range Objects Syntax to Specify a Range Named Ranges Shotcut for Referencying in Other Sheets Referencing a Range Related to Another Range Use the Cells Property to Select a Range Use the Resize Property to Change the Size of a Range Use the Columes and Rows Properties to Specify a Range Use the Union Method to Join Multiple Ranges Use the Intersect Method to Create a New Range from Overlapping RangesLooping and Flow Control For Next Loops Do Loops the VBP Loop For Each Flow Control Using If.. Then..Else and Select CaseR1C1-Style Formulas Referring to Cells A1 Versus R1C1 References Toggling to R1C1-Style References The Miracle of Excel Formulas Explanation of R1C1 Reference Syle Array Formulas Require R1C1 FormulasCreate and Munipulate Names in VBA Excel Names Global Versus Local Names Adding Names Deleting Names Adding Comments Types of Names Hiding Names Checking for the Existence of a NameEvent Programming Levels of Events Using Events Workbook Events Worksheet Events Chart Sheet Events Application-Level EventsArrays Declare an Array Declare a Multidimensional Array Fill an Array Retrieve Data from an Array Use Arrays to Speed Up Code Use Dynamic Arrays Passing an ArrayCreating Classes, Records, and Collections Inserting a Class Module Trapping Applicat5ion and Embedded Chart Events Creating a Custom Object Using a Custom Object Using Property Led and Property Get to Control How Users Utilize Custom Objects Using Collections to Hold Multiple Records Using User-Defined Types to Create Custom PropertiesUserforms An Introduction User Interaction Methods Creating a Userform Calling and Hiding a Userform Programming the Userform Programming Controls Using Basic Form Controls Verifying Field Entry Illegal Window Closing Gettinga FilenameData Mining with Advanced Filter Replacing a Loop with AutoFilter Advanced Filteris Easier in VBA Than in Excel Using Advanced Filter to Extract a Unique of Values Using Advanced Filter with Criteria Ranges Using Filter in Place in Advanced Filter The Real Workhorse xlFilterCopy with All Records Rather Than Unique Records OnlyUsing VBA to Create Pivot Tables Introducing Pivot Tables Understanding Versions Building a Pivot Table in Excel VBA Using Advanced Pivot Table Features Filteringa Dataset Using the Data Model in Excel 2013 Using Other Pivot Table FeaturesExcel Power File Operations Combining and Seperating Workbooks Working with Cell Comments Utilities for VBA Pros Cool ApplicationsSample-User Defined Functions Creating User-Defined Functions Sharing UDFs Useful Custom Excel FunctionsCreating Charts Charting in Excel 2013 Understanding the Global Settings Creating a Chart in Varios Excel Versions Customizing a Chart Creating a Combo Chart Creating Advanced Charts Exporting a Chart as a Graphic Creating a Chart as a GraphicData Visualizations and Conditional Formatting Introduction to Data Visualizations VBA Methods and Properties for Data Visualizations Adding Data Bars to a Range Adding Color Scales to a Range Adding Icon Sets to a Range Using Visualization Tricks Using Other Conditional Formating MethodsDashboarding with Sparklines in Excel 2013 Creating Sparklines Scaling Sparklines Formating Sparklines Creating a DashboardText File Processing Importing from Text Files Writing Text FilesAutomating Word Using Early Binding to Reference the Word Object Using Late Binding to Reference the Word Object using the New Keyword to Reference the Word Application using the CreateObject Function to Create a New Instance of an Object Using the GetObject Function to Reference an Existing Instance of Word Using Constant Values Using Word's Objects Controlling Form Fields in WordUsing Access as a Back End to Enhance Multiuser Access to Data Adding and Retrieving a Record to the Database Updating an Existing Record Deleting Records via ADO Other Utilites via ADO SQL Server ExamplesAdvanced Userform Techniques Using the UserForm Toolbar in the Design of Controls on Userforms More Userform Controls Controls and Collections Modeless Userforms Using Hyperlinks in Userforms Adding Controls at Runtime Adding Help to the UserformWindows APiHandling ErrorsCustomizing the Ribbon to Run Macros