Advanced Excel VBA Methods for Engineers / Scientists Training

Detailed training syllabus.

How we run the class: We focus our advanced Excel VBA training on what our customers need. When training begins, we analyze those needs and shift our advanced Excel VBA training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Day-1

Full Excel VBA Language, Object, and Error Trap Review for Engineers and Scientists (Discussed Throughout Class)

A complete review of the VBA language including variables, data types, constants, operators, expressions, loops, logic structures, string concatenation, and calling conventions
A complete review of VBA arrays including adjustable arrays, the ArrayList object, and the Array VBA function
Advanced VBA Editor methods for debugging and viewing your code
A review of objects, properties and methods and how are they used to control a program
Advanced Excel VBA strategies for using the Set statement to track objects in your VBA code
Advanced Excel VBA strategies for handling run-time errors using logic and error traps
How to use CreateObject and GetObject to command other programs and class libraries
How to create objects in Excel using Class modules, Subs, Functions, Properties, and public variables

Processing Complex Worksheet Data Patterns and Outputting to Different Worksheets / Workbooks

Review of basic Excel VBA data processing methods, loops, and logic
Using worksheet and VBA functions in your VBA code to process data
Proper worksheet table design for engineers and scientists; 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
How to use VBA loops, logic, and functions to analyze complex worksheet table patterns like stacked tables, fragmented tables, indented tables, double row headers...
Advanced VBA techniques for inserting / deleting data and formulas in worksheet tables
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
Advanced 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
How to import a range as an array in your Excel VBA code to speed up data processing
How to output your results to different workbooks and worksheets

Searching for Data in Folders, Workbooks, and Worksheets Then Processing It

How to use Excel VBA to access and control the Windows folder system using the FileSystemObject
How to use Excel VBA to create, move, copy, delete, and rename folders and files
Review of using Excel VBA to add, open, close, and save workbook files
How to use VBA For...Each Next loops to loop through data workbooks or text files in folders
Various VBA logic strategies for detecting data in a workbook
Using Excel VBA loops and the Remove Duplicates tool to add, move, and rename worksheets in data analysis apps
Advanced Excel VBA looping strategies for processing data in multiple workbooks / worksheets

Excel Chart Automation and Report Workbook Generation

Review of how to use Excel VBA to create chart sheets and embedded worksheet charts
Using VBA arrays to create a chart series
How engineers and scientists can use Excel VBA to create combo charts
Using Excel VBA to create chart limit lines and curve fits
Advanced Excel VBA formatting methods including data point conditional formatting and hiding / displaying series
Using Excel VBA to move a design point or analysis line within a chart
Using Excel VBA to create and arrange multiple charts on worksheets in a report workbook

How to Use Excel VBA to Control Models, Analysis Problems, Trade Studies, and Solver

Review of model / analysis problem layout on the worksheet (ActiveX control integration discussed below)
Using Excel VBA to control cell names and formula construction on the worksheet
Design strategies for problems that use multiple workbooks / worksheets that integrate teams
How to use the VBA loops to build parameter upload, download, and batch processing procedures
How engineers and scientists can perform trade studies using Excel VBA and the worksheet
Using Excel VBA to automate Solver and its settings
Using Excel VBA to automate Solver and its answers inside a trade study loop

Day-2

Advanced User Interface (UI) Construction Using VBA Userforms, ActiveX, and Events

How to use userforms and ActiveX controls to build model, analysis, and data processing UI's
Creating modal and non-modal userforms in VBA, when is each type used?
How to create VBA procedures and logic that launch VBA userforms for viewing
How to create and layout ActiveX controls and pictures on userforms
How to create events for ActiveX controls on userforms
Using VBA to control text box, scroll bar, spin button, check box, option button, list box, and combo box controls on userforms
VBA methods for formatting data inside of list boxes and combo boxes
Loading table data into list boxes and combo boxes using worksheet ranges, VBA arrays, ArrayList object, and other Excel data tools like Sort and Remove Duplicates
How to use Excel VBA to read multi-column list boxes and combo boxes
Creating multi-select list boxes on userforms and commanding them with 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

Advanced Worksheet Based User Interface (UI) Construction Using ActiveX, Events, and Cells

How to use the worksheet, events, ActiveX controls and Data Validation to build model, analysis, and data processing UI's
How to use Excel VBA to control ActiveX controls on the worksheet
Using Excel VBA to control Data Validation on the worksheet
Creating events that respond to ActiveX controls on the worksheet
Constructing interfaces that respond to VBA worksheet events like typing, clicking, double clicking, and right-mouse clicking
Using Workbook level events like Open, Close, Save... to control your worksheet based interfaces
Designing advanced VBA logic to control worksheet events and how they execute
Using Excel VBA, events, and ActiveX controls to control charts
Using the Image ActiveX control and events on the worksheet to create chart digitizers

Automating Technical Illustrations on the Worksheet Using Excel VBA

How to use Excel VBA to add, edit, delete, hide, resize, and position shapes and pictures on the worksheet
Overview of how to use names to track shapes and pictures on the worksheet
Using the worksheet as a grid to position engineering / science diagrams
Using the Freeform shape, Line shape, Trigonometry, and VBA to draw advanced engineering / science diagrams on the worksheet
Controlling shape text and formats with Excel VBA
Assigning Sub procedures to shapes and pictures with VBA, key to advanced UI development
Determining what shape was clicked inside a procedure using VBA, key to advanced UI development
Using Excel VBA, functions, shapes, and the worksheet to create project management / timeline tools

Using the Ribbon, Custom Toolbars, File / Folder Pickers, and the Windows Registry in Advanced Excel UI's

How to use Excel VBA to build a file / folder picker that steers VBA to the right file or folder
How to create a Ribbon tab, its controls, and assign them VBA procedures in an Excel workbook file
Using VBA 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

Day-3

Automating Excel's Data Tools with Excel VBA (Rapid Report Generation)

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 with Excel VBA
How to automate the Analysis Toolpak with VBA

Using SQL, ADO, and VBA to Control and Query Databases, Text files, and Workbooks

Various Excel VBA methods for processing data in text files
How to use Excel VBA to open, read, write, and close text files
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 different string concatenation techniques in Excel VBA to assemble text file data
Using advanced VBA string concatenation to stack data in text files for user interface purposes
How to use VBA to extract data from large text files (100,000 + rows of data)
Basic SQL language review
How to use string concatenation and loops to assemble a SQL statement in your VBA code
How to use ADO to control and query SQL driven databases, text files and worksheet tables

How to Create Advanced Report Tools Using Excel VBA, PowerPoint, Word, and Outlook

The basics of using Excel VBA to automate Microsoft Word
The basics of using Excel VBA to automate PowerPoint
The basics of using Excel VBA to automate Microsoft Outlook

Advanced Custom Worksheet Function (UDF) Design for Models and Analysis Problems

Review of creating and running a UDF from the worksheet
How to create cell, range, value, and array arguments for UDF's
Design strategies for UDF's that access other programs and data sources
Construction strategies for UDF's that calculate advanced equations, calculate numerical methods, implement advanced logic, and gather data
Design strategies for developing system model UDF's that accept large amounts of parameter information

Excel Add-In Design for Userform Based Apps and UDF Design / Analysis Libraries

Designing userform based apps to run from Excel Add-Ins
Designing Excel Add-Ins to run from the Ribbon or custom toolbars
Creating UDF library add-ins for worksheet based rapid design and analysis environments

Additonal Classes:

Free Repeats for Our Training

Learn More >

US Military and DOD Discounts