Five Easy To Learn VBA Programming Techniques

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=0




Do




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.