Automation using Excel Visual Basic for ApplicationsIT Short Courses

Enquire MoreRegister NowCorporate Courses

DurationTwo days
Course Dates21 Feb 2024 9:00am - 22 Feb 2024 5:00pm
13 May 2024 9:00am - 14 May 2024 5:00pm
01 Aug 2024 9:00am - 02 Aug 2024 5:00pm
04 Nov 2024 9:00am - 05 Nov 2024 5:00pm

Course Fee

Member $370.60
Non-Member $436.00
  • Members enjoy a 15% discount before GST
  • Group Discount (5 or more participants) - 10%
  • Course fees updated with 9% GST for 2024

Introduction

Most people express a modicum of a surprise when told that Excel, like Access, can be enhanced with Visual Basic. But it should be no surprise at all since both programs share a similar structure. Increasingly, VB is being used to enhance Excel’s basic functionality and create (almost) intelligent spreadsheets and financial models.

This course aims to introduce VBA in as non-threatening a manner as possible. Beginning with wizards and macros, participants are led through the methods and syntax of simple programming in VB, culminating in writing custom code, customizing Excel elements and debugging.

Key Takeaways

Upon completion of this workshop, participants will be able to;

  • This course aims to introduce Visual Basic Application (VBA) in as non-threatening a manner as possible
  • Beginning with wizards and macros, participants are led through the methods and syntax of simple programming in Visual Basic (VB), culminating in writing custom code, customising Excel elements and debugging

Who Should Attend

Anyone who wishes to harness the power that Excel can demonstrate when combined with programming in Visual Basic.

Programme Outline

Module 1: Recording Macros

  • Macro Recorder Overview
  • Recording Formatting Macros
  • Recording Calculation Macros
  • Testing Macros
  • Relative Recording
  • Assigning Macros to Toolbars
  • Viewing Macro Code
  • Modifying Recorded Macro

Module 2: Introduction to VBA

  • Overview of VBA
  • Modifying Existing Code
  • Setting General Options
  • Modifying Existing Code
  • Creating Reference to Object
  • Setting Object Properties
  • Using With…End With Structure
  • Commenting Statements

Module 3: Variables, Data Types & Constants

  • Variable Naming Rules
  • Declaring Variables
  • Defining Data Type
  • Scoping Variables
  • Working with Constants

Module 4: Using Excel Objects

  • Excel Object Model
  • Using Workbook Objects
  • Using Worksheet Objects
  • Using Range Objects
  • Objects in Procedures

Module 5: Programming Techniques

  • Communicating with the User
  • Prompting for User Input
  • Creating Conditional Statements
  • Testing for Multiple Conditions
  • Looping/Repeating Statements

Module 6: User-Defined Functions

  • Creating Custom Functions
  • Passing Arguments
  • Creating Add-Ins

Module 7: Creating Custom Forms

  • UserForm Overview
  • Creating Custom Form
  • Modifying Form Properties
  • Adding Controls to Form
  • Control Properties
  • Running a Form

Methodologies

Participants must be familiar with Excel when attending this course – this is NOT a course for beginners. Some programming experience or knowledge of another programming environment is advantageous though not essential.

A combination of lectures, explicit teaching, practical demonstrations and peer teaching is applied for this course.

If you are interested in this corporate programme, please complete the following form and one of our representatives will get back to you as soon as possible.

I would like to receive marketing, advertising and promotional messages via:
(Please untick if you do not wish to receive)

MDIS values your privacy and we are committed to safeguarding your personal data in compliance with the Personal Data Protection Act 2012. By providing your personal details, you have authorised MDIS to share with you our marketing, advertising and promotional materials.

Quick Information

Visual Basics (VB) is being used to enhance Excel's basic functionality and create (almost) intelligent spreadsheets and financial models.

Contact Details

  +65 6842 6666
  mdc@mdis.edu.sg