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:
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:
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:
Sometimes you'll just want to use part of a text value and discard the rest of the string.
This example looks at what might be a telephone number and removes the area code:
A lot of programming is about making decisions within your code and the if then command is the most commonly used:
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:
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.
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=10There 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 ifThis 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 if5. 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 nextNormally, 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
SummaryIn 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 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/ including links to other articles on VBA issues he has written.
Article Source:
http://EzineArticles.com/?expert=Andy_L_Gibson
0 comments:
Post a Comment