Am I using the Replace function properly

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

Ken Warthen

I've created a custom message box for an Access 2007 application that support
HTML messages. In order to keep the message box reasonably attractive when
the message is lengthy I need to insert a "\n" in between words at a
relatively consistent interval. This will cause line breaks to occur at
about the same place on each line. Setting a text interval initially to 25
characters I used the following code, but the Replace command is not only
replacing the space between words, " ", it's replacing everything that comes
before that space as well. Can anyone tell me what I'm doing wrong here?

Ken

Public Function fncInsertStringBreaks(strLongString As String) As String
On Error GoTo PROC_ERROR
Dim strInsertString As String
Dim strTemp As String
Dim strRemainder As String
Dim intLongStringLength As Integer
Dim intInsertInterval As Integer
Dim intInsertCount As Integer
Dim intInsertPosition As Integer
Dim i As Integer

intLongStringLength = Len(strLongString)
strInsertString = "\n"
intInsertInterval = 25
intInsertCount = Int(intLongStringLength / intInsertInterval)
Debug.Print strLongString
For i = 1 To intInsertCount
intInsertPosition = InStr((i * intInsertInterval), strLongString, " ")
Debug.Print intInsertPosition
strTemp = Replace(strLongString, " ", "\n", (intInsertPosition - 1),
1, vbTextCompare)
Debug.Print strTemp
Next i



PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Function
 
Well, Yes and no. The problem is the Replace is loosing the left portion of
the string. It only returns from the starting position, not from the
beginning of the string, but never fear. Here is a replacement that will work
just fine:

Public Function fncInsertStringBreaks(strLongString As String) As String
Const NewLine As String = "\n"
Const OneSpace As String = " "
Dim i As Long
Dim j As Long
Dim strOut As String

On Error GoTo PROC_ERROR

strOut = LongString
i = 25
Do While True
j = InStr(i, strOut, OneSpace)
If j = 0 Then
Exit Do
End If
strOut = Left(strOut, j - 1) & NewLine & Mid(strOut, j + 1)
i = i + 25
Loop
fncInsertStringBreaks = strOut

PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Function
 
Dave,

Thanks for the help. It's annoying that Access help doesn't mention how the
Replace function does not return the whole string. Anyway, thanks again.

Ken
 
Yeah, Help can sometimes be frustrating. Sort of like:
Instructions for Using Parachute:

Put Arms through harness arm straps.
Put legs throught harness leg straps.
Close harness buckle.
Jump out of airplane.

Then it assumes you know you should pull the rip cord :)
 
Back
Top