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 objects, how to use them in programming and how to design your own objects using Class modules. Next it discusses how to identify and control ranges, cells, worksheets and workbooks using advanced methods that lead to advanced dashboard design and data processing programs. Our training then dives into how to control the Window's folder and file system and loop through folders looking for various files. Also covered are advanced data processing techniques for reading non-standard data arrangements on the worksheet, shifting header positions and combining/looking up multiple table data. In addition to reading worksheet data, our training covers how to read and write to text files and databases and integrate their information. After discussing data control, our VBA training provides an in depth review of how to use ActiveX controls on worksheets and userforms and program them with VBA. It then begins the grand finale showing how to layout, build and program advanced dashboards on worksheets, charts and userforms providing a full review of how to use events. Pivot and query table automation using VBA is also reviewed. After the dashboard discussion, advanced VBA methods for controlling PowerPoint, Word, and Outlook and interfacing with other languages like VB.Net is covered.

 

Key Excel VBA skills learned:

• A complete review of the Excel VBA language where needed in the class
• Complete review of a class, object, property and method and their VBA syntax rules
• How to make a simple object in a VBA class module
• How to use the CreateObject and GetObject VBA functions to run other class libraries and programs in VBA
• Advanced Excel VBA methods for tracking cells and ranges
• Advanced Excel VBA methods for automatically determining worksheet range sizes and cell locations
• How to build Excel VBA procedures that scan for multiple workbooks, multiple data worksheets and cell information that is not known
• How to use VBA For...Each Next loops and the FileSystem, Folder and File objects to scan for workbooks in folders
• Advanced Excel VBA methods for processing complex worksheet data patterns and fragmented data
• How to use VBA to find and track cell positions on the worksheet
• How to use key Excel VBA functions and worksheet functions in your VBA code that process dates, parse text, find errors, find table headers, summarize data...
• Advanced Excel VBA methods for controlling Excel data tools like 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
• How to use Excel VBA along with the FileSystemObject to control and manage the Windows folder and file system
• Advanced Excel VBA methods for creating VBA procedures that run at a specific time or key combination
• How to control ActveX controls with VBA
• Advanced Excel VBA methods for loading list boxes and combo boxes with data
• How to use userforms, ActiveX controls, graphics and events in Excel VBA to create advanced dashboard interfaces
• How to use the worksheet, ActiveX controls, drawing shapes and events in Excel VBA to create advanced dashboard interfaces
• How to use charts, ActiveX controls and drawing shapes along with Excel VBA to create advanced dashboard interfaces
• How to communicate with VB.NET and use it capabilities in your advanced Excel VBA programs
• Advanced Excel VBA methods for controlling other programs like Microsoft Word, PowerPoint, Outlook (class discussion)

 

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

Using Objects in Excel VBA Programming

• 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
• What is an object and how are they used in Excel VBA programming
• What is a property and how are they used in Excel VBA programming
• What is a method and how are they used in Excel VBA programming
• Learn how to build an object in an Excel class module creating its own properties and methods
• Review of the syntax rules used in VBA code to identify an object and run its properties and methods
• Strategies for using the Set statement to track objects in your VBA code
• Strategies for using VBA For...Each Next loops and logic to find and track objects in your code
• How to use CreateObject and GetObject in your Excel VBA code to access other class libraries and programs

Advanced Excel VBA Methodologies for Controlling Ranges, Data Validation and the Sort/Filter Tools

• Review of how to use the Range, Cells, Columns, Rows, CurrentRegion, SpecialCells, Offset and Find accessory to identify cells and ranges in VBA
• How to combine various Range object accessors like ws.Range(ws.Cells(1,1), ws.Cells(1,1)) to identify various sub ranges within a range
• Advanced Excel VBA programming methods for mapping resizing data ranges
• Advanced ways to use Find and Offset in VBA to find and lock in on cell information without knowing exact locations (great for modeling and data processing)
• How to use cell and range names in your Excel VBA code to track vital data and model information
• How to use cell and range names in your Excel VBA code to designate output areas on worksheets
• Using VBA to track and automatically adjust cell and range names (great for modeling and data processing)
• How to use Excel VBA to apply number formats, fonts, alignments, borders and colors... to complex range configurations
• How to use VBA to determine cell and range references and automatically build worksheet formulas using those references (great for modeling and reports)
• How to use Excel VBA to control a cell's Data Validation settings (interface design for both modeling and data processing)
• How to use For Each Next loops and names to build advanced upload and download tools for models
• Using Excel VBA to automate the Autofilter and Advanced Filter tools
• Using Excel VBA to automate the old and new Sort tools

Controlling Workbooks and Worksheets Using Excel VBA

• 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 a sophisticated 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 to add, move and rename worksheets
• 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

Advanced Excel VBA Data Analysis Techniques for Worksheet Data

• Reading data efficiently, the key to developing high-end data analysis and dashboard displays
• Creating proper tables on worksheets, in text files and in databases that VBA, ADO and SQL can read
• Bringing ranges into your Excel VBA code as arrays and processing using VBA For...Next loops and For...Each Next loops
• Advanced VBA programming techniques for looping through VBA arrays and syncing their output with the range they came from
• Using the IS functions to determine if your worksheet data being read has errors
• Using the 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 (necessary for dashboard design)

Day-2

Advanced Excel VBA Data Analysis Techniques for Worksheet Data (Cont.)

• Using the VBA For...Next loop and the Cells accessor 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, current value,...)
• Advanced Excel VBA methods for using the MATCH and COUNTIF worksheet functions along with the Find and Offset accessors to find headers and other data on a worksheet
• Advanced  Excel VBA programming techniques for finding and processing data on multiple worksheets and in multiple workbooks
• Advanced Excel VBA programming techniques for using the Sort 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 worksheet tables
• How to use For...Next loops, logic 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 to detect file and folder conflicts
• 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 Use Certain Application Methods to Control VBA Procedures

• 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 and add-ins

Controlling ActiveX Controls with VBA

• When to Use ActiveX controls
• The difference between Form controls and ActiveX controls
• Where can ActiveX controls be used
• Review of the different types of ActiveX controls in Excel: text boxes, combo boxes, list boxes, spin buttons, scroll bars, check boxes, labels...
• How to create and edit ActiveX controls on the worksheet and on userforms
• Different ways to use ActiveX controls on the worksheet and on userforms (cell reference links and VBA)
• Overview of how to control ActiveX controls with VBA on both userforms and on worksheets
• 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
• Using Excel VBA to set scroll bars and spin buttons
• Advanced Excel VBA techniques for controlling text boxes and error checking their data
• How to create multi-select list boxes in Excel VBA
• 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
• Using Excel VBA to add controls on both worksheets and userforms

Day-3

Developing Advanced Dashboard Interfaces Using Userforms

• What is a VBA userform and how to them as a dashboard interface
• What is the difference between a modal and non-modal userform in VBA
• How to create, arrange and interact ActiveX controls along with graphics on userforms
• 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 create a list box on a userform in VBA that fills in another list box using events
• 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 Dashboard Interfaces Using the Excel Worksheet

• Ways to look at and configure the worksheet as a dashboard interface
• How to create event procedures that interact with a user's worksheet actions like typing, clicking, double clicking and right-mouse clicking
• Developing the Excel VBA logic to handle these worksheet event procedures and make them fail safe
• Turning Excel's drawing shapes into a visual interface
• How to use Excel VBA to add, position, format and delete drawing shapes and pictures on the worksheet
• Using Excel VBA to alter the text in drawing shapes turning them into displays
• Using Excel VBA to assign procedures to run when drawing shapes are clicked (i.e. buttons)
• Using VBA to read the name of the drawing shape that was clicked (key to picture click interfaces)
• How to name drawing shapes, pictures,... so their names can be used in VBA logic
• Excel VBA techniques for controlling advanced Pivot Tables and Pivot Charts
• Excel VBA techniques for controlling Query tables on the worksheet

Developing Advanced Dashboard Interfaces with Charts

• Ways to use charts as a dashboard interface
• Review of adding and formatting charts using Excel VBA
• Using Excel VBA to add, edit and delete a chart's chart series
• Using Excel VBA to assign VBA arrays to a chart series instead of ranges
• How to write Excel VBA code that relates cell data position on the worksheet to series points (used in formatting specific points and ActiveX controls)
• 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...)
• Integrating ActiveX controls with charts in VBA to build interactive displays
• Using chart events in VBA to build charts that react to user action

Excel VBA Strategies for Controlling Word, PowerPoint and Outlook

• 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

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
• How to automate outlook to read incoming emails and send emails