Yesterday, I came across the following thread:
Run Excel Trim Function from Access
I tried to find this on AWF but was unable to find it, apologies if I am covering ground already covered. I learned yesterday that Trim in Excel works differently in Excel than Access: Trim in Excel removes all spaces but one from a given string: Trim(“1234 567 89”) returns “1234…
in which NauticalGent was asking how one could run an Excel WorkSheet function from within Access to remove multiple spaces that appeared in strings.
Running Excel WorkSheet Functions From Another Application
To answer the direct question, but not the point of this article, I wrote an article on this many, many moons ago. So if this is something you are truly looking to do then check it out:
VBA - Excel - Execute/Run an Excel Worksheet Function
Have you ever needed to use an Excel function within one of your databases, or other application. Below is a generic examples of how you can call just about any Excel function using VBA to extend your database’s functionalities even further.
Trimming Multiple Spaces In a String
Back to the root question:
How can we remove multiple spaces in a string (and reduce them to a single space, like Excel’s Trim function)?
Odd, No???
Am I the only one that finds it odd that Microsoft didn’t standardize the Trim command throughout Office?
Trim in Excel != Trim in VBA???
Why would you have the same named command act differently in different apps/situations?
Moving on.
Now, in the thread, several excellent VBA solution were offered.
For instance, my thought when reading the thread was to simply loop over the String performing a Replace until no multiple spaces were found. Eventually, that solution was proposed. Here would be my pure VBA version:
'---------------------------------------------------------------------------------------
' Procedure : TrimMultipleSpaces
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove multiple spaces within a string and optionally remove leading and
' trailing spaces as well
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : String to remove multiple spaces from
' bRemoveLeadingTrailingSpaces : Whether leading and trailing spaces should be removed
'
' Usage:
' ~~~~~~
' TrimMultipleSpaces("1234 567 89")
' Returns -> '1234 567 89'
'
' TrimMultipleSpaces(" 1234 567 89 ")
' Returns -> '1234 567 89'
'
' TrimMultipleSpaces(" 1234 567 89 ", False)
' Returns -> ' 1234 567 89 '
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2024-02-07 Initial Release
'---------------------------------------------------------------------------------------
Public Function TrimMultipleSpaces(ByVal sInput As Variant, _
Optional bRemoveLeadingTrailingSpaces As Boolean = True) As String
On Error GoTo Error_Handler
Do Until InStr(1, sInput, " ") = 0
sInput = Replace(sInput, " ", " ", 1)
Loop
If bRemoveLeadingTrailingSpaces Then sInput = Trim(sInput)
TrimMultipleSpaces = sInput
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: TrimMultipleSpaces" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
However, since I’ve been on a RegEx kick as of late, I thought it would be fun to explore this problem via RegEx as it is made specifically to perform such actions.
So below I offer 2 different RegEx functions for replacing any number of multiple spaces from a string:
- Remove multiple spaces within a string (but will leave leading/trailing space)
- Remove multiple spaces within a string and remove leading/trailing spaces
Remove multiple spaces within a string (but will leave leading/trailing space)
'---------------------------------------------------------------------------------------
' Procedure : TrimMultipleSpaces
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove multiple spaces within a string (but will leave leading/trailing space)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : String to remove multiple spaces from
'
' Usage:
' ~~~~~~
' TrimMultipleSpaces("1234 567 89")
' Returns -> '1234 567 89'
'
' TrimMultipleSpaces(" 1234 567 89 ")
' Returns -> ' 1234 567 89 '
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2024-02-07 Initial Release
'---------------------------------------------------------------------------------------
Public Function TrimMultipleSpaces(ByVal sInput As Variant) As String
On Error GoTo Error_Handler
Dim oRegEx As Object
If Not IsNull(sInput) Then
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "\s{2,}" 'will still leave leading/trailing space
.Global = True
.IgnoreCase = True
.MultiLine = True
End With
TrimMultipleSpaces = oRegEx.Replace(sInput, " ")
End If
Error_Handler_Exit:
On Error Resume Next
If Not oRegEx Is Nothing Then Set oRegEx = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: TrimMultipleSpaces" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
Remove multiple spaces within a string and remove leading/trailing spaces
'---------------------------------------------------------------------------------------
' Procedure : TrimExtended
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove multiple spaces within a string and remove leave leading/trailing spaces
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : String to remove multiple spaces from
'
' Usage:
' ~~~~~~
' TrimExtended("1234 567 89")
' Returns -> '1234 567 89'
'
' TrimExtended(" 1234 567 89 ")
' Returns -> '1234 567 89'
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2024-02-07 Initial Release
'---------------------------------------------------------------------------------------
Public Function TrimExtended(ByVal sInput As Variant) As String
On Error GoTo Error_Handler
Dim oRegEx As Object
If Not IsNull(sInput) Then
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^\s+|\s$|\s+(?=\s)" 'Remove multi-space and leading/trailing spaces
.Global = True
.IgnoreCase = True
.MultiLine = True
End With
TrimExtended = oRegEx.Replace(sInput, "")
End If
Error_Handler_Exit:
On Error Resume Next
If Not oRegEx Is Nothing Then Set oRegEx = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: TrimExtended" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
So just to offer up another possible solution to a problem.
SHOV Is The Way To Go
As always, RegEx is another approach that benefits greatly from implementing Self-Healing Object Variables (SHOV). So be sure to consider it to get the best performance possible! You can learn more by reviewing:
Self-Healing Object Variables
So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables! So What Are Self-Healing Object Variables? In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session. If reset, they will reinitialize again automatically. They are like Global Variables on steroids. It is just one more step you can take as a developer to improve the overall performance and give your application a little boost, and this for any VBA application (not just Access)! My first…
In the original thread, I saw comments my code wasn’t optimized. They obviously skimmed the article and hadn’t caught the above section on SHOV. Thus, I thought I’d include a full example (see below), but do read the above article for the details and to learn who you can apply the same principle to many other Objects we often use in VBA coding.
Implementing Self-Healing Object Variables (SHOV) With RegEx
Typically, I create a separate VBA Module and copy/paste the following. Once this is in place, we are ready to use RegEx SHOV coding.
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft VBScript Regular Expressions X.X
#Const RegEx_EarlyBind = False
#If RegEx_EarlyBind = True Then
Private pRegEx As RegExp
#Else
Private pRegEx As Object
#End If
'Self-healing oRegEx property
#If RegEx_EarlyBind = True Then
Public Function oRegEx() As RegExp
#Else
Public Function oRegEx() As Object
#End If
On Error GoTo Err_Handler
If pRegEx Is Nothing Then
Debug.Print "*** Setting oRegEx ***"
#If RegEx_EarlyBind = True Then
Set pRegEx = New RegExp
#Else
Set pRegEx = CreateObject("VBScript.RegExp")
#End If
End If
Set oRegEx = pRegEx
Exit_Procedure:
Exit Function
Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Property Get oRegEx" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Exit_Procedure
End Function
Public Sub oRegEx_Clear()
'Be sure to always run this when closing your Form/DB to avoid
' hidden instances from running in the background!
Set pRegEx = Nothing
End Sub
Adapting Our Functions
'---------------------------------------------------------------------------------------
' Procedure : TrimMultipleSpaces_SHOV
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove multiple spaces within a string (but will leave leading/trailing space)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : String to remove multiple spaces from
'
' Usage:
' ~~~~~~
' TrimMultipleSpaces_SHOV("1234 567 89")
' Returns -> '1234 567 89'
'
' TrimMultipleSpaces_SHOV(" 1234 567 89 ")
' Returns -> ' 1234 567 89 '
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2024-02-07 Initial Release
'---------------------------------------------------------------------------------------
Public Function TrimMultipleSpaces_SHOV(ByVal sInput As Variant) As String
On Error GoTo Error_Handler
If Not IsNull(sInput) Then
With oRegEx
.Pattern = "\s{2,}" 'will still leave leading/trailing space
.Global = True
.IgnoreCase = True
.MultiLine = True
End With
TrimMultipleSpaces_SHOV = oRegEx.Replace(sInput, " ")
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: TrimMultipleSpaces_SHOV" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : TrimExtended_SHOV
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove multiple spaces within a string and remove leave leading/trailing spaces
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : String to remove multiple spaces from
'
' Usage:
' ~~~~~~
' TrimExtended_SHOV("1234 567 89")
' Returns -> '1234 567 89'
'
' TrimExtended_SHOV(" 1234 567 89 ")
' Returns -> '1234 567 89'
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2024-02-07 Initial Release
'---------------------------------------------------------------------------------------
Public Function TrimExtended_SHOV(ByVal sInput As Variant) As String
On Error GoTo Error_Handler
If Not IsNull(sInput) Then
With oRegEx
.Pattern = "^\s+|\s$|\s+(?=\s)" 'Remove multi-space and leading/trailing spaces
.Global = True
.IgnoreCase = True
.MultiLine = True
End With
TrimExtended_SHOV = oRegEx.Replace(sInput, "")
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: TrimExtended_SHOV" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
As you can see, SHOV simplifies our coding slightly, once the initial SHOV setup is done, but significantly improves performance!!!
Anyways, this was more out of personal interest, but still thought I’d share the example to demonstrate the power of RegEx and help others trying to master VBA RegEx coding.