Intermediate Microsoft Excel VBA Training

Our 2-day hands-on live intermediate Excel VBA training for engineers and scientists focuses on how to turn Excel into an innovative data processing, data fusion and data visualization tool for engineering and science data. Our Excel VBA training starts off with showing you unique ways to control Excel cells, ranges and data tools in Excel VBA and how these elements can be used together to create high end data fusion and data analysis tools. Our Excel VBA training then focuses on how to use various loops, Excel worksheet/VBA functions, arrays and logical techniques to analyze technical data with a focus on reading unconventional worksheet data layouts, re-sizing moving data areas and locating hard to find information on a worksheet. Our training then discusses sophisticated data fusion techniques using VBA loops and logic to sift through data in multiple workbooks and worksheets from multiple folders. The data fusion discussion also covers methods for reading and writing to text files and databases and fusing that information together. Also covered is various data visualization methods showing how to use Excel VBA to visualize your collected engineering/science data in various worksheet table configurations, colored cells, drawing shapes, pictures and charts.

Key Excel VBA skills learned:

• A review of key VBA language elements during the training including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling
• Full object, property and method review, how to use them and their syntax rules in VBA code
• A full review of key of how to use Excel worksheet and VBA functions in your code that sum and count values; check for errors; lookup information;, clean and process text; and process dates and times
• Advanced methods to identify cells, ranges, columns and rows in VBA and control them
• Using Excel VBA to locate and track hard to find data on the worksheet
• How to automate key Microsoft Excel data tools (AutoFilter, Advanced Filter, sort, Excel Tables, Microsoft Query and Pivot Tables) in VBA that are key to data fusion and data analysis techniques
• Automating key Excel VBA data operations that are used in data fusion like sorting, filtering, finding, copying, pasting, inserting and deleting
• How to use Excel VBA to control key Microsoft Excel objects that relate to data fusion and data visualization in Microsoft Excel
• Using VBA to control the Windows folder and file system
• How to use VBA to control other programs
• How to use Excel and VBA to open, scan and process data in text files including hard to read text files
• Using Excel VBA to access data stored on multiple worksheets in multiple workbooks
• Using VBA ADO, DAO and SQL to access and control databases
• Key strategies and methodologies for creating data fusion tools in VBA
• How to use Excel VBA to control worksheets, charts and drawing shapes and turn them into data visualization tools

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

• Written a procedure in VBA
• Used variables, loops, functions, declarations,... in some capacity in VBA
• Used and written basic logic in VBA
• Used Microsoft Excel's data tools manually and know them operationally
• Formatted worksheets and know Excel's basic formatting capabilities
• Have used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...

Day-1

Using Objects in Excel VBA

• A review of critical VBA programming elements during the class including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling conventions
• Definition of an object, how to use one in programming and the syntax rules for using them in your VBA code
• Definition of a property, how to use them in programming and the syntax rules for running them in your VBA code
• Definition of a method, how to use them in programming and the syntax rules for running them in your VBA code
• How to construct object expressions using accessors in your VBA code
• How to use the Set statement in VBA to track objects
• How to use the VBA For...Each Next loop to loop through object collections and track individual objects

Review of Key Excel Worksheet and VBA Functions Commonly Used in Data Fusion, Data Analysis and Data Visualization

• Full syntax review of how to run Excel and VBA functions in your VBA code
• Using the SUMIF, COUNTIF, SUMIFS and COUNTIFS worksheet functions in Excel VBA to sum and count technical data
• Review of using the imaginary and engineering functions in Excel VBA to crunch data
• How to perform table lookups in Excel VBA using MATCH, COUNTIF, VLOOKUP, SUMIFS
• How to use the COUNTIF worksheet function in Excel VBA to test for data existence
• Using the IS VBA and Excel worksheet functions to detect errors in your data
• Using the Left, Right, Mid and Len VBA functions to break text apart in VBA
• Using the TRIM, CLEAN, Replace and Lcase functions in Excel VBA to condition text for use in logic
• How to test for a subtext within a piece of text using the Instr VBA function
• Using the Split VBA function to break data apart based on a specific delimiter
• How to use the C VBA functions (Cdbl, Cdate,...) to flip data types for use in logic and expressions
• How to determine date elements in Excel VBA using the Month, Day, Year and Format VBA functions
• Using DateAdd and DateDiff in VBA to add or subtract days, months, years...from a specific date
• How to use string concatenation to create and modify dates in your VBA code
• How to use the VBA For...Next Loop and DateAdd, DateDiff and Format VBA functions to create automated calendars

Key Methods for Tracking Excel Ranges in Your VBA Code for Data Fusion, Data Analysis and Data Visualization Purposes

• Why do you need to know how to assemble multiple Range accessors together in VBA code to isolate data on a worksheet
• Review of using the Cells, Rows, Columns and Range accessors in your Excel VBA code
• How to isolate parts of a range within another range in your VBA code by assembling multiple Range accessors together in an object expression
• Using the Find accessor to locate hard to find data on a worksheet
• Unique ways of using the Offset accessor to locate data in cells around cells, great for modeling and multi-row data headers
• Using the CurrentRegion accessor in your VBA code to track a range of unknown size
• Alternate ways of tracking ranges that change size in your VBA code
• Clever ways to use range names in your VBA code to track data and flag worksheet positions

Using Excel VBA to Control Key Excel Operations and Tools Vital to Data Fusion, Data Analysis and Data Visualization

• Various high-end Excel VBA methods for reading and writing to ranges (single cell values, ranges...)
• Various high-end Excel VBA methods for coping and pasting worksheet data (entire sheets, specific table sections, rows, columns...)
• Worksheet data management (inserting, deleting, and moving rows, columns, cells)
• Using Excel VBA to control formatting for cells and various range configurations (fonts, colors, borders, number formats and alignments)
• Using Excel VBA to manage Excel tables and queries
• Strategies and methods for using Excel VBA to control Excel's filter tools like AutoFilter, Advanced Filter and Remove Duplicates
• Using Excel VBA to control the Sort, Text-to-Columns and grouping tools
• Using Excel VBA to create, edit and delete formulas and to determine their cell and range references
• Strategies and methods for using Excel VBA to control cell and range names
• Various high-end Excel VBA methods for adding and controlling Pivot Tables
• How to design For...Next and For...Each Next loops that use these operations and tools

Methods for Using VBA Arrays and For...Next Loops to Perform Advanced Data Analysis

• Benefits of using the VBA For...Next loop to analyze worksheet data
• Strategies and methods for designing VBA For...Next loops and logic to analyze worksheet data arranged in various patterns (stop and go data, stacked tables, oddly grouped data....)
• Strategies and methods for designing multiple counters and logic to track start and stop positions of data inside VBA For...Next loops
• Strategies and methods for designing VBA For...Next loops and logic that deal with shifting header names, column positions and multi-row headers in your data
• How to declare, write to, and read arrays in VBA
• How to create dynamic arrays in VBA
• How to use Excel worksheet and VBA functions that generate arrays in your VBA code
• How to read worksheet ranges as arrays in VBA and process them using For...Next loops
• How to use Excel VBA to build report worksheets
• How to use VBA For...next loops to build worksheets from table values

Day-2

Controlling the Windows Folder and File Systems, Registry; and Other Programs Using VBA

• How do these programming elements time into data visualization, analysis and fusion tools
• Strategies and methods for controlling other programs and libraries in VBA with early (tools/reference) and late binding (CreateObject, GetObject)
• Using the VBA Object Browser and on-line help to determine the object hierarchy of another program
• Why store program information and settings in the Windows Registry
• How to use VBA to store and retrieve information in the Windows Registry
• Delimiting multiple pieces of program information when storing in the Windows Registry
• Using the FileSystemObject in VBA to move, create, delete and rename Windows folders and files
• Using VBA, the FileSystemObject and logic to detect if program files and folders exist
• Strategies and methods for labeling files for use in a data fusion environment
• Using the File and Folder objects in VBA to determine folder and file names
• Overview of using VBA For...Each Next loops to loop through folders and files
• How to deconstruct folder and file names using the Split and Instr VBA text functions

 Data Fusion Methods in VBA for Looping Through Workbooks and Worksheets

• What is data fusion, how can it be applied to worksheets and workbooks, and how to implement it using Excel VBA
• Data fusion strategies for naming and reading workbook files in a data fusion environment
• Benefits of using the Set statement to track workbooks in VBA when you open them
• How to use Excel VBA to automate the Excel Open dialog box turning it into a file selector
• Using Excel VBA to name and save workbooks to different folders and closing them
• Using VBA For...Each Next loops, various Excel methods and properties, and logic to loop through open workbooks and their worksheets looking for data
• Using VBA For...Each Next and For...Next loops, various Excel methods and properties, and logic to loop through workbooks in folders, open them and scan their worksheets looking for data

Data Fusion Purposes in VBA to Control Text Files

• VBA data fusion strategies for storing data in multiple text files
• VBA strategies for naming and reading text files in a data fusion environment
• Using VBA and the TextStream object to open and close text files
• How to read and write to a text file using VBA
• Using VBA Do loops, logic and functions to scan large and small text files for key information and transfer that data to the worksheet
• How to clean misaligned degraded text files and parse their information using Excel VBA

Data Fusion Methods in VBA to Control Databases

• Data fusion strategies for using multiple databases along with Excel based data
• SQL language review
• How to use string concatenation and loops to assemble a SQL statement in your VBA code
• How to use DAO to control Microsoft Access databases
• How to use ADO to control SQL driven databases
• How to use ADO to query and process data in workbooks and text files

How to Assemble and Use Excel VBA to Control Charts, Drawing Shapes and the Worksheet for Data Visualization Purposes

• How to automatically build embedded charts and chart sheets using Excel VBA
• How to use VBA to size and place embedded charts on the worksheet
• How to add and change a chart's series using Excel VBA (ranges and arrays)
• How to use VBA including For...Each Next loops to format various chart elements
• How to use VBA to add and control Excel drawing shapes, their format and their text
• Strategies for laying out visual display areas on the worksheet so VBA can fill them in
• How to assemble and use these elements to make data visualization tools for data fusion programs