Excel VBA Macro Programming

Overview

If you import data into a spreadsheet from another application or another file, it is frequently not in the format you would like to see it in. For example, a list of names from another file might be in the format last name , first name (e.g., ‚“Shepherd Richard ‚½), and you may want them in the format first name, last name (e.g., ‚“Richard Shepherd ‚½) in your spreadsheet. There might be thousands of names being imported into your spreadsheet, so how should you deal with it? Should you change each one manually? I have seen some people do this. No, it is much better to write some code to do this and, now that you are aware of the capabilities of Excel VBA, it is fairly straightforward:

Sub reverse_label() For Each window In Windows For Each Worksheet In window.SelectedSheets For Each cell In Application.Selection addr = Worksheet.Name & "!" & cell.Address If Len(Range(addr).Text) > 0 Then temp = Range(addr).Value x = InStr(temp, " ") If x Then temp = Mid(temp, x + 1) & " " & Left(temp, x - 1) Range(addr).Value = temp End If End If Next cell Next worksheet Next window End Sub

The user selects a range by dragging the cursor across it. Multiple sheets may also be used. The code cycles through each window in the Windows object and through each worksheet in the window.selectedsheets object to find the sheets that the user selected. Each cell within the selection is then gone through. The variable addr holds the selected worksheet name and is concatenated with the cell address using the ! character. The text in the cell is tested to ensure that there is something in there; the code does not do anything if there is no text.

Next, a variable called temp is loaded with the cell value. Using the Instr function, a search is made for a space character. The code assumes that there is only one space character between the first name and last name, although you could easily substitute another character for the space, if required. This address is loaded into variable x .

If x has a nonzero value ‚ that is, if it is a true value ‚ then the space has been found. The variable temp is loaded with the part of temp after the space, followed by the space itself, and then the part of temp before the space. Look at this example:

temp = "Richard Shepherd" x = InStr(temp, " ")

The variable x will have the value of 8 because the space is the eighth character in the string.

temp = Mid(temp, x + 1) & " " & Left(temp, x - 1)

Here, Mid(temp, x + 1) is the same as writing Mid(temp, 9) , in this case. Both mean take all characters from the position of character 9 to the end, since I have not specified how many characters to take. This gives the result ‚“Shepherd. ‚½ Left(temp, x ‚ 1) is the same as writing Left(temp, 7) , in this case. This gives the result ‚“Richard ‚½ (7 characters from the left).

The two parts are concatenated together with a space in the middle, and the result is ‚“Shepherd Richard. ‚½ The string temp is then returned to the cell.

Try entering a list of names into a range on a spreadsheet. Leave a space in the middle of some and not in others. Select the range by dragging the cursor over it. Run this code, and you will see that where there is a space, the name has been reversed .

This code can be used for a number of situations and can be easily modified to deal with commas (,) or slashes (/) instead of space characters. For example, if the names were listed ‚“Shepherd, Richard ‚½, you would need to search for the comma character.