Beginning Microsoft Excel VBA Training

Our 2-day hands-on live beginning Microsoft Excel VBA training for business provides a full review of the Excel VBA language and shows you how to use VBA to control essential Excel elements that are vital to business. Our training starts off with the basics of programming covering VBA terminology, procedures, statements and how to use the VBA Editor. It then discusses how to use variables, logic, expressions, functions and other VBA programming elements to construct simple programs. After the basics have been introduced, our training discusses in detail how to use Excel VBA to command key Excel program elements like worksheets, workbooks, cells, ranges, charts and pivot tables. It also discusses the basics of using Excel VBA to control common Excel tasks like reading and writing to cells, copying and pasting data, sorting and filtering data, adding and deleting rows and columns, running Excel worksheet \ VBA functions, and worksheet formatting. After these programming elements have been discussed, our training then shows how to use loops and logic to tie everything discussed together to form useful data processing programs.

Key Excel VBA skills learned:

• What is VBA?
• A complete review of the Excel VBA Editor and its features
• A complete review of the beginning Excel VBA macro language including procedures, loops, logic, variables, functions, properties, methods and objects
• How to use Excel VBA  to control key Excel data tools like sorting, filtering, pivot tables, text-to-columns, removing duplicates...
• How to use VBA to track and control worksheet cells and ranges including adjustable ranges
• How to use VBA to track and control workbooks and worksheets including creating, opening, naming and saving and closing
• How to format cells including naming, fonts, colors, borders and number formats
• How to perform calculations in your Excel VBA code using formulas, worksheet functions and VBA functions
• How to use VBA to manage and transfer worksheet data including copying and pasting, inserting and deleting and moving
• How to use VBA For...Next loops and logic to process worksheet data on single worksheets and in multiple worksheets
• How to use VBA For...Each Next loops to find workbooks and worksheets
• How to use Excel VBA to create and control business charts
• Introduction to controlling other programs like along with Word and PowerPoint

 

Select this Excel VBA training if you or your group have:

• Never programmed
• Just used the macro recorder
• Not programmed in over a year
• Programmed but have not used objects before
• Used Microsoft Excel and know its operational capabilities and its tools
• Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM

Day-1

Visual Basic Editor and VBA Terminology Review

• A complete review of the Excel VBA Editor and its windows
• What is a VBA project used for in programming
• What is a VBA module used for in programming
• What is a VBA Sub procedure and how is it used in programming
• What is a VBA Function procedure and how is it used in programming
• What is a statement and how is it used in programming
• Overview of how to use the different VBA procedure types in your Excel VBA programs
• How to use the VBA Editor's debugging tools to correct your Excel VBA code

Excel VBA Program and Language Review

• What is an Excel VBA program and how to translate your ideas into one
• Overview of the VBA language elements that make up program
• The different ways to run your VBA code
• Complete review of the VBA core language elements including how to use variables, data types, constants, arrays, operators, expressions, loops, logic decisions and calling conventions
• How to use VBA to command Excel and as well as other programs
• What are objects, properties and methods and how to use them in Excel VBA
• How to use VBA to control specific Microsoft Excel program elements (command.command.command structure))
• The proper way to track objects in your VBA code
• What is logic and how is it used in programming
• What is a loop and how is it used in programming (loops discussed in depth on Day-2))
• How to use IF, Select Case and expressions in Excel VBA to create logic decisions
• How to translate your decisions into VBA code logic

Creating Formulas and Using Functions in Your Excel VBA Code (Expression Overview)

• How to create different types of formulas (expressions) in your VBA code
• How to use the different mathematical operators (+,-,/,*...) to create basic math formulas (numeric expressions) in your Excel VBA code
• How to use the & in Excel VBA to create string concatenation formulas that combine text (string expressions)
• How to use the relational operators (<, >, <=, >=,...) in Excel VBA to create logical formulas (relational expressions) that make decisions
• How to use the logical operators (And, Or, Not,...) in Excel VBA to make multiple decisions
• How to run VBA functions and Excel worksheet functions in your VBA code
• Key VBA functions and Excel worksheet functions to know for your Excel business tasks
• Using key VBA and Excel worksheet functions in your VBA code that analyze dates and times
• Using key VBA and Excel worksheet functions in your VBA code that sum and count
• Using key VBA and Excel worksheet functions in your VBA code that lookup information
• Using Key VBA and Excel functions in your code that deal with text

Controlling Cells and Ranges Using Excel VBA

• How to identify and use cells and ranges in your Excel VBA code
• Using VBA to copy and paste worksheet data
• How to use Excel VBA to read worksheet data and output program information to any worksheet or workbook
• How to use worksheet cell and range names in your Excel VBA code to retrieve and output worksheet data
• Using Excel VBA to track resizing data tables on worksheets
• How to use Find and Excel/VBA functions to search for cells with specific data
• Using VBA to delete, insert and move columns, rows and cells on the worksheet
• How to use Excel VBA to control worksheet number formats, fonts, alignments, borders and colors
• How to use Excel properties like bold, color, values in logic decisions
• Using Excel VBA to manage formulas and cell/range names

Controlling Workbooks and Worksheets Using VBA

• Why store data in separate Excel workbooks?
• Useful strategies for tracking workbooks and worksheets in your VBA code
• How to use Excel VBA to open, add, save and close workbooks
• How to use the Open dialog box in VBA to find and open your workbooks (file picker)
• Using Excel VBA to add, move and rename worksheets

Day-2

How to Create Basic Dashboards That Run Your Excel VBA Programs

• Creating a drawing shape as a button to run a VBA procedure
• Using Excel VBA to control drawing shape text, use as a display
• Arranging drawing shapes and the worksheet itself to create a high tech dashboard look

Using Arrays in Excel VBA

• What is an Array and how to create one in Excel VBA
• How to read and write to an array element
• How to determine an array's upper and lower bounds
• Dealing with VBA and Excel Worksheet functions that return arrays

Control Key Excel Data Tools Using Excel VBA

• How to use Excel VBA to control the Excel Sort tool
• How to use VBA to control Autofilter and Remove Duplicates
• How to use Excel VBA to control the grouping tool
• How to use VBA to control basic Excel pivot tables

Using VBA For...Next loops and Logic to Analyze Worksheet Data and Arrays

• How to organize your business data in properly designed table on a worksheet
• Overview of how to use the VBA For...Next loop
• Using VBA For...Next loops to analyze data on a worksheet
• Using the Cells property with the For...Next loop to access worksheet data
• How to write logic that operates inside of a VBA For...Next loop
• Writing expressions that operate inside of a For...Next loop
• How to use VBA For...Next loops and logic to fill out worksheet forms and tables, translating patterns into loops and logic
• How to build a report worksheet while looping
• Using a VBA For...Next loop to read and write to an array

Using VBA For...Each Next loops to Find Workbooks and Worksheets

• Strategy for finding data and information when you do not know what workbook or worksheet contains it
• Overview of how to use the VBA For...Each Next loop to loop through objects
• How to use a VBA For...Each Next loop and logic to search for a specific workbook
• How to use a VBA For...Each Next loop and logic to search for worksheets within a workbook, key to storing data in multiple worksheets
• Using VBA Set statements to track your workbooks and worksheets inside of VBA For...Each Next loops

The Basics of Controlling Charts Using Excel VBA

• How to use Excel VBA to create chart sheets and embedded charts
• Using VBA to control where an embedded charts are placed on a worksheet
• Using Excel VBA Basic for basic chart formatting
• Updating your chart's data using Excel VBA

Introduction to Controlling Other Applications Using Excel VBA

• What is early binding and late binding"
• How to use the CreateObject function in VBA
• Introduction to controlling Word and PowerPoint using VBA