Excel VBA Data Analysis for Engineers and Scientists Training

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:

• Complete Excel VBA language and coding strategies review More >
• Using key functions and the Analysis Toolpak in VBA to process engineering / science worksheet data More >
• Using Excel VBA to process complex engineering and science data patterns on the worksheet More >
• Using Excel VBA to build, fill, and format engineering / science worksheet tables More >
• Using Excel VBA to perform worksheet table lookups and create PivotTable reports More >
• Searching folders, open workbooks, and worksheets for engineering / science data More >
• Shaping engineering / science worksheet tables with Excel VBA More >
• Controlling engineering / science text files with Excel VBA More >
• Querying databases, workbooks, and text files with Excel VBA, SQL, and ADO More >
• Visualizing engineering / science data with charts using Excel VBA More >
• Designing User Interfaces (UI's) on the worksheet to control and view your engineering / science data More >

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) Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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 Back >

• 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