VBA Programming in MS Excel

VBA Programming in MS Excel

PUBLIC COURSES

£1300

- Anyone can join the training
- Course outline as presented on the website
- Small groups, 3-10 people

PRIVATE COURSES

Price set individually

- Training workshop just for your team
- You choose date and location of the training
- Course outline tailored to your needs

About the training

Visual Basic for Applications (VBA) is the programming language built-in in Microsoft Excel and other Office programs. VBA is used in automating daily work with Excel and adding additional functionalities. It can be used to record or write very simple macros that speed up repeatable tasks, but also to create new Excel functions and Add-Ins. Due to its versatility and popularity, Excel has become an essential tool for data analysis and calculations. For this reason, Excel and VBA skills are valued by employers.

The aim of this training is to introduce you to VBA syntax and basics of VBA programming. You will see many examples of automation of common Excel tasks. Each section of the course is followed by exercises that will help you practice the material.

How can macros automate my daily work? The possibilities are endless. Macros can filter, sort and format data, create pivots, make calculations, save files, send emails, create .pdf files, create folders, open and manipulate other workbooks…And you will learn it all during this training!

Who is this training for?

This training is people that would like to automate their daily Excel work by creating VBA macros. If you work with Excel on a daily basis and would like to speed up lengthy or repeatable tasks and reports this training is right for you. To take part in this training you need to be an intermediate Excel user, however, no programming knowledge is required.

What will I learn?

The skills and knowledge acquired during this training will allow you to create various macros and automate the daily work. In particular, you will be able to:

  • Understand the concept of Excel object model and VBA programming
  • Use VBA editor effectively
  • Write your own Subroutines
  • Create and use variables of a different type
  • Creating and using user-defined Excel functions
  • Using loops and conditional statements in your Subroutines
  • Create user forms
  • Program events in user forms and validate data inputted by user
  • Writing procedures that start automatically
  • Debug your code

Course outline

  1. Concept of VBA programming in Microsoft Excel
    • Programming in Microsoft Excel
    • VBA glossary
    • Developer tab
    • VBA editor
    • Opening and closing the editor
    • Microsoft Excel objects
    • Using the Immediate window
    • Working with objects collection
    • Changing parameter values
    • Working with sheets
    • Object browser
    • Using help
  2. Starting your work with VBA in Excel
    • Using project explorer
    • Using properties window
    • Locals  and watch window
    • Using and customizing VBA editor toolbar
    • Working with code module
    • Running code from the editor
    • Pausing and stopping code, using breakpoints
    • Running code in step mode
  3. Procedures
    • About procedures
    • Creating a new Subroutine
    • Using IntelliSense
    • Using Edit toolbar
    • Commenting and uncommenting blocks of code
    • Creating indents
  4. Variables
    • About variables
    • Creating and using variables
    • Explicit declaration
    • Variable range
    • Private variables
    • Public variables
    • Passing arguments to a function
    • Passing variable by reference
    • Passing variable by value
    • Data types
    • Declaring data types
    • Arrays
    • Exercises
  5. Functions in VBA
    • About functions
    • Functions vs Subroutines
    • Writing your own function
    • Using user functions in spreadsheet
    • Setting data types for arguments
    • Using multiple arguments
    • Importing VBA module
    • Using functions in Subs and other Functions
    • Exercises
  6. Working with Microsoft Excel objects
    • Application object
    • Worksheets object
    • Workbook object
    • Range object
    • Exercises
  7. Programming basics
    • MsgBox function
    • Getting user’s input
    • InputBox function
    • InputBox method
    • If with one and with multiple conditions
    • Select Case
    • Do Loop
    • For
    • Do While
    • Do Until
    • Exercises
  8. Creating User Forms
    • VBA User Forms concept
    • Creating basic form
    • Adding text fields – TextBox
    • Labels
    • ComboBox
    • OptionButton
    • CommandButton
    • Running form
    • Exercises
  9. Programming User Form controls
    • Programming form events
    • Initializing form
    • Closing form
    • Getting user input
    • Running form procedures
    • Data validation
    • Running forms from Subroutines
    • Running forms from the toolbar
    • Exercises
  10. Automatic procedures
    • Programming automatic procedures
    • Running automatic procedures
    • Opening the Workbook automatically
  11. Debugging
    • Error types
    • On Error
    • Basic debugging
    • Resume
    • Statements for error handling
    • Err
    • Debugging user forms
    • User-defined errors
    • Exercises
  12. Advanced VBA programming (topic is on the three-day course only)
    • Practical examples, tips and tricks
    • Creating Excel Add-Ins using VBA
    • Pivot Tables
    • Charts
    • Events
    • Interacting with other applications
    • Useful Add-Ins
    • Working with files

Course Curriculum

Curriculum is empty

Instructors


Send an enquiry

I am interested in


 

Enquire about the private (on-site) training course

I am interested in


 

Enquire about the public training course
 

I am interested in


 
Szybki kontakt