Advanced Excel VBA for Business and Industry - Public, Virtual, Onsite, Self-Study

Class syllabus.

View class examples >

Day-1

Finding Ranges, Using Arrays, and Tracking Objects

* Topics below discussed when needed in the class

• Using VBA to track various worksheet range configurations
• Loading and clearing arrays, sequencing arrays, and using ReDim
• Using Set, For...Each Next loops, CreateObject, and GetObject to track objects
• Using the Run method, concatenation, and logic to create macro calls on the fly
• Creating your own objects using Class modules, procedures, and public variables

Using VBA to Control Workbooks, Folders, Files, and the Registry

• Using FileDialog to build a File/Folder picker for your app
• How to use VBA to create, move, copy, and delete folders and files
• How to control your workbooks in VBA using Open, Close, Add, SaveAs...
• Storing and retrieving workbook data in a multi-user, high demand environment
• Why store your program settings in the Windows Registry and not in your code workbook?
• Using GetSetting, SaveSetting, and DeleteSetting to write, read, and delete settings in the registry

Advanced Report Automation and Formatting Using Excel VBA

• Determining where worksheet tables start and stop using Range, CurrentRegion, Find...
• Looping Excel's data tools (AutoFiter, Sort, PivotTables...) to process large worksheet datasets
• Using VBA to copy, paste, and stack filtered data in different report worksheets
• Using Remove Duplicates and VBA to determine report sheet names from data
• Using loops, logic, and formulas to create summary rows / columns for report worksheets
• Using macros to format your worksheet tables and remove columns / rows

Processing Complex Worksheet Data Patterns with Excel VBA

• Processing bank, quick books, inventory... worksheet downloads with VBA
• Using the Math, Statistical, and Lookup functions in VBA to analyze worksheet data
• Inserting worksheet rows and columns in your data and filling them with formulas or values
• Using VBA and functions to perform table lookups and fill worksheet rows  / columns
• Using loops and logic to delete and move worksheet data rows, cells, and columns
• Using VBA to read data and output it to various worksheet table configurations and forms

Advanced Chart Design and Automation

• Combining charts, shapes, and Conditional Formatting to make visual displays
• How to modify chart data ranges and assign data subsets to charts with VBA
• Using macros to create and format charts like xy scatter, column, doughnut, combo...
• Using VBA to create chart limit lines, color data points, hide /display series, build curve fits...
• Adding multiple tables to a chart using VBA
• Using VBA to create and arrange multiple charts on a worksheet

 Advanced VBA Methods for Creating and Reading Text Files

• Automating Text Wizard in Excel VBA to load text data on the worksheet
• Using the TextStream object in Excel VBA to open, read, write, modify, and close text files
• Parsing, stacking, and processing worksheet text data with VBA
• How to use loops and string concatenation to assemble and write text data to text files
• How to use loops, logic, and arrays to access and load specific text file data onto a worksheet
• Using macros to clean and parse text file data using functions and Text to Columns

Day-2

Creating Advanced User Interfaces on the Worksheet

• Creating and positioning ActiveX controls on the worksheet
• Combining Data Validation and VBA to create advanced worksheet UIs
• Using worksheet Object modules to create ActiveX control events (Click, Change...)
• Controlling and reading worksheet ActiveX controls with VBA
• Using worksheet, workbook, and application events to run your UIs
• Running worksheet UI procedures at specific times using the OnTime method
• Using VBA, shapes, cells, and formats to build UI displays
• Combining your worksheet UIs with your project

Developing Advanced User Interfaces with VBA Userforms

• How to create userforms and place ActiveX controls, pictures, and text on them
• Launching and setting userforms with VBA; using modal vs. non-modal userforms
• Designing event procedures for userform ActiveX controls
• Controlling and reading userform ActiveX controls with Excel VBA
• Using Excel VBA to load and format list and combo boxes
• Designing a list or combo box to fill another list or combo box
• How to control multi-column and multi-select list boxes in your code
• Creating a Ribbon tab and its controls to run a userform
• How to turn your userform app into an Excel Add-In

Advanced Modeling and Problem Analysis with Excel VBA

• Filling downloaded data in model tables with VBA
• Controlling Excel tables and range names with Excel VBA
• How to use VBA to manage problem formulas in complex table configurations
• Calculating worksheet data in VBA using worksheet and VBA functions
• Using loops, logic, and names to collect multi-worksheet information, calculate it, and output it
• Using Excel VBA to copy worksheets, modify their formulas, and tie them into summary sheets
• Using names, logic, loops, and formulas to track and build models on the worksheet

Searching for Data in Multiple Workbooks, Worksheets, and Folders Using VBA

• Design strategies for naming workbooks and folders for large scale data storage and access
• Scanning open workbooks for a specific data set using For...Each Next loops, logic, and functions
• Searching multiple worksheets for a specific data set using For...Each Next loops, logic, and functions
• Accessing multiple folders for specific data workbooks using For...Each Next loops, logic, and functions

Day-3

Constructing Advanced Status and Calendar Tools with Excel VBA

• Why use the worksheet to build status and calendar tools?
• Looking up table headers in your code using MATCH, COUNITIF, Find...
• Simple worksheet strategies to track project status info in team workbooks
• Writing loops and logic to process project data from multiple workbooks
• Writing loops and logic to fill and color the cell data in your status dashboard
• Using Excel VBA to create and fill monthly and 2-week calendars with data
• Outputting timelines to the worksheet, making calendar controls

Assembling Advanced Worksheet Dashboards Using Excel VBA

• Review of the various dashboard types, metrics, benchmarks, and KPIs
• How to combine charts, shapes, and VBA to create various dashboard gages
• Using Data Validation, ActiveX controls, cells, and VBA to build dashboard controls
• Using VBA to create, write to, format, and position shapes and pictures on the worksheet
• Assigning macros to shapes and pictures on the fly with VBA
• How to turn shapes into hi-tech dashboard displays (i.e. timelines, factory layouts...)
• Creating various dashboard displays using VBA, cells, and formats

Using SQL, ADO, and UIs to Control Databases, Workbooks, and Text Files

• SQL language review for querying, inserting, updating, and deleting data
• How to automatically build SQL statements and connection strings in VBA
• How to use ADO and SQL to update, insert, delete, and query database data
• How to use ADO and SQL to query a worksheet table or text file and return a record set
• Combining ADO, SQL, loops, and functions to integrate data from different data sources
• Automating Power Query and Microsoft Query to query / integrate data from multiple sources
• Developing UIs to control database tasks

Prerequisites for our Advanced Excel VBA training.

• Used the Excel VBA language before
• Used variables, loops, and logic before and know how they basically operate
• Used objects, properties, and methods before in code to control a program
• Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM, MATCH, COUNTIF, COUNTA...
• Used Excel drawing shapes and built charts manually
• Used Excel's data processing tools like Autofilter, Sort, Remove Duplicates, and PivotTables

Class time, manual, and repeats.

• Audience:   Business, government, industry, military, engineering, science, techs...
• Duration:   9:00 - 5:00 CT
• # of Days:   3-days
• Instruction Type:   Live, hands-on
• Class Manual:  Our complete Excel manual for business and industry (TCEM).  Details >
• Excel Versions:   2016-Present, Microsoft Excel 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >

Contact us about the class.

Phone Number: 1.629.325.9937

Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT

You can email us at info@emagenit.com >