|
Strings in VBA are by their nature resizable. All of
VBA's string functions are used to set or retrieve portions of strings of
variable length. You may find, however, that you need strings of a specific
length, such as when you are writing text-based files that will be
parsed by another application, or simply for alignment in list boxes.
You can declare a string to be a fixed length with a
declaration like
Dim S As String * 20
This declares a string of 20 characters. The disadvantage of this method is
that the length of the string is always fixed at 20. You cannot resize the
string "on the fly" at run time. You can neither lengthen nor
shorten the string. Moreover, you must know the length of the string at
design-time. You can't create fixed length strings at run time. The
SizeString
function on this page can be used to create pseudo-fixed length strings. The
strings are normal, sizable strings, by are sized a specified length,
containing the specified text value on either the left or right region of
the string.
The
following are a few VBA procedures for working with strings.
These functions will work in any version of Office and any application.
They will also work in Visual Basic 6. There is nothing in the code that is specific to Excel.
You can download a
bas module containing these functions
here.
Testing For Fixed Length Strings
At times, you may need to determine whether a string
variable is a fixed length string or a sizable string. You cannot do this
with a function procedure because VBA will convert fixed length strings to
sizable strings when passing the string variable to a procedure. Instead,
you must write code within the procedure in which the string is
declared (unless it is a module-level or global variable) to test whether a
string is fixed or sizable. The following code will do this.
Dim S As String
' normal, sizable string
Dim FS As String * 10 ' fixed length string, 10 chars
Dim OrigLen As Long ' original length of string
Dim OrigVal As String ' original value of string
'''''''''''''''''''''''''''''''''''''''''
' put some arbitrary values in FS And S.
' This is for demonstration purposes only.
' It is not required for the code to work.
'''''''''''''''''''''''''''''''''''''''''
FS = "ABC"
' length of FS is still 10 chars, even though it contains only 3 chars.
S = "DEF"
' length of S is 3 chars.
'''''''''''''''''''''''''''''''''''''''''
' test whether FS is fixed length string
'''''''''''''''''''''''''''''''''''''''''
OrigLen = Len(FS)
' length of FS string
OrigVal = FS
' save original value of FS
FS = FS & " "
' attempt to append a space to the end of FS
If OrigLen = Len(FS) Then ' if the length of
FS didn't change, the string is fixed length
Debug.Print "FS is a fixed length string"
Else
' if the length of FS did change, the string is sizable
Debug.Print "FS is a sizable string"
' restore original value
FS = OrigVal
End If
'''''''''''''''''''''''''''''''''''''''''
' test whether S is fixed length string
'''''''''''''''''''''''''''''''''''''''''
OrigLen = Len(S)
' save length of S
OrigVal = S
' save original value
S = S & " "
' attempt to append a space to the end of S
If OrigLen = Len(S) Then ' if length of
S didn't change, the string if fixed lengh
Debug.Print "S is a fixed length string"
Else
' if the length of S did change, the string is sizable
Debug.Print "S is a sizable string"
' restore original value
S = OrigVal
End If
The output in the Immediate Window in VBA of the
code above is:
FS is a fixed length
string
S is a sizable string
|
|
|
SizeString The following
VBA procedure will return a string variable
containing the specified text, on either the left of the right, padded
with PadChar on either the right or left to make a string Length
characters long.
''''''''''''''''''''''''''''''''''''''
' This enum is used by SizeString
' to indicate whether the supplied text
' appears on the left or right side of
' result string.
''''''''''''''''''''''''''''''''''''''
Public Enum
SizeStringSide
TextLeft = 1
TextRight = 2
End Enum
Public Function SizeString(Text As String, Length As Long, _
Optional ByVal TextSide As SizeStringSide = TextLeft, _
Optional PadChar As String
= " ") As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SizeString
' This procedure creates a string of a specified length. Text is the
original string
' to include, and Length is the length of the result string. TextSide
indicates whether
' Text should appear on the left (in which case the result is padded on
the right with
' PadChar) or on the right (in which case the string is padded on the
left). When padding on
' either the left or right, padding is done using the PadChar.
character. If PadChar is omitted,
' a space is used. If PadChar is longer than one character, the
left-most character of PadChar
' is used. If PadChar is an empty string, a space is used. If TextSide
is neither
' TextLeft or TextRight, the procedure uses TextLeft.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim sPadChar As String
If Len(Text) >= Length Then
' if the source string is longer than the specified length,
return the
' Length left characters
SizeString = Left(Text, Length)
Exit Function
End If
If Len(PadChar) = 0 Then
' PadChar is an empty string. use a space.
sPadChar = " "
Else
' use only the first character of PadChar
sPadChar = Left(PadChar, 1)
End If
If (TextSide <> TextLeft) And (TextSide <> TextRight) Then
' if TextSide was neither TextLeft nor TextRight, use
TextLeft.
TextSide = TextLeft
End If
If TextSide = TextLeft Then
' if the text goes on the left, fill out the right with
spaces
SizeString = Text & String(Length - Len(Text), sPadChar)
Else
' otherwise fill on the left and put the Text on the right
SizeString = String(Length - Len(Text), sPadChar) & Text
End If
End Function
PathCompactPathEx
The converse of the SizeString
is the PathCompactPathEx
Windows API function. While SizeString
is designed to increase the length of a string by padding with a
character, the
PathCompactPathEx is used shorten a string to
a specified length.
PathCompactPathEx
is intended to trim fully-qualified filenames to a
specified number of characters, omitting or truncating a folder name
component of the full file name, but it can be used with any text string.
When used with a fully qualified file name, it omits part of the file
name (such as a folder name) to size the string to fit in the specified
number of characters, replacing the deleted text with the characters "...".
For example
PathCompactPathEx with a length
parameter of 30 will shorten the Excel Application's file name to a truncated
string. It will change
C:\Program Files\Office
2003\OFFICE11\Excel.exe
to
C:\Program Files...\Excel.exe
When
PathCompactPathEx
is used with arbitrary text without '\' characters, it typically just
truncates the InputString, removing the characters on the right and
replacing them with "...".
The following function,
ShortenTextToChars,is
a wrapper function for
PathCompactPathEx that handles the
variable and string buffer handling, and most importantly the validation
of the
NumberOfCharacters parameter. If you pass an
invalid
NumberOfCharacters value to
PathCompactPathEx
, it will cause the application to crash.
PathCompactPathEx is unforgiving of any
invalid input parameters. The
ShortenTextToChars
function uses the
GetSystemErrorMessageText
function, available here and the
TrimToNull function
available here.
Private Declare Function
PathCompactPathEx Lib "shlwapi.dll" _
Alias "PathCompactPathExA" ( _
ByVal pszOut As String, _
ByVal pszSrc As String, _
ByVal cchMax As Long, _
ByVal dwFlags As Long) As Long
Public Function ShortenTextToChars(InputText As String, _
NumberOfCharacters As Long) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShortenTextToChars
' This function returns a shortened version of the InputText parameter
that is
' NumberOfCharacters in length. This function is primarily designed for
use with
' fully-qualified file names. With a file name, the function will remove
or truncate
' an element of the path (e.g., a folder name), replacing the removed
text with the string
' "...". While this is intended for use with file names, it will work
with any text string.
' When used on text that does not contain '\' characters, it typically
just truncates the
' right side of InputText.
' Returns vbNullString is an error occurred.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ResString As String
Dim Res As Long
Dim ErrorNumber As Long
Dim ErrorText As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Ensure that InputText is not an empty string
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If InputText = vbNullString Then
MsgBox "The InputText parameter is an empty string"
ShortenTextToChars = vbNullString
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Test for NumberOfCharacters <= 3. If the InputText
' is 3 or fewer characters, PathCompactPathEx would replace the
' entire string with "...". We don't want that. Return the entire
' InputText.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Len(InputText) <= 3 Then
ShortenTextToChars = InputText
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Test for NumberOfCharacters less than equal to 3.
' If the NumberOfCharacters <= 3, PathCompactPathEx would replace
' the entire InputString with "...".
' Instead, return the left-most characters and get out.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If NumberOfCharacters <= 3 Then
ShortenTextToChars = Left(InputText, NumberOfCharacters)
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Ensure we have a valid number of characters. If NumberOfCharacters
' is less than or equal to 0, or greater than the length of
' the InputText, PathCompactPathEx will crash the application.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If NumberOfCharacters <= 0 Then
MsgBox "The NumberOfCharacters must be greater than 0."
ShortenTextToChars = vbNullString
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Here we test if the length of InputTex is equal to
' NumberOfCharacters. If they are equal, return the
' the entire string and exit. If we allowed
' PathCompactPathEx to process the string, it would truncate
' on the right and replace the last three characters on the
' right with "...". We don't want that behavior -- we
' want to return the entire string.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Len(InputText) = NumberOfCharacters Then
ShortenTextToChars = InputText
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Initialize the buffer. When PathCompactPathEx
' creates its string, it considers NumberOfCharacters
' to include room for the trailing null character. Thus
' the actual number of real characters it returns will be
' NumberOfCharacters-1. Thus, we allocate the string
' to NumberOfCharacters+2 = 1 because we want
' NumberOfCharacters (without the trailing null)
' returned, + 1 for trailing null.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
ResString = String$(NumberOfCharacters + 2, vbNullChar)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Shorten the text with PathCompactPathEx
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Res = PathCompactPathEx(ResString, InputText, NumberOfCharacters, 0&)
If Res = 0 Then
ErrorNumber = Err.LastDllError
ErrorText = GetSystemErrorMessageText(ErrorNumber)
MsgBox "An error occurred with PathCompactPathEx" & vbCrLf &
_
"Error Number: " &
CStr(ErrorNumber) & vbCrLf & _
"Descrition: " &
ErrorText
ShortenTextToChars = vbNullString
Exit Function
End If
'''''''''''''''''''''''''''''''''''''''
' trim to get the charcters to the left
' of the vbNullChar character.
'''''''''''''''''''''''''''''''''''''''
ResString = TrimToNull(Text:=ResString)
'''''''''''''''''''''''''''''''''''''''
' return the result string
'''''''''''''''''''''''''''''''''''''''
ShortenTextToChars = ResString
End Function
TrimToNull
When you are calling Windows API functions, you
are often required to pass in a string variable that will be populated
with the result of the function. An example is the GetWindowsDirectory
function.
In these cases, you must first initialize the string to a given length
(often the length is defined by the constant MAX_PATH
= 260, a Windows-mandated value of the maximum
length of a fully-qualified file name). You can use
either the String$ or
Space$ function to initialize the string to a
given length:
Public Const MAX_PATH = 260
' Windows mandated value
StrValue = String$(MAX_PATH,vbNullChar) ' initialize StrValue to
MAX_PATH vbNullChars.
' or
StrValue = Space$(MAX_PATH)
' initialize StrValue to MAX_PATH spaces.
When the API call returns, the buffer remains at
its original length (the API functions never resize the result buffer).
The API function will put a null character (Chr(0)
or vbNullChar)
at the end of the actual data in the string variable. It is up to you as
the programmer to extract the data to the left of the
vbNullChar.
The following function will do this for you. This is a very simple
function, but if you use a lot of API calls in your code, it is worthy
of its own function.
Public Function
TrimToNull(Text As String) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TrimToNull
' This function returns the portion of Text that is to the left of the
vbNullChar
' character (same as Chr(0)). Typically, this function is used with
strings
' populated by Windows API procedures. It is generally not
used for
' native VB Strings.
' If vbNullChar is not found, the entire Text string
is returned.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos As Integer
Pos = InStr(1, Text, vbNullChar)
If Pos > 0 Then
TrimToNull = Left(Text, Pos - 1)
Else
TrimToNull = Text
End If
End Function
With a little extra code,
TrimToNull can be expanded to trim to any
character or string of characters.
TrimToCharThe following function,
TrimToChar, will return the
text to the left of either the first or last occurrence of a specified
character or string of characters.
Public Function
TrimToChar(Text As String, TrimChar As String, _
Optional SearchFromRight As Boolean = False) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TrimToChar
' This function returns the portion of the string Text that is to the left
of
' TrimChar. If SearchFromRight is omitted or False, the returned string
' is that string to the left of the FIRST occurrence of TrimChar. If
' SearchFromRight is True, the returned string is that string to the
left of the
' LAST occurrance of TrimToChar. If TrimToChar is not found in the
string,
' the entire Text string is returned. TrimChar may be more than one
character.
' Comparison is done in Text mode (case does not matter).
' If TrimChar is an empty string, the entire Text string is returned.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos As Integer
' Test to see if TrimChar is vbNullString. If so, return the
whole string. If we
' don't test here and used InStr as in the next logic block,
an empty string would
' be returned.
If TrimChar = vbNullString Then
TrimToChar = Text
Exit Function
End If
' find the position in Text of TrimChar
If SearchFromRight = True Then
' search right-to-left
Pos = InStrRev(Text, TrimChar, -1,
vbTextCompare)
Else
' search left-to-right
Pos = InStr(1, Text, TrimChar,
vbTextCompare)
End If
' return the sub string
If Pos > 0 Then
TrimToChar = Left(Text, Pos - 1)
Else
TrimToChar = Text
End If
End Function
You can download a
bas module containing these
functions here.
|