Advanced Microsoft Excel VBA Training >
Advanced Excel VBA Training for General Audience
Introduction
Our 3-day hands-on advanced Excel VBA training shows you how to build UI driven data processing, analysis, and dashboard tools by using VBA with worksheets, formulas, Excel's data tools, userforms, ActiveX controls,
shapes, charts, events, and databases emphasizing adaptability and upgradeability.
Day-1
Excel VBA Language, Object, and Error Trap Review (Discussed Throughout Class)
• A review of all major VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions where needed
• Review of an object, property, and method and how are they used in Excel VBA programming
• Advanced ways to use the debugger when debugging your VBA code
• Syntax rules used in VBA code to identify an object and run its properties and methods
• Advanced strategies for handling run-time errors in your code using error handlers and logic
• Advanced strategies for using the Set statement to track objects in your VBA code
• How to create objects in Excel using Class modules, Subs, Functions, Properties, and public variables
• How to use the CreateObject and GetObject VBA functions in your code to access other class libraries and programs
Processing and Formatting Advanced Data Sets on the Worksheet
• Review of basic Excel VBA data processing methods, loops, and logic
• Review of using worksheet and VBA functions in your VBA code to process worksheet data
• Review of table design on the worksheet for large scale data storage
• Review of using the Cells, Range, Rows, Columns, CurrentRegion, SpecialCells, and Find properties to lock onto complex worksheet data configurations
• How to use VBA to parse, interpret, and fix worksheet text data using the TRIM, CLEAN, Left, Right, Mid, Replace, Instr, Split... functions
• Parsing and merging date / time values in your VBA code using the Day, Month, Year, DateDiff, DateAdd, DatePart, Weekday, Format, WEEKNUM, NETWORKDAYS, EOMONTH... functions
• How to use VBA loops, logic, and functions to analyze complex worksheet table patterns like stacked tables, table start / stop points, fragmented tables, indented tables, double row header tables...
• Scanning for and deleting multiple rows and columns from worksheet tables using advanced Excel VBA strategies
• VBA techniques for inserting / deleting data and formulas in worksheet table columns and rows
• How to use VBA to lock onto various worksheet data configurations and use them in your code with the statistical, math, and lookup worksheet functions
• VBA strategies for finding max, min... and starting / end points in complex data sets
• Using Excel VBA to build summary tables from complex data patterns and perform advanced table lookups
• Importing a worksheet range into VBA as an array to speed up your processing time
Using VBA to Create Advanced Excel Charts and Chart Report Sheets
• Review of how to create chart sheets and embedded charts using Excel VBA
• Review of advanced chart formatting methods using Excel VBA
• 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...)
• Using Excel VBA to add, edit and delete an existing chart's data series
• How to use Excel VBA to create a chart series using arrays instead of worksheet ranges, great for emailed reports
• Creating combo charts using Excel VBA, ranges, and arrays
• Creating chart limit lines and curve fits using Excel VBA
• Combining worksheet cells, charts, and VBA to automatically arrange, align, and format multiple charts on a report worksheet
Searching for Data in Workbooks, Worksheets, and Folders Using VBA
• Why store data in multiple workbooks and use Excel VBA to integrate their data?
• How to use Excel VBA to access and control the Windows folder system using the FileSystemObject
• Using Excel VBA to add, open, save, close, and track workbooks
• How to use VBA, functions, and string concatenation to create workbook names (dates, version...) that can be easily tracked in code
• Scanning and tracking data in open workbooks using VBA For...Each Next loops, functions, and logic
• Using Excel VBA loops and the Remove Duplicates tool to add, move, and rename worksheets in advanced data analysis apps
• Advanced Excel VBA looping strategies for processing data in multiple workbooks / worksheets and text files
Advanced Excel VBA Methods for Controlling Reports and Analysis Problems
• Review of analysis problem layout on the worksheet, interface design, cell naming strategies, and using VBA to control it all
• How to use Excel VBA to control a cell's Data Validation settings
• Review of how to use the Range, Cells, Columns, Rows, CurrentRegion, SpecialCells, Offset, and Find properties and methods to identify cells and ranges in VBA
• How to use cell names, loops, and logic in Excel VBA to track various output areas on a report worksheet and add data to them
• Using Excel VBA to automatically build worksheet reports like revenue / expense reports and create the formulas for calculations like variance...
• How to use VBA loops to build parameter upload, download, and batch processing procedures for analysis problems
• How to use Excel VBA to copy/paste a worksheet formulas to a new worksheet, adjust its formulas, and adjust the formulas on other sheets to account for the new sheet addition
Day-2
Developing Advanced User Interfaces (UI's) With VBA Userforms, ActiveX Controls, Images, and Events
• What is a VBA userform and how to use them in your different Excel user interfaces?
• What is the difference between a modal and non-modal userform in VBA?
• How to create and layout ActiveX controls and pictures on userforms
• How to create event procedures for ActiveX controls on userforms
• Controlling text boxes, scroll bars, spin buttons, check boxes, option buttons, and image controls on userforms with VBA
• 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 Remove Duplicates 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 methods for formatting data inside of list boxes and combo boxes
• How to 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
• Designing VBA procedures that launch and preset ActiveX controls values on userforms
• How to create ActiveX control events in your Excel VBA code that trap keystrokes on a userform
Using the Ribbon, Custom Toolbars, File / Folder Pickers, and the Windows Registry In Your UI's
• How to use Excel VBA to build a file / folder picker that steers VBA to the right file or folder to use
• How to create a Ribbon tab, its controls, and assign them VBA procedures in an Excel workbook file
• Using userforms to quickly create floating toolbars that run your Excel apps
• How to use the OnTime and OnKey methods in VBA to create procedures that run at a specific time or by pressing shortcut keys
• How to use the Run method in VBA to run VBA procedures in other workbooks, add- ins, or call by string
• How to store and retrieve program information in the Windows Registry
Developing Advanced Forms and Project Status Tables Using VBA, ActiveX Controls, Data Validation, and Worksheet Events
• Creating advanced event driven forms on the worksheet using worksheet VBA events that react to user entry, double clicking, right-mouse clicking...
• Writing Excel VBA logic to control worksheet event driven calculations and when they are implemented
• Using the Range, Cells, and Offset properties along with event target cells to determine where to output calculations on a worksheet form
• Using Excel VBA to automatically create and manage a project status table (i.e., Project vs. Region) on the worksheet
• Looking up headers and rows in worksheet status tables using the MATCH and COUNITIF worksheet function in VBA
• Writing Excel VBA loops and logic to process data from multiple workbooks and load them into worksheet status tables
Creating Gantt Charts and Calendar Style Reports Using Shapes, the Worksheet, ActiveX Controls, and Excel VBA
• Creating UI's with ActiveX combo boxes, spinners, scroll bars, and VBA to control what data is viewed in a Gantt chart and calendar
• Creating UI's with Data Validation and VBA to control what data is viewed in a Gantt chart and calendar
• VBA coding strategies for creating times, dates, months, years... in specific worksheet patterns
• Using VBA to automatically format calendar layouts and timelines on the worksheet
• Using string concatenation with the Excel and VBA date functions to assemble dates, months, years, days... from data
• Looking up dates and times on the worksheet using the MATCH and COUNITIF worksheet function in VBA
• Looking up dates and times on the worksheet using the Find method, and the Cells, Row, and Column properties in VBA (alt method)
• Using shapes and Excel VBA to build interactive project timelines on the worksheet
• Assigning VBA procedures to shapes on the fly, figuring out what shape was clicked to run a procedure
• Using VBA loops, logic, and functions to quickly fill in data in worksheet based calendars and timelines
Combining the Worksheet, ActiveX Controls, and Data Validation With VBA to Construct Automated Dashboards
• When to make a new chart or use an existing chart in Excel VBA controlled dashboards?
• Using Excel VBA to create categories from data to display in dashboard ActiveX combo boxes and Data Validation cell drop downs
• Creating UI's with ActiveX combo boxes, spinners, scroll bars, and VBA to control what data is viewed in a dashboard chart
• Creating UI's with Data Validation and VBA to control what data is viewed in a dashboard chart
• Using Excel VBA and ActiveX control selections to extract data subsets from data to display in a chart dashboard
• Using Excel VBA and ActiveX control selections to scan multiple workbooks for data and assemble it into ranges or arrays that can be used by dashboard charts
• Using the workbook Open event to preload dashboard interfaces when a workbook opens
Day-3
Rapid Data Report Construction by Automating Excel's Data Tools With Excel VBA
• Advanced VBA looping and logic strategies for quickly turning Autofilter, Sort, Remove Duplicates, and Advanced Filter into large scale data processing tools
• Using Excel VBA to build report workbooks, build report worksheets, and transfer filtered data
• How to use Excel VBA to control Excel tables and refresh / change their queries
• Creating and controlling PivotTables and PivotCharts with Excel VBA
• How to automate the Analysis Toolpak with VBA
How to Use Excel VBA to Control Text Files and Integrate Their Data With Your Excel Tools
• To automate Text Wizard or run VBA loops, which programming approach to use when integrating text file data with your Excel VBA programs?
• 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 onto a worksheet
• Using Excel VBA to automate the Text to Columns tool to parse worksheet based text data
• How to use VBA to open, read, write, and close text files using the TextStream object
• How to use string concatenation and loops in VBA to assemble text that will be written to text files
• How to use the VBA Do...Loop to access and load text file data onto a worksheet
• Advanced Excel VBA methods for loading text file data on the worksheet using arrays
• Cleaning, parsing, and reorganizing corrupted text file data using various functions, the worksheet, and the Excel Text to Columns tool in Excel VBA
How to Use SQL and ADO to Control and Query Data in Databases, Workbooks, and Text Files
• SQL language review for querying, inserting, updating, and deleting database data
• Building SQL statements and connection strings in VBA using string concatenation, key VBA functions, and loops
• How to use ADO, SQL, and VBA to query and control a database
• How to use ADO, SQL, and VBA to query a workbook and text file
• Advance VBA methods for querying and combining workbook and text file data using SQL and ADO
Controlling Word, PowerPoint, and Outlook With VBA for Report Generation Purposes
• Why does Excel VBA with PowerPoint, Word or 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
• Using Excel VBA to automate Outlook to read incoming emails and send emails
Creating, Packaging, and Distributing Excel Add-Ins for Userform Based Excel Apps
• Designing userform based apps to run from Excel Add-Ins
• Designing Excel Add-Ins to run from the Ribbon or custom toolbars
• Strategies for packaging and distributing Excel Add-Ins to users
