Excel VBA Data Processing Training

Detailed training syllabus.

How we run the class: We focus our Excel VBA data training on what our customers need. When training begins, we analyze those needs and shift our Excel VBA data 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

Excel VBA Language Review, Object Tracking Strategies, and Handling Run-Time Errors (Discussed Throughout Class)

• A review of all the major VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions where needed
• How to create error handlers or the logic to handle run-time errors in VBA
• A review of key Excel objects, properties, and methods and how to use them in your VBA code
• VBA design strategies for creating and using object expressions in your code to track objects
• VBA design strategies for using the For...Each Next loop to scan object collections to locate individual objects
• VBA design strategies for using For...Next and Do loops to scan data
• Tracking workbooks, worksheets, ranges, charts... in your VBA code with the Set statement
• Controlling other programs and libraries in VBA using the CreateObject and GetObject functions

Processing Worksheet Data in VBA Using Key VBA Functions and Excel Worksheet Functions

• Running Excel worksheet functions and VBA functions in your code
• Using the Range, Cells, Columns, Rows, and CurrentRegion properties in VBA to identify various table parts (header rows, columns, entire tables...)
• Using the SUM, MAX, MIN, AVERAGE, and COUNTA worksheet functions in VBA to analyze data
• Using the SUMIFS, AVERAGEIFS, MAXIFS, MINIFS, and COUNTIFS worksheet functions in Excel VBA to analyze data
• 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 string concatenation and the Split, Left, Right, Mid, Replace, Instr, InstrRev, Format, CLEAN, TRIM... functions

Shaping (formatting, inserting columns, filtering...) Tables on the Worksheet with Excel VBA

• Removing unwanted rows and columns from worksheet tables using Excel VBA
• Inserting rows and columns in tables and then filling them with data and formulas using VBA
• Using VBA to automate the AutoFilter and Advanced Filter tools, copying the filtered data, and pasting it to report worksheets
• Designing Excel VBA loops that merge or split text in worksheet table columns
• Using VBA loops and functions to extract specific date / time elements like month, day, and year from table columns and placing them in new columns
• Using VBA loops, string concatenation, and functions to combine date / time elements from different table columns into valid dates in new columns
• Using VBA loops and functions to clean, trim, and replace text in worksheet table columns

Using Excel VBA to Process Complex Data Patterns on the Worksheet

• Full review of using VBA For...Next loops to process worksheet data and build adaptable data processing programs
• Using the Cells, Offset, Rows, Columns, Row, Column, CurrentRegion, and Range properties in your For...Next loops to locate cells and ranges
• Figuring out worksheet table header positions using the MATCH and COUNTIF worksheet functions and Row, Column, and Find in VBA
• Analyzing worksheet data between start and stop positions or at data breaks using Excel VBA
• VBA looping and logic strategies for analyzing bank downloads, quick books downloads...
• Analyzing data in stacked worksheet table arrangements using Excel VBA
• Finding data points that are above or below set limits using VBA looping and logic strategies
• Performing calculations between multiple worksheet columns and outputting the results to a new column with Excel VBA
• Scanning data, finding specific breaks in the patterns, and inserting rows, columns, and formulas
• Using VBA loops and logic to flag cells (color, font, border...) in a worksheet table
• Using cell formats like bold, font size, border... as logic triggers when analyzing data
• Importing worksheet tables and converting them to VBA arrays for speed runs

Using Excel VBA to Scan Workbooks, Worksheets, and Folders for Data

• Data fusion strategies for storing data in multiple workbooks and worksheets
• Using the FileSystemObject in Excel VBA to create, move, delete, rename, and access Windows folders
• How to use Excel VBA to open, add, save, and close workbooks
• How to build a workbook file picker using the FileDialog object in Excel VBA
• Using VBA For...Each Next loops, logic, and functions to scan open workbooks and worksheets for data
• Scanning multiple folders looking for specific workbook data using VBA For...Each Next loops, logic, and functions
• Excel VBA looping and logic strategies for processing data stored on single and multiple worksheets

Creating, Filling, and Formatting Worksheet Tables and Forms Using Excel VBA

• Review of building VBA single and nested For...Next loops to access various table patterns on the worksheet
• VBA coding strategies for formatting 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 header positions
• Determining the start cell of a worksheet table using the Cells, Find, Row, and Column properties and methods in Excel VBA
• Using Excel VBA to scan worksheet data and create summary worksheet tables
• Building Excel VBA code that reads worksheet data, generates output workbook/worksheets, and fills them in
• Using Excel VBA to scan for tables on multiple worksheets, map their boundaries, format them, and perform calculations in rows or columns
• How to use VBA loops and logic to read worksheet data sets, build and fill 2-d worksheet tables (i.e. Project columns vs. Site rows...), and color code the data cells

Day-2

Performing Worksheet Table Lookups and Creating PivotTables With Excel VBA

• Using the VLOOKUP, HLOOKUP, XLOOKUP... worksheet functions in your VBA code to perform simple worksheet table lookups
• Performing complex table lookups in VBA using MATCH, COUNTIF, Cells, Find, Row, and Column
• Performing offset worksheet table lookups in VBA using the Find, Offset, Row, Column, and Cells properties and methods
• Isolating a subset of data within a larger worksheet data set and looking for values within that range using Excel VBA
• Using Excel VBA to fill in table columns with lookup values or lookup formulas
• Creating and modifying an Excel worksheet table using Excel VBA
• Using Excel VBA to build, manage, and delete PivotTables

Using Excel VBA to Open, Analyze, and Close Text Files

• Automating the Excel Text Import Wizard tool with VBA to parse and load data onto an Excel worksheet quickly
• How to open and close text files using Excel VBA, the FileSystemObject object, and the TextStream object
• 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, and reorganizing corrupted text files using various functions, the worksheet, and the Excel Text to Columns tool in Excel VBA
• Designing Excel VBA loops and logic to loop through multiple text files in folders and fuse their data
• Loading text file data into VBA arrays and processing it (speed run)

Using ADO and VBA to Query Workbooks, Text Files, and Databases

• Basic review of databases and how they work
• Review of ADO and how it can be used to query different data sources in Excel VBA tying them together
• Basic SQL language review and how it is used for queries and database record control
• How to use ADO to control databases like SQL Server and Microsoft Access
• How to use ADO and SQL to query and process data in workbooks and text files
• Using string concatenation to assemble a SQL statements and ADO connection strings in your VBA code

Visualizing and Reporting Your Data Using Excel Charts and VBA

• Review of creating chart sheets and embedded worksheet charts using Excel VBA
• How to use Excel VBA to format charts including coloring data points based on logic and hiding / displaying series
• How to use VBA arrays to create a chart series, a must know for emailed chart reports
• Creating combo charts (i.e. line / column mix) using Excel VBA
• Creating vertical and horizontal chart limit lines and curve fits using Excel VBA
• Using Excel VBA to create and align multiple charts on a report worksheet

Creating Gantt Chart and Calendar Style Reports Using Shapes, the Worksheet, and Excel VBA

• VBA coding strategies for creating times, dates, months, years... in specific patterns on the worksheet
• 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
• How to lookup dates and times on the worksheet using the MATCH and COUNITIF worksheet function
• How to lookup dates and times on the worksheet using the Find method, and Cells, Row, and Column properties in VBA
• How to use Excel VBA to control Excel shapes, their format, and their text in project timelines
• Using VBA loops, logic, and functions to quickly fill in data in worksheet based calendars and timelines

Creating Various Excel Data Displays Using Shapes, ActiveX Controls, the Worksheet, and Excel VBA

• Using Excel VBA and the worksheet grid to construct and control things like maps, diagrams, various displays.. that display data
• How to use Excel VBA to control and format drawing shapes and pictures for use in these visual displays
• Using Data Validation, ActiveX controls, and VBA to build the interfaces that control your Excel displays

Excel VBA skills needed for the training.

The training details.

• Audience:   Business, government, industry, military,... general audience
• Duration:   8 hrs
• # of Days:   2-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full Excel Manual Included:   Our 2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
• Excel Versions:   2010-2019, Microsoft 365, Windows and Mac
• Free Repeats:   Repeat your Excel training. Details >
• Personal Examples:   We review our attendees personal examples in our training instruction
• Available Training Formats:
  • In Person Training >
  • Live Virtual Learning >
  • Company Onsite >

If you need to contact us about our training.

Phone Number: 1.805.498.7162

Business Hours: 8:30 - 5:00 PM PT

email: question@emagenit.com

Contact us by form.