Microsoft Excel VBA Data Processing Training

Our 2-day hands-on live Excel VBA data training focuses on how to use Excel as a powerful data analysis, data fusion and data visualization tool. Key topics include:

• Excel VBA language review, object tracking strategies, and handling run-time errors (discussed throughout class) More >
• Processing worksheet data in VBA using key VBA functions and Excel worksheet functions More >
• Shaping (formatting, inserting columns, filtering...) tables on the worksheet with Excel VBA More >
• Using Excel VBA to process complex data patterns on the worksheet More >
• Using Excel VBA to scan workbooks, worksheets, and folders for data More >
• Creating, filling, and formatting worksheet tables and forms using Excel VBA More >
• Performing worksheet table lookups and creating PivotTables with Excel VBA More >
• Using Excel VBA to open, analyze, and close text files More >
• Using ADO and VBA to query workbooks, text files, and databases and combine their data More >
• Visualizing and reporting your data using charts and Excel VBA More >
• Creating Gantt chart and calendar style reports using shapes, the worksheet, and Excel VBA More >
• Creating various data displays using shapes, ActiveX controls, the worksheet, and Excel VBA More >

 

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

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

Day-1

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

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

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

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

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

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

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

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

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

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

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

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

• 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