Section 26.3. Exploring the VBA Language
26.3. Exploring the VBA Language
Now that you've learned a bit about the Visual Basic editor and how Excel uses objects, it's time to jump right in and get some firsthand experience with VBA. First, you'll see how custom-programmed macros can perform some basic editing and formatting. After that, you'll learn how you can use simple macro code to solve problems that you can't tackle with the macro recorder alone. In these sections, you'll see how to manipulate data more flexibly, make decisions, and repeat actions.
Before you get started, here are a few helpful tips:
-
For easier macro coding, try to arrange your windows so you can see the Excel window and the Visual Basic editor window at the same time, side-by-side.
-
To play a macro, move to the position in the worksheet where the macro should execute, and then switch to the Visual Basic editor. In the Visual Basic editor, move to the appropriate subroutine and click the play button.
-
Make sure you type in every command and object name correctly. Minor differences cause mysterious errors.
-
If Excel finds some invalid code when running a macro, it stops executing it and highlights the problem line in yellow. At this point, you can correct the line and press play to keep going, or stop and give up altogether (many programmers find long walks on the beach or guzzling a jug of Mountain Dew a helpful way to clear their heads).
26.3.1. Entering Text in the Current Cell
A few examples can go a long way to showing you how a typical macro works. First of all, check out the following macro subroutine, which represents one of the simplest possible macros you can create. It inserts the text "Hello World!" in the current cell (replacing whatever content might already be there).
Sub MyMacro ActiveCell.Value = "Hello World!" End Sub
With a little more effort, you can edit the current cell value instead of replacing it with new content. For example, imagine you want to take the current text value, and add the message "INVALID: " before the text. To accomplish this feat, you can use the following macro code:
Sub MyMacro ActiveCell.Value = "INVALID: " & ActiveCell.Value End Sub
In this macro, Excel begins by joining together two pieces of text: the word "INVALID: " and whatever content is in the current cell. It then takes that combined piece of text and places it back into the cell. In this macro, the ampersand symbol (&) is key. It's a concatenation operator , which means it glues together different pieces of text.
You can use a similar approach to adjust a cell that has a number in it. In this case, you can use all the ordinary numeric operators, like +, -, /, *, and ^. Here's an example that multiplies the current cell value by 2, subtracts 1, and enters the new value:
Sub MyMacro ActiveCell.Value = (ActiveCell.Value * 2) - 1 End Sub
Note: When using arithmetic operators, make sure the current cell contains a valid number. Otherwise , your code will fail with a cryptic "type mismatch" error, which is a reminder that Excel can't perform numeric calculations with text.
26.3.2. Moving to Other Cells
The ActiveCell object is the starting point for everything you want to do with the currently selected cell. However, it doesn't let you change the content of other cells. If you want to do that, you need to access these cells by using the Offset property.
The property looks a little more complicated than other properties because it needs two pieces of information: a row offset and a column offset (in that order). The row offset tells Excel how many rows down you want to move. The column offset tells Excel how many columns to the right you want to move. If you want to move up or left, you need to use a negative number.
For example, the following macro places the phrase Top cell in the current cell, and then places the phrase Bottom cell in the cell that's immediately underneath it.
Sub MyMacro ' Change the top cell. ActiveCell.Value = "Top cell" ' Change the bottom cell. ActiveCell.Offset(1,0) = "Bottom cell" End Sub
There's one important factor you need to note about this code. Although it changes two cells, it doesn't actually move to the second cell. Instead, when the macro ends, you're still positioned in the top cell.
If you actually want to move to the new cell, you need to use the Activate or Select method, as shown here:
Sub MyMacro ' Change the top cell. ActiveCell.Value = "Top cell" ' Move down one cell. ActiveCell.Offset(1,0).Select ' Now this changes the bottom cell. ActiveCell.Value = "Bottom cell" End Sub
26.3.3. Editing Specific Cells
Using Value and Offset, you can romp around your worksheet changing cells as you please . However, you probably remember from the last chapter that you can edit cells in two different waysusing relative or absolute references. The example macros you've seen so far use relative references, which means they start working in the current position in the worksheet. However, in some situations you want to move to a specific cell. In order to do this in macro code, you use the Range object.
The basic technique is easy. You supply the cell address (like A2) as an argument to the Range object, and then use Activate or Select to move to the cell. If you just want to change the cell, you can use the Value property to alter its content without leaving your current position.
Here's an example that shows both techniques:
Sub MyMacro ' Change cell A1. Range("A1").Value = "This is A1" ' Move to cell A2 and change it. Range("A2").Select ActiveCell.Value = "This is A2" End Sub Interestingly, you can even select multiple cells at once using a range reference (like A1:A2). In this case, if you set the value, that value appears in every selected cell.
Sub MyMacro ' Insert the text "Hello" in ten cells Range("A1:A10").Value = "Hello" End Sub For a little more excitement, take a look at the next macro. It starts by creating a new worksheet for your workbook, and then it fills in several cells in that new worksheet.
Sub MyMacro ' Create the worksheet using the Add method. ActiveWorkbook.Worksheets.Add ' Enter several cell values. Range("A1").Value = "Company Report" Range("A2").Value = "Generated by an Excel macro" ' Get the name of the person who owns this copy of Excel ' using the UserName property of the Application object. Range("A3").Value = "Generated for " & Application.UserName End Sub 26.3.4. Formatting Cells
Conceptually, using macros to format cells is just as easy as using them to edit text. The difference is that there are many more properties you need to think about, because there are dozens of different ways to format a cell.
You use the Selection object to perform any formatting you want. The Selection object includes top-level properties like HorizontalAlignment, VerticalAlignment, and MergeCells, as well as some objects like Interior (which lets you set fills and patterns) and Font (which lets you configure the typeface and font size ).
Before you can apply any formatting, you need to start off by selecting the cells you want to change. If you know the exact reference, you can use the Select method of the Range object. Here's an example that shows some of the code from the InsertHeader macro:
Sub InsertHeader ' Select the cells to format. Range("A1:C1").Select With Selection ' Note that the alignment properties take special constant values. .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .MergeCells = True End With ' Change the font of the selected cells. With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 14 End With ' (Other code omitted.) End Sub This code selects a range of three cells (A1 to C1) and changes the alignment and font.
If you want to select a relative group of cells, you need to be a little craftier. Instead of using the Range object directly, you use the ActiveCell.Range object. Using this object, you can specify a range of cells in the same way, but with one key differenceExcel interprets your range relative to the current cell. For example, consider this code:
ActiveCell.Range(A1:A2).Select
This statement doesn't necessarily select cells A1 and A2. Instead, it selects the current cell and the cell immediately to the right. Essentially, Excel pretends that the current cell is cell A1 for the purpose of your selection.
Here's a more detailed example:
Sub MyMacro ' Select the cells to format. If you are currently positioned ' in cell B2, for example, this actually selects B2:D2. ActiveCell.Range("A1:C1").Select ' (Formatting code omitted.) End Sub 26.3.5. Using Variables
Every programming language includes the concept of variables , which are temporary storage containers where you can keep track of important information. In an Excel macro, you can use variables to get around problems that you just can't avoid with the macro recorder.
For example, imagine you want to swap the content in two cells. On the surface, this seems like a fairly straightforward operation. All you need to do is copy the text in one cell, place it in the other, and insert the other cell's text in the first cell. The problem is that once you paste the new cell content into the second cell, you end up overwriting the content you want to put in the first cell. The easiest way around this problem is to use a variable to keep track of the information you need.
To create a variable in VBA, you use the oddly named Dim keyword (short for dimension , which is programmer jargon for "create a new variable"). After the word Dim, you enter the name of the variable.
For example, here's how you'd create a variable named CellContent:
Dim CellContent
Once you've created the variable, you're free to put information in it and take information out. To perform both these operations, you use the familiar equal sign, just as you would with properties.
Here's an example that stores some text in a variable:
CellContent = "Test text"
The following macro puts it all together. It uses a variable to swap the content of two cells.
Sub SwapTextWithCellOnRight( ) ' Create the variable you need. Dim CellContent ' Store the content that's in the current cell. CellContent = ActiveCell.Value ' Copy the value from the cell on the right ' into the current cell. ActiveCell.Value = ActiveCell.Offset(0, 1).Value ' Copy the value from the variable into the ' cell on the right. ActiveCell.Offset(0, 1).Value = CellContent End Sub
Figure 26-7 shows this code in action.
|
26.3.6. Making Decisions
Another programming staple is conditional logic , which is code that runs only if a certain condition is true. There's no limit to the number of ways you can use conditional logic. For example, you might want to perform a different calculation based on the value of a cell, apply different formatting based on the number of cells, or create a different printout depending on the date. All these operations and many more are possible when using conditional logic.
All conditional logic starts with a condition : a simple expression that can turn out to be true or false (programmers call this process evaluating to true or false). Your code can then make a decision to execute different logic depending on the outcome of the condition. To build a condition, you need to compare a variable or property using a logical operator like = (equal to), < (less than), > (greater than), and <> (not equal to). For example, ActiveCell.Value = 10 is a condition. It can be true (if the current cell contains the number 10), or false (if the current cell contains something else).
On its own, a condition can't do anything. However, when used in conjunction with other code, it can become tremendously powerful. Once you've created a suitable condition, you can put it inside a special structure called the If block . The If block evaluates a condition, and runs a section of code if the condition is true. If the condition isn't true, the code is ignored completely.
Here's a macro that looks at the current cell value. If that value exceeds 100, Excel changes it to 100. If the cell value is less than 100, nothing happens, and the current value remains.
Sub MyMacro If ActiveCell.Value > 100 Then ' This value is too big. Change it to the maximum of 100. ActiveCell.Value = 100 End If End Sub
Note that the If block always starts with If and ends with End If. Everything else is conditional and runs only if the condition is true.
An If block can also evaluate several different conditions. Here's an example that considers the current value of a cell. Depending on the cell's value, the If block uses a different calculation to arrive at the sales commission, which it places in another cell.
Sub MyMacro If ActiveCell.Value > 1000 Then ' Use the 5% commission rate. ActiveCell.Offset(0,1).Value = ActiveCell.Value*0.05 Else If ActiveCell.Value > 500 ' Use the 2.5% commission rate. ActiveCell.Offset(0,1).Value = ActiveCell.Value*0.025 Else ' Give a basic comission. ActiveCell.Offset(0,1).Value = 5 End If End Sub
Here, only one segment of code will run. Excel works its way through the If block, testing each condition until one matches. If the cell value is greater than 1,000, it runs the first conditional block of code, and then jumps down to the closing End If statement. It then continues with any other code that's in the macro. If the cell value is less than 1,000 but greater than 500, the first condition is false, and Excel tries the second one, which is true. If no condition matches, Excel runs the code in the final Else clause.
These examples scratch only the surface of what careful conditional logic can do. You can use And and Or keywords to combine conditions, put one conditional block inside another, and much more. To learn more about these approaches, you may want to consult the Web or a dedicated book about VBA programming. For those who are ready to dive right in to the nitty-gritty, try Excel 2003 Power Programming with VBA , by John Walkenbach (Wiley, 2004).
26.3.7. Repeating Actions with a Loop
Computers work particularly well when you need to automate a tedious task. While you might tire out after typing in your 100 th cell value, an Excel macro has no such weakness, and can perform thousands of operations without pausing.
One of the best tools for repeating operations is the loop . A loop is another type of block, one that repeats itself over and over again. Here's an example:
Do ActiveCell.Select Selection.Interior.ColorIndex = 35 Selection.Interior.Pattern = xlSolid ActiveCell.Offset(1, 0).Select Loop
When Excel reaches the final Loop statement at the bottom of this loop, it automatically jumps back to the beginning and repeats your code. However, there's one problemthis process continues infinitely! That means if you make the mistake of running this macro, your worksheet will be locked up indefinitely (until you press the emergency-stop key combination, Ctrl+Break).
To avoid this situation, you should build all loops with an exit condition . This is a condition that signals when the loop should end. For example, here's a rewritten version of the same loop that stops as soon as it finds an empty cell:
Do Until ActiveCell.Value = "" ActiveCell.Select Selection.Interior.ColorIndex = 35 Selection.Interior.Pattern = xlSolid ActiveCell.Offset(1, 0).Select Loop
This technique is quite powerful. For example, consider the macro shown below, which uses a loop to format all the rows in a table. It gives each row an alternating color and stops when there are no values left.
Sub FormatAllCellsInColumn Do Until ActiveCell.Value = "" ' Format the first row. ActiveCell.Rows.EntireRow.Select Selection.Interior.ColorIndex = 35 Selection.Interior.Pattern = xlSolid ' Move to the next row. ActiveCell.Offset(1, 0).Select ' Move down one more row. ActiveCell.Offset(1, 0).Select Loop End Sub
This macro is really an enhanced version of the FormatRow macro shown in the previous chapter. Unlike FormatRow, you need to run this macro once only, and it takes care of all the rows in your worksheet that contain data.
There are actually different types of Excel loops. One other useful loop is the For Each loop, which repeats itself once for every item in a collection of objects. For Each loops come in handy if you need to process all the cells in the current selection.
For example, imagine you want to fix up many cells that have a jumble of upper- and lowercase letters . As you learned in Chapter 10, the Excel PROPER( ) function can do the trick and convert a string like "hElLo THERE" to a respectable "Hello There." The downside is you need to write a separate formula for each cell you want to change. A much better solution is to use the PROPER( ) function from inside a macro to perform the change automatically.
The following macro does the trick. It accesses the PROPER( ) function through the Application.WorksheetFunction object.
Sub FixText( ) ActiveCell.Value = Application.WorksheetFunction.Proper(ActiveSheet.Value) End Sub
This useful macro quickly cleans up the current cell. However, if you select multiple cells and run the macro again, you're likely to be disappointed. The problem is that the FixText macro changes the current cell only. It ignores all the other selected cells.
If you want to take these other cells into account, you need to create a loop using For Each. That's because the For Each block lets you scan through all the selected cells, and run a series of code statements once for each cell.
Here's the revised macro, which cleans up every selected cell:
Sub FixTextInAllCells( ) ' This variable represents the cell you want to change. Dim Cell ' Find all the cells in the current selection. For Each Cell In Selection ' This code repeats once for each cell in the selection. Cell.Value = Application.WorksheetFunction.Proper(Cell.Value) Next End Sub
Figure 26-8 shows the result.
|
Note: There's no limit to what good (or just ambitious) macro code can accomplish. For a truly unusual example, surf to www.geocities.jp/nchikada/pac where you can download a macro that turns an Excel worksheet into a fully functioning imitation of the classic Pacman game. As you weave through the maze, eating pellets and dodging ghosts, it may occur to you that the Excel software package really does have too many features.
26.3.8. Creating Custom Functions
So far, you've seen how you can use code to create powerful macros that take control of Excel. But there's another option for plugging your logic into Excel: You can create custom functions (known in programmer-ese as user -defined functions ). Essentially, a custom function accepts some information (through arguments), performs a calculation, and then provides a result. Once you've created your custom function, you can use it in a formula in a cell, in exactly the same way that you use Excel's built-in functions.
You create custom functions in the same place that you create macrosin modules. In fact, any number of macros and functions can exist side-by-side in a module. The difference is that macros start with the word Sub . Custom functions start with the word Function .
Here's an example of one of simplest possible custom functions:
Function GetMyFavoriteColor( ) GetMyFavoriteColor = "Magenta" End Function
Every function needs to provide a result, which is what appears in the cell when you use the function. To set the result, you use the name of the function, followed by an equal sign and the value, as shown here:
GetMyFavoriteColor = "Magenta"
This means the function provides the text "Magenta" as its result. If the function provided a number instead of a piece of text, you wouldn't use the quotation marks.
To use the function in your worksheet, just create a formula that uses the function, Figure 26-9 shows you how.
|
The GetMyFavoriteColor( ) function is particularly simple because it doesn't use any arguments. But there's no reason you can't get a little fancier. Consider the following custom function, which takes two argumentslength and widthand calculates the total area by multiplying them together:
Function Area(Length, Width) Area = Length * Width End Function
The two arguments are defined in the parentheses after the function name. You can add as many as you want, as long as you separate each one with a comma.
Now, to use this function in a cell in your worksheet, you can use this formula:
=Area(100,50)
This formula uses literal values, but you can easily go one step further and use cell references instead.
Neither the GetMyFavoriteColor( ) nor Area( ) function shows you anything you couldn't already achieve on your own. The following example shows a much more practical function that does something you can't otherwise achieve in Excel banker's rounding . As discussed on Sidebar 8.1 in Chapter 8, Excel's rounding can contribute to biases when adding numbers. To avoid this problem, accountants sometimes use other types of rounding. Banker's rounding is one exampleit rounds .5 up sometimes and down other times, depending on whether the number before it is even or odd. For example, 1.5 always rounds up to 2, while 2.5 always rounds down to 2. With ordinary rounding, .5 is always rounded up, and that can slightly inflate long columns of numbers .
Implementing banker's rounding by hand is tricky, and it requires some conditional logic. The VBA language, however, gives you a powerful shortcut. It provides a built-in Round( ) function that always uses banker's rounding, unlike Excel's ROUND( ) function.
Here's the complete function you need:
Function BankerRound(NumberToRound) BankerRound = Round(NumberToRound) End Function
To test is out, try the following two formulas. The first one produces a result of 0, while the second one has a result of 2:
=BankerRound(0.5) =BankerRound(1.5)
| POWER USERS' CLINIC Other Ways to Program Excel |
| When it comes to programming Excel, the VBA macro language isn't the only game in town. In fact, there're a variety of more powerful ways to take control of Excel. One common choice is to use a full-featured language like Visual Basic, which can interact with Excel through a set of special COM objects. (COM, or Component Object Model, is a Microsoft technology for sharing useful objects between different programs.) Visual Basic lets you use the same set of Excel objects, methods , and properties, but you place your code in a standalone application that has much greater power. This approach gives you the ability to create large-scale solutions, like applications that generate Excel documents or Web sites that read Excel data. Visual Basic isn't the only language that can participate in this scenarioany other programming platform that supports the COM standard also works, including Microsoft's next-generation languages Visual Basic .NET and C#. Another approach, which is new in Excel 2003, is to program a smart document , which is a specialized solution that combines an Excel workbook with intelligent code. By creating a smart document, you can build documents that guide people through the steps needed to use them properly, automatically download up-to-the-minute information from the Web, or even automate a business workflow (routing a document from one person to another, for example). Smart documents present the greatest possibilities for custom code and Excel integration, but they're exceedingly complicated. Before you can create a smart document, you need to master Microsoft's .NET programming platform, and buy two new products: Visual Studio .NET and Visual Studio Tools for the Office System. For more information, go to http://msdn.microsoft.com/vstudio/office. |
EAN: 2147483647
Pages: 185