VBA Tipp: Stringzusammensetzung wie in .NET

Aus DBWiki
Wechseln zu: Navigation, Suche

Aufgabenstellung

In .NET gibt es die Methode String.Format um einen String über Platzhalter zusammen zu setzen; die folgende Prozedur bietet diese Funktionalität auch in VBA.

Der Code muss in einem allgemeinen Modul gespeichert werden.

Codebeispiel

Option Explicit
 
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'===========================================================================================================================
' Module    : BW_StringHelper, © Benny Wunder (2014)
' Version   : 1.2.0
' Type      : Standard Module
' Author    : Benny Wunder, bw-services.net
'           : Inspired from SmartClasses which was showed on AEK14 by Michael Zimmermann
' Date      : 2014|05|15
' Purpose   : The module provides procedures and functions to get the comfort of a .NET-String in VBA
' Licence   : This example is free to use and adopt it in your own solutions without changing the author information in the
'           : moduleheader.
' Changelog : 1.0.0 | Initial release
'           : 1.1.0 | Limited parameterlist changed to ParamArray
'           : 1.2.0 | replaced regEx with VBA.Replace (thanks to Josef Pötzl for the hint...)
'===========================================================================================================================
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
'---------------------------------------------------------------------------------------------------------------------------
' Procedure : StringFormat
' Purpose   : Provides the .NET-StringFormat Method to build a complex string value.
'           : StringToFormat contains the string including the variables in curly brackets.
'           : Variables for replacing the placeholders will be set as an ParamArray
'---------------------------------------------------------------------------------------------------------------------------
' Example   : StringFormat("This is a {0} {1} string", "very", "simple") returns ->  "This is a very simple string".
'---------------------------------------------------------------------------------------------------------------------------
Public Function StringFormat(ByVal StringToFormat As String, ParamArray params() As Variant) As String
On Error GoTo Exit_StringFormat
 
Dim result As String
Dim i As Integer
Dim arrParams() As Variant
 
    If IsArray(params(0)) Then
        If UBound(params) = 0 Then arrParams = params(0)
    Else
        arrParams = params
    End If
 
    result = StringToFormat
    For i = 0 To UBound(arrParams)
        result = Replace(result, "{" & i & "}", CStr(arrParams(i)))
    Next i
 
Exit_StringFormat:
    Select Case Err.number
        Case 0
        Case Else
            GoSub CleanUp
            Err.Raise Err.number, Err.Source & vbNewLine & vbTab & "BW_StringHelper.StringFormat", Err.Description
    End Select
    GoSub CleanUp
    Exit Function
 
CleanUp:
    StringFormat = result
    Return
 
End Function