About Course

Credit Risk Analysis Training: Using Advanced Excel

Advanced Excel Training with VBA Programming | Kenya

Category: Data Management and Analysis Training

Overview

The Expedition

About the Program

The Advanced Excel Training with VBA Programming is designed to help professionals automate complex Excel tasks, create custom solutions, and work more efficiently with data. This hands-on course teaches participants how to use Visual Basic for Applications (VBA) to develop macros, build interactive forms, and write powerful procedures that streamline data processing and reporting. Through practical examples and real-world exercises, learners will gain the skills to transform repetitive tasks into automated workflows, saving time and improving accuracy.

Participants will explore the structure of VBA, learn how to navigate Excel’s object model, handle errors effectively, and debug code using built-in tools. The course also covers creating dynamic PivotTables, working with user-defined functions, and managing user forms and controls. By the end of the training, attendees will be able to confidently build customized Excel solutions that meet unique business needs.

The program is offered both online and in-person and includes a certificate of completion.

Objectives of the Program

By the end of this Advanced Excel Training, participants will be able to:

Navigate the Visual Basic for Applications (VBA) development environment and configure macro security settings.
Record, run, edit, and manage macros to automate repetitive Excel tasks efficiently.
Develop custom sub and function procedures using standard modules to streamline data processes.
Understand and manipulate Excel’s object model, including objects, properties, methods, and collections.
Use expressions, declare variables, apply data types, and implement intrinsic functions for dynamic programming.
Design interactive user forms and integrate controls such as text boxes, combo boxes, and buttons to capture user input.
Create and manage PivotTable objects programmatically to generate dynamic reports from structured datasets.
Debug VBA code using breakpoints, the Immediate window, and step-through techniques to identify and resolve logic errors.
Implement structured error handling using the On Error statement and the Err object to manage run-time issues effectively.

Target Audience

This course is ideal for:

Professionals who need to optimize data management and boost productivity using VBA

Advanced Microsoft Excel professionals looking to automate spreadsheet tasks using VBA

Big Data enthusiasts who want to enhance their Excel processes through programming

Advanced Excel Training Period

Classroom: 5 Days
Online: 7 Days

Course Outline: Advanced Excel with VBA Training

Module 1: Getting Started.

Introducing visual basic for applications.
Displaying the developer tab in the ribbon.
Recording a macro.
Saving a macro-enabled workbook.
Running a macro.
Editing a macro in the visual basic editor.
Understanding the development environment.
Using visual basic help.
Closing the visual basic editor.
Understanding macro security.

Module 2: Working with Procedures and Functions.

Understanding modules.
Creating a standard module.
Understanding procedures.
Creating a sub procedure.
Calling procedures.
Using the immediate window to call procedures.
Creating a function procedure.
Naming procedures.
Working with the code editor.

Module 3: Understanding Objects.

Module 4: Using Expressions, Variables, and Intrinsic Functions in Excel.

Understanding expressions and statements.
Declaring variables.
Understanding data types.
Working with variable scope.
Using intrinsic functions.
Understanding constants.
Utilize intrinsic constants.
Ply message boxes.
Using input boxes.
Declaring and using object variables.

Module 6: Working with Forms and Controls.

Understanding user forms.
Using the toolbox.
Working with user form properties, events, and methods.
Understanding controls.
Setting control properties in the properties window.
Working with the label control, text box control, command button control, combo box control.
Working with the frame control.
Working with option button controls.
Working with control appearance.
Setting the tab order.
Populating a control.
Adding code to controls.
Launching a form in code.

Module 7: Working with the Pivot Table Object in Excel.

Understanding pivot tables.
Creating a pivot table using worksheet data.
Working with the pivot table objects.
Working with the pivot fields collection.
Assigning a macro to the quick access toolbar.

Module 8: Debugging Code.

Module 9: Handling Errors.

Understanding error handling.
Understanding VBA’s error trapping options.
Trapping errors with the on-error statement.
Understanding the err object.
Writing an error-handling routine.
Working with inline error handling

Delivery Method

This course is a blend of theory, practical exercises, group work, and case studies. Participants will receive training manuals and reference materials to continue learning after the course.