This session is all about the basics of VBA coding, and the statements and functions that are supported within the language. We explore how to access and manipulate data in the worksheet framework. Remember we have very few needs to interact with VBA other than through worksheet cells (although there are options to create dialog boxes). The first session is focused on helping you to understand the nature of VBA coding, and to interact with the worksheet as the environment that is unique to Excel™. For people with experience in Quick Basic or Visual Basic this may come as a surprise because many operations are so much simpler when you have the underlying framework as a “given” that you design a solution around.

This session continues with the VBA coding, looking at more robust tools at a workbook level that can then operate on multiple worksheets. We look at invoking subroutines and user-defined functions that let us re-use previously developed functionality in a different context. You will find the actual programming logic in VBA quite easy to use, but what can be tough to master is debugging. We show a technique we use for creating traps, so that we know exactly where the code is executing, and how we cause a controlled stop in the event of a VBA error that tells us where to look for an answer to the problem. Once you have that under control it becomes quite easy to write VBA code and then to test and debug that code.

One of the most important tasks you can perform is sorting, and it can be one of the most painful processes to implement. Not so with Excel™-VBA where with a little slight-of-hand we can employ old techniques while we let Excel™-framework do the heavy lifting. In this segment we develop a solid sorting subroutine that can be invoked with parameters declaring all the data elements we want to sort on. Once you know how, you can just incorporate the components of the sort within your regular logic, and so potentially save yourself a lot of wear and tear in calling subroutines that have a lot of parameters to pass. In our course we also use this to create a powerful example of how you can use a predefined subroutine.

After exploring the different things you can do, we need to also look at a potential to use Excel™ in a collaborative manner, with many contributors to a given application, and how to control who does what so that there is no risk of corruption. There are passive ways of sharing, but there is also an interactive “real time” sharing capability in a networked environment. Depending on the nature of the application that can be an important part of your overall design, to figure out who can do what, and how to collect and integrate that data in the overall scheme of things. We show how to build a sort index, then to invoke the sorting engine subroutine to create a composite index, and finally to process the data using a “tag-file” that lets us do a random access across multiple worksheets. This will be what you can focus on in your “take home” assignment before the next course.

Learning Formats       BPEV_VB3

This course is currently available in a classroom setting (public or company private) with approximately 15 contact hours. The training should be followed by independent learning to gain hands-on practice with what has been learned in class. Therefore we recommend at least a one week gap to let the student master the subject matter before taking the next course.

PDF – Certificate Of Completion

Each course offers a certificate of completion that identifies the course, the student, and a brief description of the course. To receive a certificate the student must have attended at least 80% of the course sessions. This personalized certificate is forwarded to the student by Email.

PDF – Course Notebook

Each course includes a notebook in PDF format that provides the minimum knowledge the student must master in order to obtain the certificate. In the notebook you will find references to other study materials. Students receive the notebook by Email when their registration is confirmed.

PDF – Program Overview

An overview of this study program can be downloaded from the website by right-clicking on the program link on the enquiry page.

PDF – Current Training Schedule

A list of upcoming training sessions can be downloaded from the website by right-clicking on the schedule link on the enquiry page.

Registration – Service Providers

To register for any training course please look on the enquiry link page of your service provider (from where you accessed this website). On the page you will find a registration request form where you can order the course that you are interested in. The availability dates will be provided to you, along with payment instructions if you decide to go ahead.