5 Basic VBA Techniques And How To Apply Them In Excel

As a business tool Excel is useful for analysis and creating information from raw data. But often the data itself needs to be worked with before it can be useful and some knowledge of Excel's VBA functions and procedures is vital.

This article will look at 5 basic techniques in Excel's VBA suite that will make the task of working with spreadsheet data that much easier.

1. Create A Subroutine To Run Your Code

If you're going to write VBA code, you need a place to write and save it and as long as you know how to create a macro then writing your own code becomes straightforward. Here's a simple code snippet to get up and running:

Sub helloWorld()




Msgbox("Hello World")




End sub

You can run this code by selecting the run command in the code window, and then select the name of the macro you've just written. The code should display a dialog box with the message "Hello World" displayed.

2. Selecting A Cell In The Spreadsheet

To manipulate data, VBA code needs to move the cursor to various sheets and cells in the spreadsheet. The following command "moves" the cursor to cell A1 on Sheet 1 then displays in a message box the value of the active cell.

Sheets(1).activate




Range("a1").activate




Msgbox activeCell.value

A similar command to activate is select which differs slightly from activate. You would use select if you're intending to actually do something with the cell such as copying the contents or selecting a larger range of cells.

The activeCell reference is a common one and enables the code to access various properties of the current cell.

3. Moving Around The Spreadsheet With The Offset Command

Once you've selected or activated a cell address, you might want to move to another cell and sometimes it's convenient to move relative to the active cell:

activeCell.offset(1,0).activate

This command moves the cursor one cell down and 0 cells across.

4. Accessing the Value Of A Cell

You'll need to know what's in a cell before you can do anything with it and you can either use the value directly or assign it to a variable. The value can also be converted to a text string or a number, if applicable.

myTxt=activeCell.value




' use the ampersand character to convert a number into a string to use as part of an expression




myString="The number is " & activeCell.value

5. Selecting A Range Of Cells

A very common need is to select a range of cells and there are numerous methods to do this. For example, if there are values in the first column you could select them in the following ways:

' most common




range("a1").currentRegion.select





' find the last row




last=range("a1").end(xlDown).address




range("a1:" & last).select





'select the entire column




range("a1").entireColumn.select

Summary

There are a large number of methods and techniques for manipulating data in Excel which can be confusing to begin with. Although a basic knowledge of VBA coding is essential, an important issue is understanding your own processes and objectives; then you can decide on the most effective way forward.

If you don't know how to record a macro, here's a simple tutorial.

Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business. 

Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities whereever they might present themselves. You can talk with Andy at his blog at http://solutions4business.wordpress.com/