Simplifying building of complex strings in code (eg sql statements)

This article is based on Visual Basic 6. Find other Visual Basic 6 articles.

We're long used to ugly code like this:

Dim x as string 

x = "SELECT *" & _
   "FROM products" & _ 
   "WHERE Something = 1234"

Its ugly. We'd really like to see code like this:

Dim SQL as New StringBuilder

SQL "SELECT *"
SQL "  FROM products"
SQL " WHERE Something=12344"
 
MsgBox "Sql Statement=" & SQL.value()

VB6 has the ability to mark one method of a class as the "default" method, so that you dont have to explicitly name the method when referring to the class instance. Notice in the code sample above, we're NOT calling any named method. We *assume* from the context that the string's are being concatenated. Makes sense.

Here's the code for a slimmed down version of StringBuilder.CLS (make it public/creatable)

Option Explicit

Private c As New Collection

'IMPORTANT NOTE: Select this method, click TOOLS|Procedure Attributes|Advanced
'  and select "(default)" in the ProcedureID dropdown!!

Public Sub Add(s) 'Yes, this intentionally a Variant type! More useful this way.
    c.Add CStr(s)
End Sub

Public Function CopyToClipboard()
    Clipboard.Clear
    Clipboard.SetText Me.value
End Function

Public Function Value() As String
    Dim s As String
    Dim I
    s = ""
    For Each I In c
        s = s & I & vbcrlf
    Next
    value = s
End Function