Page 225 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

There is a potential trap here, mainly because many readers will be familiar with the amper­
sand character, &, as the equivalent of the word and. Also, if you have previous programming
experience, you might have used the & operator to indicate a logical "and" in expressions
such as

If ((Range("C5").Value >= 1000) & (Range("D5")<=10)) Then…
Don’t fall into that trap! The VBA concatenation operator & is not the same as the logical
And operator, the latter of which is spelled out as the word And. The previous If condition
statement is properly written as
If ((Range("C5").Value >= 1000) And (Range("D5")<=10)) Then…

The concatenation operator is fairly straightforward to use. For example, you could use the
concatenation operator in conjunction with the LEN function described earlier to indicate
why the data typed into a cell is invalid.

Public Sub VerifyLength()
If Len(ActiveCell.Value) <> 10 Then
MsgBox ("The produ ct code entered is " & LEN(ActiveCell.Value) & _ "
characters, not 10.") Chapter 9
ActiveCell.Value = ""
End If
End Sub
The LEN function and the & operator are also useful if you need to add characters to a cell
value or a variable so the text is the expected length for export to a program that requires
fixed-length data. To add characters to the beginning or end of a string, you use the REPT
function in combination with the & operator. The REPT function has the following syntax:

Application.WorksheetFunction.REPT(string, times)
The string parameter provides the string to be repeated, and times indicates the number of
times the character should be repeated. For example, if you worked for a fast-growing com­
pany that used a variable-length order code to track orders, you might need to change the
5-character code to a 10-character code. That’s no problem in Excel—all you need to do is
repeat a zero at the front of each order code to bring the length up to 10 characters. The fol­
lowing procedure checks the length of the order code string in the cells the user identifies and
adds enough x’s to make the string 10 characters long:

Public Sub MakeTen()

Dim strFirst, strLast, strAllCells, strPadding, strContents As String
Dim intPadding As Integer
strFirst = InputBox("Enter the address of the first cell.")
strLast = InputBox("Enter the address of the last cell.")
strAllCells = strFirst & ":" & strLast

199
Part 4 Advanced VBA