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

 

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.