Advanced Excel VBA for Business & Industry

Some key topics we cover in class.

• Review of the VBA Editor, Excel VBA language, debugging, and error traps
• Controlling the Windows folder system to store report and data workbooks
• Advanced data processing methods using VBA, Excel's data tools, and PivotTables
• Advanced worksheet data processing and report generation using Excel VBA loops and logic
• Processing data in multiple folders, multiple workbooks, and on multiple worksheets
• How to use VBA to build / format charts and layout multiple charts on a worksheet (chart report)
• Using Excel VBA to create, write, read, parse, and clean text files
• Creating UIs for your Excel tools using the worksheet, ActiveX controls, Ribbon, and file / folder Pickers
• Creating UIs for your Excel tools using userforms, ActiveX controls, events, and VBA
• Creating advanced dashboards on the worksheet using charts, shapes, events, ActiveX controls, and VBA
• Creating and managing advanced model and analysis problems with Excel VBA
• Using SQL, ADO, and UIs to query and integrate databases, workbooks, and text files
• Using VBA to control Outlook emails and Word / PowerPoint for report generation purposes

Day-1

VBA Editor, Excel VBA Language, and Object Review (Discussed Where Needed)

• Review of VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions
• Advanced use of the VBA Editor windows and debugger
• Handling run-time errors in your VBA code using error handlers and logic
• Using the CreateObject and GetObject functions along with the Set statement to create / track multiple objects in your code
• How to create objects using Class modules, Subs, Functions, Properties, and public variables

Advanced Workbook and Folder Control Using Excel VBA

• Why store data in multiple workbooks and use Excel VBA to integrate their data?
• Using VBA to access and control the Windows folder / file system using FileSystemObject, File, Folder...
• Using Open, Close, Save, Add, and SaveAs to control your workbooks
• Testing for folder and file existence in your macro code using FolderExists, FileExists...
• Using string concatenation in your code to build folder path and file names for your report / data workbooks

Advanced Report Methods Using VBA, Excel's Data Tools, and PivotTables

• Using VBA, loops, and logic to control PivotTables and Pivot Charts
• Using VBA, loops, and logic to control AutoFilter, Sort, Remove Duplicates, and Advanced Filter
• Using string concatenation to build filter logic on the fly inside macro loops
• Using Remove Duplicates in your macro code to filter report worksheet names from your data
• Using loops and logic to build report workbooks / worksheets to store your filtered data
• Using macros to copy, paste, and stack filtered data on report worksheets

Advanced Worksheet Data Processing Using Excel VBA Loops and Logic

• Using Range, Cells, Offset, CurrentRegion, Find, Match... to dynamically track worksheet tables, headers, position, and size
• Looping and logic strategies for processing bank, quick books, inventory, human resources... downloads
• Automatically inserting worksheet rows and columns in your data and filling them with formulas or values
• Performing single and multi-table lookups in your worksheet data and outputting the results to specific data rows and columns
• Using loops and logic to delete and move worksheet data rows, cells, and columns
• Creating and tracking multiple report workbooks / worksheets in your code and outputting data to them in various patterns

VBA Methods for Creating Text Files and Reading Text File Downloads

• Using VBA, loops, and logic to control Text Wizard and parse / stack / process text file data on a worksheet
• Using the FileSystemObject and TextStream object to open, read, write, modify, and close text files
• 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 various functions, the worksheet, and Text to Columns

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

• Searching 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
• Searching multiple folders for specific data workbooks using For...Each Next loops, logic, and functions
• Designing scalable procedures that adapt to  n... number of workbooks, worksheets, text files...

Advanced Chart Report Generation Using Excel VBA

• Review of creating various Excel chart types like pie, line, column, xy scatter, combo... in Excel VBA
• Formatting charts, creating / deleting chart elements, and loading chart templates with VBA
• Using macros to add, edit, and delete an existing chart's data series
• Using VBA arrays to create a chart series, great for emailing chart reports
• Using macros to create chart limit lines and curve fits
• Using macros to layout multiple charts on a report worksheet (i.e., chart report)

Day-2

Using VBA to Store Program Settings in the Windows Registry

• Why store your program settings in the Windows registry and not in your code workbook?
• Using GetSetting, SaveSetting, and DeleteSetting to write and retrieve program settings in the Windows Registry
• How to use delimiters in your code to stack array information in the registry and use Split to retrieve it

 Creating Advanced UIs with Worksheets, Controls, Events, File / Folder Pickers, and Ribbon

• Why create user interfaces (UIs) on the worksheet?
• Creating buttons, shapes, Data Validation, and ActiveX controls on the worksheet to control your Excel VBA tools
• Using macros to control and read ActiveX controls and Data Validation on the worksheet
• How to build various file / folder pickers that steer your macro code to the right file or folder to use
• How to create a Ribbon tab, its controls, and the control event procedures to run your tools
• How to use worksheet events like Calculation, BeforeRightClick, and BeforeDoubleClick to control worksheet UIs

Developing Advanced UIs Using VBA Userforms, ActiveX Controls, and Events

• How to use modal and non-modal userforms to construct advanced UIs
• How to create ActiveX controls and event procedures on userforms and control them with VBA
• Using ranges, VBA arrays, AutoFilter, Sort, ArrayList object, and Remove Duplicates to load data into list boxes and combo boxes
• Using macros to format data inside of userform list boxes and combo boxes
• How to control multi-column and multi-select list boxes and combo boxes in your macro code
• How to create a userform list box that fills in another list box
• Designing procedures that launch userforms and preset their ActiveX control values
• Using userforms and buttons to quickly create floating toolbars that run your Excel VBA tools

Assembling Advanced Worksheet Dashboards Using Excel VBA

• Review of various dashboard types, scorecards, metrics, benchmarks, and KPIs
• How to use charts, shapes, cells, pictures, and VBA to build dashboard displays
• Using Data Validation, ActiveX controls, cells, and VBA to build dashboard controls
• How to turn shapes and pictures into various hi-tech controls for dashboards
• Using VBA to create, format, write to, move, and delete shapes and pictures on the worksheet
• Designing logic in your macro code that reads and implements dashboard UI selections
• Designing macros that gather data and build tables for dashboard charts
• When to use userform based UIs in your dashboards and how to integrate them

Constructing Advanced Project Status and Calendar Tools Using Excel VBA

• Why use workbooks and the worksheet to build project status tools?
• Looking up status table headers and rows in your code using MATCH, COUNITIF, Find, Row, Column...
• Simple worksheet strategies to track project status info in team workbooks
• Writing loops and logic to process project data from multiple workbooks and worksheets
• Writing loops and logic to fill and color the data in your status dashboard
• How to access Microsoft Share Point in your macro code
• Using Excel VBA to create and fill monthly and 2-week calendars with data

Day-3

 Creating and Managing Advanced Model and Analysis Problems with Excel VBA

• Using ActiveX controls, Data Validation, and names on the worksheet to layout your problems
• Using userform UIs to control problem inputs on single and multiple worksheets
• Using VBA to manage cell and range names
• Using Excel VBA to create and edit existing problem formulas based on UI selections
• Using loops and logic to collect problem information, calculate it, and output it to worksheets
• Creating upload and download macros that read and store your problem information in external workbooks
• Using VBA to create worksheets from templates and tie their formulas / information back into the problem

Advanced Report Generation Using Excel VBA

• How to use VBA to add report workbooks / worksheets, name them, and track them
• Using macros to create and track report worksheets from pre-made templates
• Using cell names in your macro code to track worksheet output areas
• Designing loops and logic to build report worksheets like revenue / expense, various balances, inventory...
• Using macros to format, perform calculations, and build formulas on your report worksheets
• Designing loops and logic to fill in worksheet forms

Using SQL, ADO, and UIs to Query / Integrate 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 workbook table or text file and return a recordset to the worksheet
• 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
• Using worksheet and userform based UIs to write SQL, control ADO, and display recordset results

Report Generation Using Word, PowerPoint, Outlook, and Excel VBA

• Why use Excel VBA to automate PowerPoint, Word, and Outlook?
• The basics of automating Word
• The basics of automating PowerPoint
• Using Excel VBA to send and read emails in Outlook

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

• 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

Duration, #days, audience details about our class.

• Audience:   Business, government, industry, military, engineering, science, techs... general audience
• Duration:   9:00 - 5:00 CT
• # of Days:   3-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full TCEM 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
• Repeats:   Repeat your Excel training for a year. Details >
• Personal Examples:   We review our attendees personal examples in our training instruction

If you need to contact us about our class.

Phone Number: 1.805.498.7162

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

You can email us at info@emagenit.com >