Advanced Microsoft Excel VBA Training

Our 3-day hands-on advanced Microsoft Excel VBA training for business shows you how to build advanced data processing, data integration and dashboard displays in Microsoft Excel using VBA. Our training starts off with a complete review of the VBA language, different code modules, objects, properties, methods and how they are all used in programming. Next it discusses how to identify and control complex ranges, hard to find cells, worksheets and workbooks using advanced methods that apply to advanced dashboard and data tool design. Our training then dives into how to control the Window's folder, file and registry system and how to loop through folders looking for various data files.

Next it discusses how to read different data configurations using various loops and process, clean, parse, concatenate, and replace data using a variety of different worksheet and VBA functions. Also covered are advanced VBA data processing techniques for reading complex table arrangements on the worksheet, tracking shifting header positions, figuring out table starts and stops, and automating pivot and query tables. In addition, our training also covers how to read and write to text files and control various databases, worksheet tables and text files using ADO, DAO and SQL.

After discussing data analysis, our training provides an in depth review of userforms and how to control them along with how to control ActiveX controls on both worksheets and userforms using VBA. It then begins the grand finale showing how to bring everything together to build advanced visual interfaces on worksheets while also providing a full review of how to use various key events to make them interactive. Creating advanced chart reports is also covered. Last, advanced VBA methods for controlling PowerPoint, Word, Outlook, web pages and interfacing with VB.Net is covered.

 

Key Excel VBA skills learned:

• A complete review of the Excel VBA language and debugging where needed in the class
• Complete review of a class, object, property and method and their VBA syntax rules
• How to use the CreateObject and GetObject VBA functions to run other class libraries and programs in VBA
• Advanced Excel VBA methods for building/tracking various complex range configurations, names and formulas on the worksheet
• How to design Excel VBA procedures that open or find open workbooks, find worksheets and track them
• How to use VBA and the FileSystem, Folder and File objects to control and create Window folders
• How to design efficient For...Next loops that scan various data configurations and process them
• How to use key Excel VBA functions and worksheet functions in your VBA code that process data, parse text, find errors, find table headers, summarize data, fix data,...
• Advanced Excel VBA methods for processing complex worksheet table data patterns
• Advanced VBA methods for processing data in multiple workbooks and worksheets including looping through folders
• Advanced Excel VBA methods for using Excel data tools like Sort, AutoFilter, Remove Duplicates, Excel tables, query tables, Pivot Tables and Data Validation
• How to use Excel VBA to communicate with databases using DAO, ADO and SQL
• Advanced Excel VBA methods for opening and reading text files using Text Wizard, the TextStream object, loops, ADO and SQL
• Advanced Excel VBA methods for querying Excel worksheets like a database using SQL and ADO
• How to create advanced VBA procedures that store and retrieve program information in the Windows registry
• Advanced Excel VBA methods for creating VBA procedures that run at a specific time or key combination
• How to build userforms and ActiveX controls and control them using VBA
• Advanced Excel VBA methods for loading list boxes and combo boxes with data
• How to use various built-in dialog boxes and Windows dialog boxes to create folder and file interfaces
• How to use the worksheet, userforms, ActiveX controls, drawing shapes, Excel Ribbon and events in Excel VBA to create advanced dashboard interfaces
• Advanced Excel VBA methods for chart creation and formatting
• How to create and layout mutli-chart report worksheets using Excel VBA
• Advanced Excel VBA methods for controlling other programs like Microsoft Word, PowerPoint, Outlook and web pages
• Introduction to integrating VB.NET with your Excel VBA programs

 

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

• Used the Excel VBA language before
• Used variables, loops and logic before and know how they basically operate
• Used objects, properties and methods before in code to control a program (do not have to be expert)
• Used Excel drawing shapes and built charts manually
• Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM, MATCH, COUNTIF, COUNTA...
• A desire to understand Excel VBA from a nuts and bolts perspective

Day-1

Full Excel VBA Language and Object Review

• A review of all major VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling where needed in the training
• Review of an object, property and method and how are they used in Excel VBA programming
• Review of the syntax rules used in VBA code to identify an object and run its properties and methods
• Learn how to build an object in an Excel class module creating its own properties and methods
• Strategies for using the Set statement to track objects in your VBA code
• How to use CreateObject and GetObject in your Excel VBA code to access other class libraries and programs

Advanced Excel VBA Methods for Controlling Ranges, Names, Formulas and Data Validation

• Review of how to use the Range, Cells, Columns, Rows, CurrentRegion, SpecialCells, Offset and Find accessors to identify cells and ranges in VBA
• Excel VBA techniques for tracking and building various range configurations and sub ranges, headers on the worksheet and resizing data ranges
• How to use Excel VBA to figure out worksheet table start and stop points and insert/delete data in various range configurations
• How to use VBA to build formulas automatically that use references and functions and figure out the ranges that the functions use
• How to run various worksheet functions in your VBA code and automatically figure out their range configurations
• How to use Excel VBA to apply number formats, fonts, alignments, borders and colors... to various complex range configurations
• Review of how to use cell names on the worksheet for various tracking purposes
• How to leverage names in Excel VBA and adjust their settings with VBA
• How to use Excel VBA to dynamically control a cell's Data Validation settings, key to controlling model, form and program entries
• How to use Excel VBA to control pivot tables and query table ranges

Advanced VBA Methods for Controlling Workbooks and Worksheets

• Why store data in multiple workbooks and use Excel VBA to integrate their data
• How to properly design Excel VBA code to create, open, save and close workbooks in an advanced data program
• How to use VBA to create workbook names (dates, version...) that can be easily tracked and used in code
• Excel VBA techniques for tracking workbooks and worksheets when they are opened or added
• Using Excel VBA, loops and Remove Duplicates to add, move and rename worksheets in models and data analysis apps
• Using the VBA For...Each Next loop in VBA to search for open workbooks and worksheets
• How to create workbook level event procedures that run VBA code when a workbook is opened, saved, closed...

Using the FileSystemObject, Folders and File Object to Loop Through Workbooks in a Folder

• Strategy behind storing data in multiple workbooks in folders and using VBA For...Each Next loops to integrate their data
• Overview of the FileSystemObject, Folder and File object; how do they control the Windows folder and file system
• Using the VBA For...Each Next loop in Excel VBA to search for workbooks in various folders
• Using the Split and Instr VBA text functions to read parts of your file and folder names as you loop through them

Excel VBA Methods for Scanning, Analyzing and Fixing Worksheet Data

• Reading worksheet data efficiently, the key to developing high-end data analysis apps, models and dashboard displays
• How to create proper tables on worksheets and in text files that VBA, ADO and SQL can read
• Using  For...Next loops and logic to efficiently access and process your worksheet data in normal tables
• Using various IS functions to determine if your worksheet data being read has errors
• Using the Trim, Clean, Left, Right, Mid, Replace, Instr and Split functions to parse, interpret and fix the worksheet data being read
• Using the Month, Day, Year, Weekday, Format, DateDiff, DateAdd,... VBA functions to tear worksheet dates apart in VBA
• Assembling dates and other strings in VBA using advanced string concatenation techniques
• Converting worksheet data into different data types using the C functions (Cdate, Clng) and the data type declarations
• Speed runs, bringing ranges into your Excel VBA code as arrays and processing using VBA For...Next loops and For...Each Next loops
• VBA programming techniques for looping through VBA arrays and syncing their output with the range they came from

Day-2

How to Analyze Complex Worksheet Table Patterns and Data In Multiple Workbooks

• Using the VBA For...Next loop to analyze worksheet data arranged in complex table patterns (stacked tables, fragmented tables, stepped tables...)
• How to write the VBA logic to analyze worksheet data in complex table patterns (start/stop points, current value, partially filled in tables,...)
• Advanced Excel VBA methods for finding moving headers and other fluctuating data positions on a worksheet
• Advanced techniques for finding and processing data on multiple worksheets and in multiple workbooks
• Advanced VBA programming techniques for using the Sort, Remove Duplicates and Filter tools inside of VBA For...Next loops
• How to use For...Next loops, logic and key worksheet functions to automatically construct various summary and report worksheet tables
• How to use For...Next loops, logic, names and the VBA date/time functions to automatically build various output tables and forms

How to Use Excel VBA to Control Text Files

• Integrating text file data with your Excel VBA programs, which programming approach to use
• The different text file extension types and how to access them using Excel VBA
• Using Excel VBA to automate the Text Wizard tool to open and parse text file data
• Using Excel VBA to automate the Text to Columns tool to parse worksheet text data
• How to use VBA to open, read, write and close text files using the TextStream object
• How to use string concatenation to assemble text that will be written to text files
• How to use the VBA Do...Loop to access text file data and the logic to shut it down
• Excel VBA methods for loading text file data on the worksheet

How to Use SQL, ADO and DAO in Excel VBA to Control Databases and Other External Data Sources

• SQL language review for querying, adding, updating and deleting databases data
• How to assemble SQL statements and connection strings in VBA using string concatenation, key VBA functions and loops
• How to use DAO and ADO to query workbook, text file and database data
• Advance VBA techniques for querying and combining workbook and text file data using SQL and ADO

Combining Excel VBA, the Shell Object, the FileSystemObject Object and the Windows Registry for File and Folder Management

• Why design folder and file selectors for your programs
• Overview of the various built-in dialog boxes to use from Windows and Excel when creating folder and file selectors
• Using Excel's Open and Save As dialog boxes in Excel VBA to create file selectors
• How to use the Shell object in Excel VBA to control the Windows Browse for Folder dialog box; allows users to select Windows folders and other things that your Excel VBA program will use
• Why use the Windows Registry to store your VBA program file and folder information
• Using Excel VBA to store and retrieve program information in the Windows Registry
• VBA logic behind setting file and folder paths first, then having other procedures use this information after it has been set
• How to use the FileSystemObject in VBA to create, move, rename and delete Windows folders automatically
• Naming conventions to use in VBA for folder and file names
• Using the Split and Instr VBA text functions in your VBA code to read parts of your file and folder names

How to Run VBA Procedures at a Certain Time or by Name

• How to use the OnTime method in VBA to create procedures that run at a specific time
• How to use the OnKey method in VBA to create a VBA procedure that runs via key combination
• How to use the Run method in VBA to run VBA procedures in other workbooks, add-ins or call by string

Day-3

How to Create and Command Userforms and ActiveX Controls

• What is a VBA userform and how to use them in interfaces
• How to create and layout ActiveX controls and pictures on userforms
• How to control scroll bars, spin buttons, check boxes, option buttons and image controls on userforms using VBA
• How to use VBA to control text boxes on userforms and error check their typed data
• How to use VBA to control list boxes and combo boxes on userforms
• Using ranges, VBA arrays and other Excel data tools like Sort and RemoveDuplicates in VBA to load data into list boxes and combo boxes
• Using the ArrayList object in Excel VBA to collect and sort data from worksheets, text files and databases for use in list boxes and combo boxes
• VBA techniques for formatting data inside of list boxes and combo boxes
• How to create and read multi-column list boxes and combo boxes using Excel VBA
• How to create multi-select list boxes on userforms and command them using VBA
• How to create a list box on a userform in VBA that fills in another list box using events
• What is the difference between a modal and non-modal userform in VBA
• Designing VBA procedures that launch (show) and preset ActiveX controls values on userforms
• How to create event procedures for ActiveX controls on userforms
• How to design a floating toolbar that runs program elements using userforms and events in VBA
• Designing VBA code that shows the toolbar when a key combination is pressed
• How to create ActiveX control events in your Excel VBA code that trap key strokes and movement on a userform
• What is an Excel Add-In and how can they be used to house userforms

Developing Advanced Interfaces Using the Excel Worksheet, ActiveX Controls, Shapes, Pictures, Ribbon and Events

• Ways to look at and configure the worksheet as an advanced dashboard interface
• How to create worksheet event procedures that interact with a user's worksheet actions like typing, clicking, right-mouse clicking,...
• How to turn Excel's drawing shapes into a visual interface
• How to command ActiveX controls on worksheets, slight modification from userforms
• How to use Excel VBA and loops to add, position, format, change the text of, and delete drawing shapes and pictures on the worksheet
• How to use Excel VBA to automatically assign procedures to drawing shapes using logic
• How to store vital program information in shape and picture names and then retrieve them when they are clicked as buttons
• Advanced Excel VBA techniques for controlling Pivot Tables and Pivot Charts
• Advanced Excel VBA techniques for controlling worksheet Query tables
• How to create a tab and button in the Excel Ribbon for program control

How to Create Advanced Chart Reports Using Excel VBA

• Review of how to create chart sheets and embedded charts using Excel VBA
• Review of formatting charts using Excel VBA
• How to use Excel VBA to add, edit and delete a chart's data series
• How to use Excel VBA to manually create a chart series using arrays instead of ranges, great for emailed reports
• How to use VBA For...Each Next loops to loop through series, legends and data points formatting them (make invisible, color a specific point, color points that are negative, grey out...)
• How to write Excel VBA code that automatically creates and arranges multiple charts on a report worksheet

Excel VBA Strategies for Controlling Word, PowerPoint, Outlook and the Web

• Why does Excel VBA and PowerPoint, Word and Outlook make such a good report generation environment
• How to track and output report data to Word using Excel VBA
• How to track and output report data to PowerPoint using Excel VBA
• How to automate outlook to read incoming emails and send emails
• How to read and control web pages including html tables and form controls using Excel VBA

Introduction to Using VB.Net with Excel VBA

• Intro on how to integrate the capabilities of VB.NET with VBA
• The simple rules to compile a VB.NET Com Class library that can be called from VBA