If you've been using Excel you'll know that macros are a great way to be more productive by automating repeated tasks. But to get the best out of Excel you need to know a little about the VBA language that enables you to edit and improve on macros and write your own routines.
This article looks at five simple ways to work with data and how it might be applied in your daily work with Excel.
1. The Do Loop Command
In programming, a common task is to repeat an operation until a certain condition is encountered or a series of changes are completed. Here's a simple example:
x=0Do
x=x+1
Loop until x=10
There are a few different varieties of the command; for example you can include an exit command within the loop when a condition is encountered.
An example might be to generate a list of 10 unique random numbers and exit the loop once the 10 numbers have been listed.
A word of warning about the do loop command; it's easy to overlook or program the condition status incorrectly and set up an endless loop which will cause your macro to run forever!
2. Searching Within A Text String To Find Some Other Text With Instr
If you're searching for a particular value in a worksheet sometimes you might want to see if your search text is partially contained in a cell. Here's the basic syntax:
x=instr(cellValue,myText)
The function inStr - you can remember it by thinking "in string" - tells you at what character number the search text starts in the target text; if it's greater than zero then you've found a match.
Alternatively, you can use the inStr command as a Boolean (true/false) value:
An example might be that you're searching for all the cell values that are "Las Vegas" but you're concerned some might be entered as "Los Vegas". You can use the inStr function to return the near matches:
found=instr(activeCell.text,"Vegas")
3. Returning Part Of A Text String With Mid
Sometimes you'll just want to use part of a text value and discard the rest of the string.
newString=mid(oldString,firstChar,lastChar)
The way to remember this function is "mid-string" - you're creating a new text value from mid-way through an old string and you can use other programming techniques to determine the value for first and last.
This example looks at what might be a telephone number and removes the area code:
tmp = "(06) 256 36598"
first = InStr(tmp, ")") + 1
phoneNo = Mid(tmp, first)
4. Using The If Then Command To Determine An Action
A lot of programming is about making decisions within your code and the if then command is the most commonly used:
If condition then
' do something
else
' do something else
end if
This code snippet is part of a looping routine that is searching though a column of phone numbers and stops when it finds an area code of "(06)"
If instr(activeCell.value,"(06)") then
Exit sub
Else
activeCell.offset((1,0).activate
end if
5. Using For Next To Run A Series Of Commands
Similar to the do loop command for next is generally used when your code is able to act as a counter and exit when it reaches the upper limit:
For x=1 to 10
'do something 10 times
next
Normally, you'd use the for next command when the value of the counter may determine the action within the loop. Here's a code example which examines a range determined by the first and last rows you specify.
First=1
Last=10
For x=first to last
Range("a" & x).activate
' do something
next
Summary
In this article we've covered some of the basic VBA techniques and functions.
Sometimes there are several techniques available and it can be difficult to know which one to use. While it's tempting to say which ever one works is the best, a good rule of thumb is to use the technique which is most easily understandable after the code has been written.
Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.
