Please excuse me if this is simple question, I'm just starting with GP VBA.
I've got a report I'm trying to pull in some data in the form of a string, I can get the data I'm just having trouble displaying it using a calculated field as the max width is 80 characters (I'm very curious why there is any limit).
I've got the code below that works some times but is not stable. Does anyone know a better way to do this in VBA?
Thanks!
Note is the string I'm trying to break, String1-3 are eventually bound to the calculated fields
Private Sub SplitNote(Note As String, String1 As String, String2 As String, String3 As String)
Dim Counter As Long
Dim SplitLength As Integer
Dim SplitStart As Long
Dim SplitCount As Integer ' Current split count
SplitCount = 1
SplitStart = 1
Dim LastSpaceInstance As Long
LastSpaceInstance = 1
For Counter = 1 To Len(Note)
' if we are at a space and we've reached the string max
' go back to the previous space. Else, we will keep track
' of the last space instance
If Mid(Note, Counter, 1) = " " And SplitLength >= 80 Then
Select Case SplitCount
Case 1
String1 = Mid(Note, SplitStart, LastSpaceInstance - SplitStart)
Case 2
String2 = Mid(Note, SplitStart, LastSpaceInstance - SplitStart)
Case 3
String3 = Mid(Note, SplitStart, LastSpaceInstance - SplitStart)
End Select
SplitStart = LastSpaceInstance + 1
SplitCount = SplitCount + 1
SplitLength = 0
'Keeps track of the last space
ElseIf (Mid(Note, Counter, 1) = " " And SplitLength < 80) Then
LastSpaceInstance = Counter
' If there is an enter
ElseIf (Mid(Note, Counter, 1) = Chr(13)) Then
Select Case SplitCount
Case 1
String1 = Mid(Note, SplitStart, Counter - SplitStart)
Case 2
String2 = Mid(Note, SplitStart, Counter - SplitStart)
Case 3
String3 = Mid(Note, SplitStart, Counter - SplitStart)
End Select
SplitStart = Counter + 1
SplitCount = SplitCount + 1
SplitLength = 0
End If
SplitLength = SplitLength + 1
Next
If (SplitLength <= 80) Then
If Len(Note) > SplitStart Then
SplitLength = Len(Note) - SplitStart
Select Case SplitCount
Case 1
String1 = Mid(Note, SplitStart, SplitLength)
Case 2
String2 = Mid(Note, SplitStart, SplitLength)
Case 3
String3 = Mid(Note, SplitStart, SplitLength)
End Select
End If
End If
