clsStringBuilder

In .NET programming, there is a StringBuilder object. I have found it to be quite useful in creating formatted text strings of multiple lines. I especially like it for creating SQL query text.

This does not exist in VBA text. This is my modest attempt of adding some of that functionality to VBA code.

Option Compare Database 
Option Explicit 
  
Dim strValue As String 
  
Public Sub Append(strNewText As String) 
    strValue = strValue & strNewText 
End Sub 
  
Public Sub AppendLine(Optional strNewText As String = "") 
    If strNewText <> "" Then 
        strValue = strValue & strNewText & vbCrLf 
    Else 
        strValue = strValue & vbCrLf 
    End If 
End Sub 
  
Public Sub Clear() 
    strValue = "" 
End Sub 
  
Private Sub class_initialize() 
    strValue = "" 
End Sub 
  
Public Property Get ToString() 
    ToString = strValue 
End Property 

Here is an example of how to use it:

Sub TestStringBuilder()
    Dim strSQL As New clsStringBuilder
    
    With strSQL
        .Clear
        .AppendLine "INSERT INTO [Email Recipients for Vendor Certification Expiration Matters] ( [SQE Name], [SQE zID], [Email Address], [Direct Email] )"
        .AppendLine "SELECT [sSelect Expired or About to Expire Vendor Certifications 01].[SQE Name], [sSelect Expired or About to Expire Vendor Certifications 01].[SQE zID], [ATI All Active Users].[Allison Email], True AS [Direct Email]"
        .AppendLine "FROM [sSelect Expired or About to Expire Vendor Certifications 01] LEFT JOIN [ATI All Active Users] ON [sSelect Expired or About to Expire Vendor Certifications 01].[SQE zID] = [ATI All Active Users].[User ID];"
        Debug.Print .ToString
    End With
    
    
End Sub