PDA

Vollständige Version anzeigen : StringFormat - .NET-Komfort bei der Stringzusammensetzung in VBA


CptChaos
28.08.2015, 10:51
Nachdem ich die Möglichkeit von String.Format in .NET kennen gelernt habe, war es mir immer wieder ein "Graus" einen String in VBA zusammen zu setzen.

Daher habe ich mir bereits vor geraumer Zeit eine Prozedur geschrieben, welche den Mechanismus aus .NET nachbildet und in VBA anbietet.
Option Explicit

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'=============================================================================== ============================================
' Module : BW_StringHelper, © Benny Wunder (2014)
' Version : 1.1.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
'=============================================================================== ============================================
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


'---------------------------------------------------------------------------------------------------------------------------
' 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 regEx As Object
Dim arrParams() As Variant

If IsArray(params(0)) Then
If UBound(params) = 0 Then arrParams = params(0)
Else
arrParams = params
End If

Set regEx = CreateObject("VBScript.RegExp")
With regEx
.Global = True
.IgnoreCase = True

result = StringToFormat
For i = 0 To UBound(arrParams)
.Pattern = "\{" & i & "\}"
result = .Replace(result, CStr(arrParams(i)))
Next i
End With

Exit_StringFormat:
Select Case Err.number
Case 0
Case 429
result = StringToFormat
GoSub CleanUp
Err.Raise Err.number, Err.source & vbNewLine & vbTab & "StringHelper.StringFormat", "Could not create RegEx-Object"
Case Else
GoSub CleanUp
Err.Raise Err.number, Err.source & vbNewLine & vbTab & "StringHelper.StringFormat", Err.description
End Select
GoSub CleanUp
Exit Function

CleanUp:
Set regEx = Nothing
StringFormat = result
Return

End Function

CptChaos
03.11.2015, 15:02
Update Version 1.2.0 (Danke an Josef Pötzl für den Hinweis)
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 & "StringHelper.StringFormat", Err.description
End Select
GoSub CleanUp
Exit Function

CleanUp:
StringFormat = result
Return

End Function