Advanced Excel VBA for Business and Industry

Detailed training syllabus.

Available: Public >, Virtual >, Onsite >

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

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 PivotCharts
• 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 a Class Beginning Excel Intermediate Excel Advanced Excel Beginning Excel VBA Excel VBA Data Processing Microsoft Excel Dashboards