Do you want to further develop your skills in Excel? Formulas and functions hold no new challenges? Then why not move into the world of Excel development?
Visual Basic for Applications (VBA) is the underlying programming language behind Excel macros and is used to adapt and extend the capability of Excel. At a simple level, tasks which are repetitive and lengthy can be recorded and then replayed in a matter of moments. You can also use VBA to write your own functions (User Defined Functions) to simplify more complex formulae.
Who is this for?
This is aimed at those who have a good working knowledge of Excel and want to speed up repetitive work using macros and begin learning how to develop Excel applications using the integrated Visual Basic for Applications (VBA) language. You do not have to be a programmer to join in!
What does it cover?
You'll learn how to record and edit basic macros, use the VBA editing environment and its code debugging tools, progressing to writing simple, small VBA code programs to understand the language, culminating in how to create custom screens for your users. Run over 1 day, this can also be split into small chunks, delivered as part of a co-development project if you have an application to develop.
How long is it?
1 day or can be split into several sessions if required
Where is it run?
On your premises or via one of the training centres I use
Overview of VBA
What is VBA?
What can it do for me?
VBA Jargon - Understanding workbooks and VBA projects
Being repetitive – recording and running macros
Recording a few useful macros
Relative versus absolute recording
Running a macro
Assigning a macro to the Quick access toolbar
Introducing the VBA editor – the IDE
The Integrated Development environment (IDE) explained
Menu & toolbar overview
The Project Explorer
Where does the code go?
Introducing Code Modules
Naming your modules
Watch your Macros operate
Tracing through code
Responding to your User
Getting Input through input boxes
Feedback through Message Boxes
Variables & Constants dissected
Why use variables?
Creating, naming and assigning values to variables
Using Option Explicit
Why use constants?
Creating and using constants
Controlling the flow …
Making decisions with
IF…THEN…ELSE
Going through the loop
The FOR…NEXT
The WHILE loops
When things go wrong…
Setting breakpoints
Watching variables change
Setting up the Watch Window
Adding watches to variables
The Excel Object Browser
Methods, properties & events
Reusing your code - subroutines
What are Subroutines?
Creating & naming subroutines
Calling a subroutine
Getting data into the subroutine - basic parameter passing
Reusing your code - functions
What are User Defined Functions (UDF)?
Advantages of a UDF
Creating & declaring Functions
Getting data into the function – Basic parameter passing
Introduction to creating your own Excel user interface
Creating a custom user form
Understanding events
Adding a push button and text box
Adding VBA code to the push button’s event
Sending the text box contents to a spreadsheet
Reading text from a sheet to the text box
Adding a combo box
Setting the list items from a spreadsheet range
Picking the selected item
Sending the selected item to a spreadsheet
