Microsoft Excel VBA Data Analysis Training for Engineers and Scientists

Our 2-day hands-on live Excel VBA data analysis focuses on how to turn Excel into an adaptive data processing, data fusion and data visualization tool for engineering and science data. Key topics include:

• A review of key VBA language elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions
• Using key functions and the Analysis Toolpak in VBA to process engineering / science worksheet data
• Using Excel VBA to process complex engineering and science data patterns on the worksheet
• Using Excel VBA to build, fill, and format engineering / science worksheet tables
• Using Excel VBA to perform worksheet table lookups and create PivotTable reports
• Searching folders, open workbooks, and worksheets for engineering / science data
• Shaping engineering / science worksheet tables with Excel VBA
• Controlling engineering / science text files with Excel VBA
• Querying databases, workbooks, and text files with Excel VBA, SQL, and ADO
• Visualizing engineering / science data with charts using Excel VBA
• Designing User Interfaces (UI's) on the worksheet to control and view your engineering / science data

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

• Used variables, loops, functions, declarations... in VBA
• Used and written basic logic in VBA
• Used Excel objects, properties, and methods in VBA
• Used Microsoft Excel's data tools manually and know them operationally
• Formatted worksheets manually and know Excel's basic formatting capabilities
• Built charts and drawing shapes manually before in Excel
• Used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...

Day-1

Excel VBA Language Review and Coding Strategies for Engineers and Scientists (Discussed Throughout Class)

• A review of critical VBA programming elements used during the class including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions
• Constructing error handlers and logic to handle run-time errors in your VBA data analysis code
• A review of Excel objects, properties, and methods and how to use them in VBA code
• Creating and using object expressions in your VBA code to track objects
• How to use the Set statement in VBA to track workbooks, worksheets, ranges, charts...
• How to use CreateObject and GetObject to control other programs and libraries
• Using the VBA For...Each Next loop to loop through object collections and find individual objects

Using Key Functions and the Analysis Toolpak in VBA to Process Engineering / Science Worksheet Data

• Full syntax review of how to run Excel and VBA functions in your VBA code
• How to identify stationary and changing data ranges inside VBA using combinations of the Range, Cells, Columns, Rows, and CurrentRegion properties
• Using the MAX, MIN, AVERAGE, and COUNTA worksheet functions in VBA to perform a variety of technical  tasks
• Using the SUM, SUMIFS, AVERAGEIFS, MAXIFS, MINIFS, and COUNTIFS worksheet functions in Excel VBA to analyze data
• Using the trig worksheet functions in your code including ATAN, COS, SIN, RADIANS, DEGREES...
• Using the statistical worksheet functions in your VBA code including STDEV.S, SKEW, CONFIDENCE, RANK...
• Parsing and merging date / time values in your VBA code using the Day, Month, Year, DateDiff, DateAdd, DatePart, Weekday, Format, WEEKNUM, NETWORKDAYS, EOMONTH... functions
• Cleaning, parsing, and merging text in your VBA code using the Split, Left, Right, Mid, Replace, Instr, InstrRev, Format, CLEAN, TRIM... functions
• Automating the Analysis Toolpak tools in VBA to analyze worksheet table data

Using Excel VBA to Process Complex Engineering and Science Data Patterns on the Worksheet

• Full review of using VBA For...Next loops to make adaptable Excel VBA programs that process worksheet data
• Using the Cells, Offset, Rows, Columns, Row, Column, CurrentRegion, and Range properties in your For...Next loops to locate cells and ranges
• How to determine table header positions and table sizes in your VBA data procedures, key to adaptable procedure design
• Using VBA loops and logic to determine engineering / science values at specific start or stop points
• Figuring out where your worksheet data enters steady state with VBA loops and logic
• VBA looping and logic strategies for determining data points that are above or below set limits
• Methods for analyzing stacked worksheet table data patterns using Excel VBA loops and logic
• Using VBA to color cells in an engineering / science worksheet table based on logic
• Using cell formats in Excel VBA as logic triggers when analyzing data
• Importing worksheet tables and converting them to VBA arrays for speed runs

 Using Excel VBA to Build, Fill, and Format Engineering / Science Worksheet Tables

• Review of building single and nested loops to access various patterns of engineering / science worksheet table data
• VBA strategies for formatting engineering and science tables on the worksheet
• Using the COUNTIF and MATCH worksheet functions along with Find, Range, and Cells in VBA to locate single and double row table headers
• Using the Find, Row, and Column methods and properties to determine the start cell of a worksheet table
• Using Excel VBA to automatically build worksheet summary tables from engineering / science data
• Building Excel VBA code that reads worksheet data, generates the output workbook/worksheets, and fills them in
• Using VBA to determine a unique value set from a worksheet table column
• Creating Excel VBA loops that iterate through tables on multiple worksheets, find their boundaries, format them, and perform the necessary calculations
• Using VBA loops and logic to read worksheet data sets, build and fill 2-d tables (i.e. Project columns vs. Site rows), and color code the data cells

Using Excel VBA to Perform Worksheet Table Lookups and Create PivotTable Reports

• Using the VLOOKUP and HLOOKUP worksheet functions in your VBA code to perform simple worksheet table lookups
• How to use the MATCH and COUNTIF worksheet functions in your VBA code to perform complex table lookups
• Performing offset worksheet table lookups in VBA using the Find, Offset, Row, Column, and Cells properties and methods
• How to use Excel VBA to isolate a data subset within a larger worksheet data set and lookup values within that range
• How to perform worksheet table interpolation with VBA
• Using Excel VBA loops and logic to fill in table columns with lookup values or lookup formulas
• How to use Excel VBA to create an Excel table on a worksheet
• Creating, modifying, and deleting PivotTables on the worksheet using Excel VBA

Searching Folders, Open Workbooks, and Worksheets for Engineering / Science Data

• Design strategies for storing data in multiple workbooks and then using VBA to combine and fuse the data
• Design strategies for naming workbooks and folders for large scale data storage
• Using the FileDialog object in VBA to construct file and folder pickers
• Using the FileSystemObject object in Excel VBA to create, move, delete, rename, and access Windows folders
• How to open, add, save, and close workbooks using Excel VBA
• Using VBA to lock onto and scan open workbooks and worksheets for engineering / science data
• Excel VBA looping and logic strategies for processing engineering / science data stored on single and multiple worksheets
• How to design VBA loops and logic to loop through workbooks stored in single and multiple Windows folders

Day-2

Shaping Engineering / Science Worksheet Tables With Excel VBA

• Removing unwanted rows and columns from engineering / science tables using Excel VBA
• Inserting rows and columns in tables and then filling them with data and formulas using Excel VBA
• Using VBA to automate the AutoFilter and Advanced Filter tools, copying the filtered data, and pasting it to report worksheets
• Merging or splitting text from worksheet table columns and placing them in new columns using Excel VBA loops and functions
• Using Excel VBA loops and functions to extract specific date / time elements like month, day, and year from table columns and placing them in new columns
• Merging table columns containing date / time elements into valid dates in new columns using Excel VBA loops and functions
• Various VBA strategies for cleaning, trimming, and replacing text in worksheet table columns

Controlling Engineering / Science Text Files With Excel VBA

• Automating the Excel Text Import Wizard tool with VBA to quickly parse and load data onto an Excel worksheet
• Using the FileSystemObject and TextStream objects in VBA to open, close, read, and write to text files
• Writing Excel data to a text file using loops, string concatenation, and the Cells, Columns, Rows, Range, and CurrentRegion properties in VBA
• Using Excel VBA Do loops, logic, and functions to scan and analyze text file data and output the results to an Excel worksheet
• Cleaning, parsing, replacing, and reorganizing corrupted text files using various functions, the worksheet, and the Excel Text to Columns tool in Excel VBA
• Loading text files into VBA arrays and processing them (speed run)
• Designing Excel VBA loops and logic to loop through multiple text files in folders and fuse their data

Querying Databases, Workbooks, and Text Files with Excel VBA, SQL, and ADO

• SQL language review
• How to use string concatenation and loops to assemble a SQL statement in your VBA code
• Using ADO and SQL to query and update databases
• Using VBA, ADO, and SQL to query data in workbook tables and text files
• Using ADO, SQL, and VBA to combine and fuse data stored from multiple data sources

Visualize Engineering / Science Data Using Excel VBA and Charts

• Review of how to use Excel VBA to create chart sheets and embedded worksheet charts
• Loading data into VBA arrays and creating a chart series from them, a must know for emailed chart reports
• How to use VBA to create combo charts for engineering / science data
• Using Excel VBA to create chart limit lines and curve fits
• Excel VBA chart formatting methods including data point conditional formatting and hiding / displaying series
• Excel VBA methods for flagging particular data points with enhanced labels
• How to write Excel VBA code that creates and arranges multiple charts on a report worksheet

Designing User Interfaces (UI's) on the Worksheet to Control and View Your Data

• Laying out user interfaces (UI's) on the worksheet for your data tools
• Using Data Validation to control user entry on the worksheet and construct drop-down lists in your UI's
• Using ActiveX and Form controls to control user inputs in your data tool UI's
• Using shapes, pictures, and VBA to dynamically display data values in your UI's
• Automating Data Validation using Excel VBA
• Controlling and reading ActiveX controls on the worksheet using Excel VBA
• For advanced UI design for data tools also see our advanced Excel VBA...Engr/Sci