Excel Programming with VBA Starter [Instant] — Save 50%
Get started with programming in Excel using Visual Basic for Applications (VBA) with this book and ebook.
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.
In this article by Robert Martin, author of Excel Programming with VBA Starter,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.
(For more resources related to this topic, see here.)
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 due to a lack of built-in tools, using VBA will help avert frustrations you may encounter along the way. VBA enables you to build custom functions, also called User-defined Functions (UDFs), and you can automate tedious tasks such as defining and cleaning formats, manipulate system objects such as files and folders, as well as work together with Windows as a combined system, through its Application Programming Interface (API), and other applications by referencing their object libraries or Dynamic-link Libraries (DLLs).
Of course you can also use VBA to manipulate the Office application that hosts your code. For example, you can customize the user interface in order to facilitate the work you and others do.
An important thing to remember, though, is that the VBA code that you create is used within the host application. In our case, the code will run within Excel. Such VBA programs are not standalone, that is, they cannot run by themselves; they need the host application in order to operate correctly.
How can you use this technology within your existing projects?
You can use VBA in two different ways. The first, and most common way is to code directly into your VBA project. For example, you may have an Excel workbook with some custom functions that calculate commissions. You can add modules to this workbook and code the UDFs in this module.
Another option would be to save the workbook as an Addin. An Addin is a specialized document that hosts the code and makes it available to other workbooks. This is very useful when you need to share the solutions you develop with other workbooks and co-workers.
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
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:
Recording a macro from the status bar.
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 not visible, right-click on the status bar and from the pop-up menu, choose the Macro Recording option, as shown in the following screenshot:
Option 2 — Recording from the Developer tab
Now that you know how to record a macro from the status bar, let us check another option. This option requires that you activate the Developer tab. In order to activate it, assuming it is not active yet, follow these steps:
Go to File | Excel Options | Customize Ribbon.
Under Main Tabs check the Developer checkbox, as shown in the following screenshot :
- Next, activate the Developer tab and click on Record Macro, as shown in the following screenshot:
Once the macro recording process starts, you will be prompted to enter some basic information about the macro such as the macro name, the shortcut key, location where the macro should be stored, and its description. The following screenshot shows these options filled out:
Once the macro has been recorded, you can access its container module by pressing, simultaneously, the Alt + F11 keys. Alternatively, you can click on the Visual Basic button in the Developer tab. This button is to the left of the Record Macro button introduced previously. This will open the Visual Basic Editor (VBE), where all the VBA code is kept.
The VBE is the tool we use to create, modify, and maintain any code we write or record. The following screenshot shows the VBE window with the project explorer, properties, and code window visible:
If upon opening the VBE, the VBA project explorer window is not visible, then follow these steps:
Go to View | Project Explorer.
Alternatively, press the Ctrl + R keys simultaneously.
If, on the other hand, the VBA project explorer is visible, but the code window is not, you can choose which code window to show.
Suppose you are interested in the content of the module you've recorded from the project explorer window, follow these step to show the module window:
Click on View | Code.
Alternatively, press F7.
In this article, you have learned some basic stuff about VBA. These included macro recording, adding modules, and browsing objects.
Resources for Article :
- Understanding ShapeSheet™ in Microsoft Visio 2010 [Article]
- Excel 2010 Financials: Using Graphs for Analysis [Article]
- Excel 2010 Financials: Adding Animations to Excel Graphs [Article]
About the Author :
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