Multiple Apps

C# Style StringBuilder for VBA

Ease of Use

Intermediate

Version tested with

2000, 2003, 2007 

Submitted by:

DarkSprout

Description:

Build easy to read strings for SQL, MsgBoxs, output for Labels or even Text Boxes. in the style of the C# String Builder function. 

Discussion:

You build a very long SQL string that would be run with DoCmd.RunSQL, you could be having trouble with the variables and their locations within the string, now you can read the syntax without the string build getting in the way. Want to output a message box to the user and an word is wrapped in quotes, you would have to use: text..." & Chr(34) & "Message in quotes" & Chr(34) & "...more text... But with StrBuild use the following: text...\u0022Message in quotes\u0022...more text.... The \u0022 is a Hex value for the number 32, so you could send any character to a text box just using using the \uNNNN switch Also. you won't have to use Vbcrlf/VbNewLine in a Msgbox for newlines any more just use the \n switch, example: MsgBox StrBld("Line 1...\nLine 2\nLine 3") <-- Much less to type, and easier to read. It also enables you to switch the Case:= Proper\Title, Sentence, Upper and Lower. 

Code:

instructions for use 

			
 '// #Function#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 '//  Procedure : StrBld                                                                     '
 '//  Purpose   : C# Style StringBuilder for VBA                                             '
 '//  Author    : Darryl S. Drury [Feb/Oct08]                                                '
 '//  ToUse     : \\n For 2 NewLines, \n For a single Newline, \tab for Tab character.       '
 '//              \uNN (NN) being the 2Digit hexadecimal value of a character number.        '
 '//              {index} as insertion points for the Items, starting at {0}.                '
 '//              Format Case: \p (Proper Or Title Case) \> (UPPER CASE) \< (lower case)     '
 '//                  \s (Sentence case) - must be first two Characters of string.           '
 '//  Example   : ?StrBld("\>\u22{0}x{1}\u4D\u50\u4C{2}\u22\nTest", "e", "A", "E")           '
 '//  Returns   : String with the new/requested formating                                    '
 '// '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
Public Function StrBld(ByVal strIn As String, ParamArray Items() As Variant) As String 
    Dim strStore As String, sSentCase() As String 
    Dim i As Integer, iPos As Integer, sChar As String * 2 
     
    On Error GoTo ERR_HANDLE 
    strStore = strIn 
     
     '// Items //
    If Not IsMissing(Items) Then 
        For i = 0 To UBound(Items) 
            If Not IsMissing(Items(i)) Then 
                strIn = Replace$(strIn, "{" & i & "}", Items(i)) 
            End If 
        Next i 
    End If 
     
     '// Tab & Newline(s) //
    strIn = Replace$(strIn, "\tab", vbTab, , , vbBinaryCompare) 
    strIn = Replace$(strIn, "\\n", DBLINE, , , vbBinaryCompare) 
    strIn = Replace$(strIn, "\n", vbNewLine, , , vbBinaryCompare) 
     
     '// Unicode //
    Do While InStr(1, strIn, "\u", vbBinaryCompare) > 0 
        iPos = InStr(1, strIn, "\u", vbBinaryCompare) 
        sChar = Replace$(Mid$(strIn, iPos, 4), "\u", "") 
        strIn = Replace$(strIn, "\u" & sChar, Chr$(Val("&H" & sChar))) 
    Loop 
     
     '// Format Case, nn[n]... is the ASCII value of the second charater in lower case //
    If Left$(strIn, 1) = "\" Then 'only format if escape-switch is used
        Select Case Asc(Mid$(strIn, 2, 1)) 
        Case 60:  strIn = StrConv(Replace$(strIn, "\<", "", , , vbBinaryCompare), vbLowerCase) 
        Case 62:  strIn = StrConv(Replace$(strIn, "\>", "", , , vbBinaryCompare), vbUpperCase) 
        Case 112: strIn = StrConv(Replace$(strIn, "\p", "", , , vbBinaryCompare), vbProperCase) 
Case 115: ' vbSentenceCase! '{
            sSentCase = Split(Replace$(strIn, "\s", "", , , vbBinaryCompare), ".") 
            For i = 0 To UBound(sSentCase) 
                sSentCase(i) = Trim$(sSentCase(i)) 
                For iPos = 1 To Len(sSentCase(i)) 
                    If Mid$(sSentCase(i), iPos, 1) Like "[A-Z]" Then Exit For 
                Next iPos 
                Mid$(sSentCase(i), iPos, Len(sSentCase(i))) = UCase$(Mid$(sSentCase(i), iPos, 1)) _ 
                & LCase$(Mid$(sSentCase(i), iPos + 1, Len(sSentCase(i)))) 
            Next i 
            strIn = Trim$(Join$(sSentCase, ".  ")) 
             '}
        End Select 
    End If 
     
    StrBld = strIn 
     
EXIT_PROC: 
    Erase sSentCase 
    Exit Function 
     
ERR_HANDLE: 
    Select Case Err 
    Case 3021, 94, 5 
         '// Null items parsed into the ParamArray //
    Case Else 
         '// VBA.MsgBox <~~ Prevents Errors With Overloaded MsgBox //
        VBA.MsgBox Error$ & ", With StrBld()" & DBLINE _ 
        & "Please Check String = '" & strStore & "'", 4160, "Error#" & Err 
    End Select 
    Err.Clear 
    Resume Next 
End Function

How to use:

  1. 1. Place into a Public Module
  3. 2. Create a new or edit an existing string that uses standard string building.
  4. example:
  5. strInsrt1 = "my": strInsrt2 = "string"
  6. MsgBox = "This is " & strInsrt & " sample " & strInsrt2 & "."
  7. Which would become:
  8. strInsrt1 = "my": strInsrt2 = "string"
  9. MsgBox = "\>This is {0} sample {1}.",strInsrt, strInsrt2)
  11. See attachment for the code in a Module (in an Excel file)
 

Test the code:

  1. Paste this into the immediate window of the Code Editer (To Open: Ctrl + G)
  2. MsgBox StrBld("\u0022{0}X{1}\u004D\u0050\u004C{2}\u0022\nTest", "E", "A", "E")
  4. The MsgBox example is properly a little converluted, but, it does show how powerful it can be.
  5. Try:
  6. MsgBox StrBld("Line 1...\nLine 2\nLine 3") <-- Much less to type, and easier to read.
  8. My favourite example is this SQL code - which I think makes for very easy reading
  9. 'Access Error Log
  10. sql = StrBld("INSERT INTO tbl_CORE_ErrorLog (nErrorNumber, txtErrorMsg, txtUser, txtFormName, txtProcedureName) " _
  11. & "SELECT {0}, '{1}', '{2}', '{3}', '{4}' ;", errNumber, errText, strUser, errForm, errProcedure)
  12. DoCmd.RunSQL sql
  14. Also try this Message Box as an example of Complex Formatting.
  15. MsgBox StrBld("Calculator Help\\nUse the mouse or keyboard for numbers and\narithmetical operators.\\n" _
  16. & "Use the mouse or the listed keys for the\nfollowing operations:\\n\tab[Esc]\tab\tabCl\n\tabShif" _
  17. & "t+[Esc]\tabCa\n\tab[BackSpace]\tabRemove last character\n\tab[Enter]\tab\tab=\n\tab[F1]\tab\tab" _
  18. & "This Help\n\tab[F2]\tab\tabM+\n\tab[F3]\tab\tabM-\n\tab[F4]\tab\tabRm\n\tab[F5]\tab\tabCm\n\tab" _
  19. & "Ctrl+[F4]\tab\tabClose"), vbOKOnly, "HelpOptions ~ Example of StrBld() in use"
 

Sample File:

StrBld.zip 17.66KB 

Approved by mdmackillop


This entry has been viewed 69 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express