VBA Tips

Last week I wrote about a string manipulation issue that a reader was having. This was a great way to illustrate some of the string manipulation techniques that I covered in previous columns. As I noted then, the string manipulation is only one part of the problem. Providing a way to search for all cells that need to be fixed is the second part of the issue.

While Excel provides Find and FindNext methods to search cells, using them correctly isn’t as easy as one might think. The reason that using Find is more difficult than it should be is that as long as it finds at least one match, calls to FindNext will never terminate because it will repeatedly find the same item. This happens because once Excel gets to the last cell in the range it wraps around and resumes searching with the first cell in the range.

The key to using Find and FindNext is saving the address of the first cell that is found and then comparing the address of subsequent finds with the saved address. When a subsequent address equals the saved address, the entire range has been searched.

Listing 1 provides and example illustrating this method. It looks a lot longer than it really is because of all the comments I’ve added.

Listing 1: Using Find and FindNext to search cells on a worksheet

Sub FindExample()
Dim rgFound As Range
Dim sFirstFoundAddress As String

MsgBox “The active sheet has been searched.”, _
vbOKOnly, “Search Complete”
End Sub

One thought that I’d like to share relates to the use of ActiveSheet. Generally, ActiveSheet will resolve to a worksheet object. However, when using ActiveSheet, be sure to make sure that the active sheet is actually a worksheet. It could be a chart sheet for example which may result in runtime errors if you have assumed that ActiveSheet is a worksheet. It is easy to validate that ActiveSheet is a worksheet by examining the Type property as I’ve done near the beginning of Listing 1.

To test this procedure, simply populate several cells in a worksheet with the value “NAR”. With the worksheet active, run the FindExample macro.

A reader writes:

One of our staff asked me if there was a way in Excel to take this information:

NAR123456

and replace it with this

123-456

In other words, the person wants to delete the “NAR” and place a hyphen after the first three numbers. Evidently, she has to do this frequently on large spreadsheets. This has got to be an easy thing to do, but I don’t have the skill set needed to come up with the solution. Can you help? Thanks.

Using the string manipulation functions that I covered in the previous two columns, it is possible to make quick work of this problem. However, depending on the layout of the spreadsheet in question, it may be even easier to handle to problem using formulas. An example of this is shown in Figure 1.

Figure 1: Using formulas to manipulate text

The difference between the “Variable” and the “Fixed” columns is that the formula in the variable column assumes that the number of digits after the “NAR” part is variable. The formula for the fixed column assumes that there are exactly 6 digits after the “NAR” part.

If the text can appear anywhere on the worksheet, using formulas isn’t an option because you don’t know ahead of time where the text will show up and it isn’t practical to re-create the formula everywhere it is needed.

The solution in VBA is twofold. First, build a procedure that will convert the text as desired. Second, provide a way to search all of the cells on the worksheet. I’ll only be covering the first issue this week.

Listing 1 shows a simple implementation of one of several approaches to this problem. This implementation is rather simple because it relies on the end user to perform the “searching” for cells that need to be fixed. Then, by assigning a shortcut key to the FixCell macro such as Ctrl + F, one can select individual cells that need to be fixed and press Ctrl + F to fix the cell. FixCell determines if the value in the cell is actually a value that needs to be fixed and, if so, calls the ConvertString function to perform the conversion. Finally, FixCell updates the active cell with the value returned from ConvertString.

Listing 1: A VBA function to convert the string.

' Very simplistic procedure to
' try and fix a cell
' Assigned to shortcut key: ctrl+f

' Return the result
ConvertString = sResult
End Function

Providing a way to search a worksheet for all possible cells that need to be fixed requires an entire column which I will save for another week.

Manipulating text is a frequent programming task. Fortunately, VBA has numerous functions that make this easy work. The purpose of this week’s column is to illustrate the use of some of the text functions that I tend to use the most.

Text in VBA is usually stored in string variables. When discussing text manipulation, then, it is common to refer to text as a string. Perhaps the most common need when it comes to manipulating strings is to concatenate two strings together. For this, you don’t even need to use a function — use the ampersand (&) character. Listing 1 demonstrates string concatenation.

Listing 1: A demonstration of string concatenation.

Sub StringConcatenation()
Dim sFirstName As String
Dim sLastName As String
Dim sFullName As String

sFullName = sFirstName & " " & sLastName
Debug.Print "His name is " & sFullName
End Sub
— The output of this listing is: —
His name is Ronald McDonald
His name is Ronald McDonald

After concatenation, the other basic needs relate to simple string modification such as stripping characters from the beginning or end of a string. The following table documents the basic string modification functions. These functions are demonstrated in Listing 2.

Function Name and Purpose

Left (string, n) Returns the n leftmost characters from a string
Right(string, n) Returns the n rightmost characters from a string
Mid (string, starting at, n) Returns n characters from a string starting with the character in the "starting at" position
LTrim (string), RTrim (string), Trim (string) Removes leading (LTrim), trailing (RTrim), or leading and trailing (Trim) white space
LCase (string), UCase (string) Converts a string to lower case (LCase) or upper case (UCase)

Listing 2: A demonstration of string manipulation.

Sub StringManipulation()

' Using LCase and UCase
Dim sMixedCase As String
sMixedCase = "New York, NY"
Debug.Print UCase(sMixedCase)
Debug.Print LCase(sMixedCase)
End Sub

— The output of this listing is: —
The short zip code is: 55021
The local prefix is: 555
The last four digits are: 1234
Using LTrim:***Interesting Text ***
Using RTrim:*** Interesting Text***
Using Trim:***Interesting Text***
NEW YORK, NY
new york, ny

The ability to employ basic string manipulation opens up a world of possibilities beyond what you can achieve with simple macro recording. Next week I’ll continue this discussion by explaining other text manipulation functions and techniques.

Mark Mudron offered this response to Steve Hansen’s programming article from last week’s issue:

Referring to the Essential Office Programming section of your most recent news letter regarding Application.ScreenUpdating…
I discovered this handy command quite awhile ago while working on large spreadsheets. The performance gain, as was pointed out, was enormous. My problem is, how does the user know that the macro is doing something? If the macro takes too long to run, the user may get impatient and try to terminate the macro or the application.

In the past, I have taken a bit of a performance hit to highlight the first cell of the row the macro is working on (or every nth row if you want to write an if/then statement). This allows the user to know something is happening without having to show every update.

Is there a better way to do this? Is there a way to display and update a progress box while still allowing the macro to run in the background?

You bet there is, Mark. Thanks for the question. This week Steve provides the answer.

- - -

A couple of weeks ago, I discussed the use of Application.ScreenUpdating in Excel to improve the performance of your macros and prevent screen flicker while your macro executes. When you turn screen updating off, it is helpful to provide some sort of feedback to the user so that they know that their computer isn’t locked up.

The method I prefer to use is using Application.StatusBar, which allows you to change the information appearing in the status bar. While the visibility of the status bar can be changed by the user, it is shown by default and in my experience, very few people ever hide it. It is a very non-intrusive way to display progress information.

While some programmers occasionally worry that “my users won’t notice it”, trust me -– they will. Based on solutions I’ve deployed, I’ve never had any feedback to the contrary. When it comes to progress information, subtle is good.

One of the reasons subtle is good when it comes to progress information is that providing progress information has a performance cost. If the reason you are turning screen updating off is to maximize performance, you probably don’t want to implement a progress information system that consumes all of the performance gain. The key is to find a refresh rate that provides reasonable progress information without a noticeable impact on performance.

Listing 1 provides an example of how to use Application.StatusBar in a common scenario –- providing progress information as you process individual rows in Excel.

Listing 1: A demonstration of Application.StatusBar

Sub StatusBarExample()
Dim lRow As Long
Dim ws As Worksheet
Dim lFrequency As Long

' Turn screen updating on and reset the status bar
Application.ScreenUpdating = True
Application.StatusBar = False
Set ws = Nothing
End Sub

To experiment with the performance impact of using Application.StatusBar, run the StatusBarExample a few times with different values for lFrequency. For example try 10, 100, and 1000. This example uses the Mod function to determine if the status bar should be updated. Mod divides two numbers and returns the remainder of the division.

Since you can write arbitrary text to the StatusBar, you can use lots of other schemes besides rows. For example, a batch processing routine may use the filename of the file it is currently processing. A routine that harvests information from numerous worksheets may display the name of the current worksheet. A process that reads data from a database may display a primary key or other identifying attribute (perhaps combined with a frequency modifier such as every nth row).

In summary, Application.ScreenUpdating and Application.StatusBar are very complimentary when used together to maximize the performance of a macro while providing some basic progress information so that the computer doesn’t appear locked-up.

One key to becoming a more effective programmer is to develop an understanding of procedure arguments and how to use them. Once you develop an understanding of arguments, you will see programming tasks in a whole new light and dramatically increase your productivity as well as the quality of your code.

As an example, consider Listing 1. This is a macro I recorded in Microsoft Word to insert a picture and add an orange border to it. If you always add the same picture with the same color borders, perhaps this macro would be useful. However, it would be much more useful and efficient if you modified this macro so that it could be used in more situations. For example, maybe you want to specify which file to add and what color the borders should be.

Listing 1: A simple macro to insert a picture in Word

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/21/2007 by Steve Hansen
'
' Note the reference to a hard-coded file name – you should update this
Selection.InlineShapes.AddPicture _
FileName:= "C:\DSC00007.JPG", _
LinkToFile:=False, _
SaveWithDocument:=True

With Selection.InlineShapes(1)
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorOrange
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorOrange
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorOrange
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorOrange
End With
.Borders.Shadow = False
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth050pt
.DefaultBorderColor = wdColorOrange
End With
End Sub

You can think of arguments as variables that you define in the declaration of a procedure (the Sub or Function statement) with an added twist — other procedures that call the procedure can specify the value of these variables.

Listing 2 shows a modified version of Listing 1 that uses arguments to specify the filename of the image to add as well as the color of the borders. I also included a procedure named AddTestImage that demonstrates how to call another procedure that uses arguments. This procedure displays a dialog box that prompts the user to select an image to add.

Listing 2: A simple macro to insert a picture in Word with arguments

Sub AddTestImage()
Dim dlgOpen As FileDialog

With Selection.InlineShapes(1)
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = clrColor
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = clrColor
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = clrColor
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = clrColor
End With
.Borders.Shadow = False
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth050pt
.DefaultBorderColor = clrColor
End With
End Sub

Using arguments is an essential concept to learn in order to start building more elaborate solutions instead of what you create simply by recording macros. As you can see here, it is relatively simple to add arguments to a recorded macro so that you can use it in more situations.

I’ll leave it as an exercise for you to figure out how to simplify the AddImage procedure even more. Here are a couple of clues: you can break it up into multiple procedures that use arguments and the AddImage has extra “baggage” that you can get rid of.