Excel VBA Macro Programming

Overview

The example presented in this chapter allows the user to make a selection, which can go across several worksheets, and then make a conversion of labels into numbers or numbers into labels where necessary. If you often paste data in from other applications, you will frequently find that the data is pasted in as the wrong data type. Excel does not have a block command to get around this. You can use a formula to do it, but it means a great deal of copying and pasting to get the results you want. The process I will show here allows you to do it in one easy action.

If data is imported or pasted in from another application, it often ends up as labels rather than numbers. This means that it does not follow the normal numeric formatting and is usually left aligned in a cell . If Excel thinks it is a label, even formatting numerically will not work. If there is a also a mixture of numeric and text characters within a cell, it's impossible to do anything with the formulas based on that cell because the cell will always be recognized as a string of characters rather than a number.

In writing the code, there are certain safeguards to bear in mind. The conversion must take place only if there is a numeric value within the string that is capable of being converted into a number. Also, if the cell holds a formula, conversion must not take place, because the formula will be lost, with disastrous results to the spreadsheet.

Here is the sample code:

Sub label_to_number() Dim addr As String For Each window In Windows For Each Worksheet In window.SelectedSheets For Each cell In Application.Selection addr = Worksheet.Name & "!" & cell.Address If IsNumeric(Range(addr).Value) = True And Range(addr).Text _ <> "" And Range(addr).HasFormula = False Then Range(addr) = Val(Range(addr)) End If Next cell Next worksheet Next window End Sub

Notice how the loops are nested and indented ‚ there are several different levels that the macro works through.

The first thing to do is to define a variable to hold each cell address within the selected range; this is called addr . The subsequent code will then get the selected range.

The main problem with the macro is that the user may make a selection across a number of worksheets and want your macro to work across that selection. If you do not take this into consideration, you might find that your macro works only on the first selected worksheet, ignoring the rest of the selection. The user will think the macro has gone through the entire selection of worksheets because there is nothing to indicate that it has not. The user, discovering this later on, won't understand what has happened .

Therefore, you not only want the selection from the current worksheet, but you may also want to give the user the flexibility to be able to select multiple sheets by clicking the sheet tabs. The user may also wish to go across more than one workbook.

This gives some idea of the complexities involved when designing a macro. It is straightforward enough to deal with what is going on for a single worksheet, but what happens if multiple worksheets or workbook selections are made?

The selection object on the application object will give the actual cell addresses of cells that have been selected, but it will not tell you the name of the sheet it has occurred on and if there are multiple sheets involved. To do this, you first cycle through the Windows object using the syntax For Each..Next. Each worksheet creates a window that is part of the Windows collection. This is convenient because it does not matter how many sheets or workbooks are loaded. Do not confuse this with Windows itself ‚ this collection is unique to windows within Excel. This will give you the name of each worksheet within Excel, regardless of the workbook it is in. You can then cycle through the worksheets, which are actually selected by using the SelectedSheets collection.

Within each selected worksheet, look at the Selection object. This is defined as the selection the user made by dragging the cursor over a group of cells so that a highlighted block appears. When this code is run, the Selection object will have the address of that selection.

There are a number of cells in the Selection object, so cycle through the selected cells using the syntax For Each..Next again. The cell address gives you the coordinates of that cell but does not tell us what worksheet it is sitting on. This is the reason for the first cycle through the Windows object to get the names of all the worksheets involved.

The cell address is then concatenated to the worksheet name using the ! character and stored in the variable addr . At this point, you need to check the cell for certain attributes, so you use an If statement. Use the Range method on the variable addr to do this.

The code checks that there is a numeric value in the cell, that the cell is not empty, and that it is not a formula. It uses the IsNumeric function and the HasFormula property. There is also a simple expression to check if the cell is empty. If all conditions are satisfied, it sets the cell value to the numeric value using the Val function.

The End If statement ends the If condition, and the remaining Next statements cycle through the collections until all the selections are completed. In this way, all selected sheets are worked through and all selected cells within those sheets are cycled through, as shown in Figure 20-1.

Figure 20-1: Changing labels to numbers in a spreadsheet

Some label data has been placed in cells, and a formula has been put in at cell A1. To turn a number into a label, insert the single quote (') character first, and it will left align as a label. Then make a selection by dragging the cursor across the range of data.

Run the macro, and anything that is a proper numeric will be changed to a number in the cell. Blank cells and formulas will remain the same.

With some ingenuity, it is not difficult to make this code go further. Strings that are partly numeric could have any nonnumeric characters stripped out from them and then converted to be numeric. The same code can also be used in reverse to convert cell contents back to labels:

Sub contents_to_label() Dim addr As String For Each window In Windows For Each Worksheet In window.SelectedSheets For Each cell In Application.Selection addr = Worksheet.Name & "!" & cell.Address If Range(addr).Value <> 0 Then Range(addr) = "'" & _ CStr(Range(addr)) Next cell Next worksheet Next window End Sub

This code is identical to the conversion of labels to numerics except for the actual conversion statement for the cell, which checks that there is a value there and uses the CStr function to change it to a string. It concatenates the single quote (') character in front so that it will be read as a label in the cell.