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.
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.
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.
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.
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:
Recording a macro from the status bar.
Recording from the Developer tab.
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
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.
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...
If you need help with Excel, here are some people and places which will prove invaluable.
Homepage: http://office.microsoft.com/en-us/
Manual and documentation: http://office.microsoft.com/en-us/excel-help/excel-help-and-how-to-FX101814052.aspx?CTT=97
API Viewer is an application that exposes Windows APIs, which you can use in your VBA code. You can download the application from http://www.activevb.de/rubriken/apiviewer/index-apiviewer.html.
A list of Excel MVP's websites is found at http://www.mvps.org/links.html#Excel.
Here is a selection of VBA code samples that will help you hone your coding skills:
Excel ActiveX Data Objects (ADO) coding: http://www.excelguru.ca/list.php?category/49-Excel-ADO
Interact with Outlook from Excel: http://www.msofficegurus.com/post/Creating-Outlook-2007-Rules-from-Excel-2007.aspx
Official forums: http://answers...
