Working Efficiently with VBA in MS Excel Using AI (Copilot, ChatGPT) 2400-SP-EXCEL-AI
The goal of this course is to introduce efficient programming methods in VBA and the effective use of MS Excel’s advanced features to create sophisticated applications, as well as to present methods for using various artificial intelligence (AI) models.
Course topics:
✓ Loops
For … Next, Do … While, Do … Until.
✓ Error Handling
Methods for handling errors and preventing them from occurring.
✓ Efficient Data Processing Methods
Operating on individual cells, performing operations on arrays of VBA variables, using Excel functions, using built-in Excel tools.
✓ Efficient work with ranges
The End, CurrentRegion, and UsedRange properties; the SpecialCells method; VBA variable arrays (declaration, resizing, loading data, inserting data into Excel cells, array variables).
✓ Sub Procedures
Syntax, scope (private and public), variable storage (local, global private, global public), calling procedures (shorthand, the Call keyword, the Run method), passing arguments (by global variable, by reference, by value).
✓ Function-type procedures
Syntax, calling (from an Excel formula, from another procedure, from the Immediate window, in conditional formatting), arguments (argument-less functions, single-argument functions, multi-argument functions, functions with optional arguments, functions with an unspecified number of arguments, array functions), creating a function description for Excel.
✓ Efficient work with pivot tables
Creating and editing, key objects (PivotCache, PivotTable, PivotFields), references to pivot table fields (ColumnField, DataField, PageField, RowField) and the variables they contain, formatting, creating pivot charts, setting conditional formatting in pivot tables.
✓ Advanced work with charts
Chart types (Charts, Worksheet Charts, Sparklines), objects (ChartObjects, Chart), chart types (column, line, scatter, area, combination, histogram, box plot), creating animations in charts.
✓ Using artificial intelligence
Presentation of methods for working with various artificial intelligence models, including Copilot and ChatGPT.
✓ Numerous practical examples
Examples that apply the knowledge gained throughout the course and demonstrate the skills acquired, including an application that imports periodic sales data (separate .txt files) into Excel, consolidates all reports into a single database, generates reports for specific product groups or cities, and saves them in separate .xlsx and .pdf files
Course coordinators
Type of course
Mode
Learning outcomes
Participants gained the ability to use VBA effectively and apply it to create applications in MS Excel. Thanks to the course, they are able to manipulate, at the VBA code level, every object available in the spreadsheet. The knowledge gained during the course enables participants to independently create advanced automated reports in MS Excel.
Assessment criteria
An independent assessment task to be completed by the participant upon course completion. A minimum score of 50% is required to pass.
Bibliography
Materials prepared by the lecturer and made available to participants on e-learning platform