In a recent project, we needed to generate a report to be mailed. The report would be divided into "columns", with the first column being some detail information and the rest of the columns being the data. Since everything needed to line up, we had to go with a fixed-width font (courier). (In R6, tables can be generated in Rich Text fields, which eliminates this need, but the client was using R5).

Each of the data columns were just a few characters wide and the data was certain to fit into that width, but the first column posed a problem. There were only so many characters for the description, and some descriptions would fit and others would be too long. The first suggestion was to trim the description at a fixed length, but they wanted the whole descript to fit. So we had to come up with a way to "word wrap" that description so it would fit within the confines of the report. This tip describes the solution.

What we ended up writing is a generic function that takes a string and a number of characters and returns an array of strings. Each entry is a string with a length less than or equal to the passed-in length. Then the report would go through the array and build lines in the rich text field.

Conceptually, we take the long string and look for "nice" places to split the string. A "nice" place would be a carriage return/line feed (which could occur by themselves or in combination). If a CR/LF can't be found, then use the last space as a "nice" place. If neither of those work, then just grab the first "n" characters (the passed-in value) and wrap there.

Here's the function:

Function WordWrap(inpString As Variant, charsPerLine As Integer) As Variant
   ' Take the input string, wrap at the number of characters per line, and return an array of the wrapped
   ' lines. Note that we don't want to blindly take that many characters - we want to end each line with a
   ' space.
   Dim retVal As Variant
   Dim temp As String
   Dim count As Integer
   Dim temp2 As String
   Dim numChars As Integer
   
   temp = inpString
   Redim retVal(1) As String   ' Size the return array so there's at least one element
   count = Lbound(retVal)
   Redim retVal(count)   ' Size the return array so there's exactly one element
   While Len(temp) > charsPerLine
      temp2 = Left(temp, charsPerLine+1)
      If Instr(temp2, Chr$(13) & Chr$(10)) <> 0 Then   ' If there's a LF/CR in the block
         temp2 = Left(temp2, Instr(temp2, Chr$(13) & Chr$(10))-1)   ' Pull out the part before the LF/CR
         numChars = 3   ' Flag so that later on 2 characters are skipped (moves to the 3rd character)
         ' If the LF/CR was the first thing, we want a blank line in the returned array
         If Len(temp2) = 0 Then
            temp2 = " "
            numChars = 2   ' Subtract one since temp2 now is 1 character instead of none
         End If
      Elseif Instr(temp2, Chr$(10) & Chr$(13)) <> 0 Then   ' If there's a CR/LF in the block
         temp2 = Left(temp2, Instr(temp2, Chr$(10) & Chr$(13))-1)   ' Pull out the part before the CR/LF
         numChars = 3   ' Flag so that later on 2 characters are skipped (moves to the 3rd character)
         ' If the CR/LF was the first thing, we want a blank line in the returned array
         If Len(temp2) = 0 Then
            temp2 = " "
            numChars = 2   ' Subtract one since temp2 now is 1 character instead of none
         End If
      Elseif Instr(temp2, Chr$(13)) <> 0 Then   ' If there's a LF in the block
         temp2 = Left(temp2, Instr(temp2, Chr$(13))-1)   ' Pull out the part before the LF
         numChars = 2   ' Flag so that later on this char is skipped (moves to the 2nd character)
         ' If the LF was the first thing, we want a blank line in the returned array
         If Len(temp2) = 0 Then
            temp2 = " "
            numChars = 1   ' Subtract one since temp2 now is 1 character instead of none
         End If
      Elseif Instr(temp2, Chr$(10)) <> 0 Then   ' If there's a CR in the block
         temp2 = Left(temp2, Instr(temp2, Chr$(10))-1)   ' Pull out the part before the CR
         numChars = 2   ' Flag so that later on this char is skipped (moves to the 2nd character)
         ' If the CR was the first thing, we want a blank line in the returned array
         If Len(temp2) = 0 Then
            temp2 = " "
            numChars = 1   ' Subtract one since temp2 now is 1 character instead of none
         End If
      Elseif Instr(temp2, " ") <> 0 Then ' If there's a space somewhere in the block
         ' Take the last space as the point to wrap - so find the last space in the line
         While Right(temp2, 1) <> " "
            temp2 = Left(temp2, Len(temp2)-1)
         Wend
         numChars = 1   ' Only 1 character (the space) will need to be skipped
      Else   ' None of the above happened. There wasn't a single space to divide the string. Just take
         ' that number of characters and "wrap" there. It won't look "nice", but it has to be done.
         temp2 = Left(temp, charsPerLine)
         numChars = 0   ' Nothing will be skipped
      End If
      If Len(temp2) <> 0 Then ' If there's something to add
         Redim Preserve retVal(count)
         retVal(count) = Trim(temp2)   ' Put smaller string (this line) into return array
         count = count + 1
      End If
      temp = Mid(temp, Len(temp2)+numChars)   ' Remove those characters from the input string
   Wend
   If Trim(temp) <> "" Then   ' Check to see if there are any characters left that haven't been processed yet
      Redim Preserve retVal(count)
      retVal(count) = Trim(temp)   ' Remove any spaces that might exist
   End If
   WordWrap = retVal   ' Return the array of strings (defined as a variant)
End Function