Excel Programming with VBA Starter

By Robert Martin
    What do you get with a Packt Subscription?

  • Instant access to this title and 7,500+ eBooks & Videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
About this book

Do you have repetitive tasks that you would like to get rid of for good? Would you like to integrate Office Applications in order to streamline some of your tasks? Then look no further. This compact book will provide you with the knowledge to get your VBA programming off the ground and up to a comfortable cruising speed. "Excel Programming with VBA Starter" was born out of the need to have a short, but yet all-encompassing book that would give you a solid foundation in programming with Visual Basic for Applications. This book will enable you to harness the power of VBA in Excel and put it to good use throughout the course of your working day.Can't find properties and methods of an object? Don't know what a property, method or object is? Covering simple and advanced topics, create powerful, reusable examples such as IO, picking files from within Excel and automatically attaching them to e-mails. Learn and use the concept of encapsulation to condense code into bite-size methods to be easily accessed from within your projects, plus much more.

Publication date:
October 2012


Chapter 1. Excel Programming with VBA Starter

Welcome to Excel VBA Starter. This book has been especially created to provide you with all the information that you need to get up to speed with programming with VBA (Visual Basic for Applications). You will learn the basics of VBA, get started with building your first VBA code, create user-defined functions to work out complex calculations, and see the tricks of the trade when it comes to using VBA with Excel.

This document contains the following sections:

So what is VBA? – find out what VBA actually is, what you can do with it, and why it's so great.

Recording a macro, adding modules, browsing objects, and variables – learn how to record a macro, add modules, browse for objects available in your project, and finally what variables are useful for.

Quick start: VBA programming – this section will get you started on programming with VBA. Here you will learn how to perform some core tasks in VBA. Such tasks include using loops, dimensioning objects, and creating and categorizing User-defined Functions (UDFs).

Top features you need to know about – VBA gives you infinite possibilities when it comes to creating your own solutions. In this section, you will learn some key concepts such as enumeration, classes (defining properties and methods), and referencing external libraries, in particular how to manipulate files and folders.

People and places you should get to know – in this day and age, it is impossible to live without the Internet and it is here that you can find resources as well as help for your VBA woes. This section provides you with many useful links to the project page and forums, as well as a number of helpful articles, tutorials, blogs, and the Twitter feeds of VBA super-contributors.


So, what is VBA?

In this section, you will get to know a bit about VBA, its basic features, what you can do with it, and how you can put it to work with a view to facilitating your daily work, by automating common tasks.

The basic features of VBA

Visual Basic for Applications (VBA) is a programming language built into Microsoft Office applications. As you improve your skills in any application from the Office System, you will eventually realize that although Microsoft Office applications offer a large number of tools, they do not offer everything you need to perform your daily chores. Such chores may include creating a corporate custom-format, a custom function that calculates commission payments, and so on.

Thus, VBA works as a gap-filler; in other words, its main purpose is to ensure that you can do whatever you need to do in your job.

What kind of things can you do with it?

Once you have pushed your experience using the Office application to the limits and you can no longer get your job done...


Recording a macro, adding modules, browsing objects, and variables

Before you get your hands "dirty" with coding in VBA, there are a few things you need to know. These things will help when it comes to coding. In this section, you will learn how to:

  • Record a macro

  • Add modules

  • Browse objects

  • Get some background on declaring variables

We will start with macro recording, a feature which is available in most Office applications.

Recording a macro

A macro, in Office applications, is a synonym for VBA code. In Excel, we can record almost any action we perform (such as mouse clicks and typing), which in turn is registered as VBA code. This can come in handy when we need to discover properties and methods related to an object. Let us now have a look at some ways you can record a macro in Excel. There are two options:

  1. Recording a macro from the status bar.

  2. Recording from the Developer tab.

Option 1 – Recording a macro from the status bar

From the status bar, click on the Record Macro button. If the button is...


Quick start – VBA programming

Now that you have the basic understanding about VBA (recording a macro, adding modules, browsing objects, and declaring variables), it is time to get to work.

In this section you will learn how to:

  • Use loops

  • Dimension and instantiate objects

  • Create sub routines and user-defined functions

Working with loops

You will start your programming trip down the VBA lane by learning a bit about loops. Loops allow you to repeat a set of instuctions until a predetermined condition changes or a criterion is met.

Loops are extremely important, so you should study and practice this section carefully. You will now be introduced to different looping methods. We will start with For-Next loops.

Method 1 – For-Next loops

If you need to count something or you need to loop through a series of predetermined elements within a given set, then you should look no further. This is because once you specify the start and end values and the loop takes place, the counter starts to run. Suppose that...


Top features you'll want to know about

In this section, you will learn about some VBA programming features that you will certainly want to know about. Here, you will learn how to work with enumeration, classes, and external libraries.

We will kick off by looking at enumeration.


The first question that might pop into your mind is, "What the heck is enumeration?" As the name suggests, it enumerates something, but what? As a rule of thumb, enumeration is a group of constants. So, supposing you have an object, it is likely that this object has a color property. Therefore, you could have a "Color" group. Then you could enumerate the colors you want to use in your code. This will make life easier when determining which color to use, given that you simply declare the enumerator and choose one of its values when the times comes.

Take a look at the following screenshot:

Here, we have a FileDialog property (which is a member of the Application class) that takes FileDialogType as an argument...


People and places you should get to know

If you need help with Excel, here are some people and places which will prove invaluable.


Articles and tutorials

Here is a selection of VBA code samples that will help you hone your coding skills:


About the Author
  • Robert Martin

    Robert Martin is an Excel MVP and Microsoft Certified Professional. With a background in finance, his career has ranged from being an IT Director of an investment bank in London to doing charity work in Africa, before moving to Brazil in 2007 and setting up an IT consultancy firm and then authoring training (audiovisual and written) material on Microsoft technologies. Currently he works in Brazil as an IT Consultant. Robert Martin has also authored the following books: Excel Avançado, Digerati 2008 RibbonX Customizing the Office 2007 Ribbon, Wiley 2008 Excel e VBA na Modelagem Financeira Uma abordagem prática. Axcel Books, 2005

    Browse publications by this author
Latest Reviews (2 reviews total)
Exactly what I was looking for and everything is according to the description. Price is extremely competitive.
Great service to date....
Excel Programming with VBA Starter
Unlock this book and the full library FREE for 7 days
Start now