visual basic excel replace spaces with underscores code example

Example: vba replace all spaces with underscores

' VBA ONLY 

'===============================================================================
'>> ReplaceSpacesWithUnderscores()
'===============================================================================
' Replaces all the selected cells' space characters with underscores
'===============================================================================
Sub ReplaceSpacesWithUnderscores()

    Dim sFunct As String: sFunct = "ReplaceSpacesWithUnderscores"

    Dim bDebugging As Boolean: bDebugging = True
    If (bDebugging = True) Then

        Debug.Print Format(DateTime.Now, "hh:mm:ss") _
            & " INFO " & sFunct & "| " _
            & "Running.."
        
    End If
        
    '*********************************
    ' VALIDATIONS and declarations
    '*********************************
    '(DECLARATIONS)
    Dim wbInit As Workbook: Set wbInit = ActiveWorkbook
    Dim wsInit As Worksheet: Set wsInit = ActiveSheet
    
    Dim sErrMsg As String
    
    Dim rCellsToEdit As Range
    Dim cell As Range
    
    On Error GoTo ErrHandling  
    
    '(SETTINGS)
    Set rCellsToEdit = Selection
    
    '(VALIDATIONS)
    'A) None... yet
    
    '---------------------------------
    '               WORK
    '---------------------------------
    '1) Replace each cell's " " with "_"
    
    'Z) Reactivate the initial workbook/worksheet
    
    '--(1)
    For Each cell In rCellsToEdit
    
        cell.Value = Replace(cell.Value, " ", "_")
    
    Next
    
    '--(Z)
    wbInit.Activate
    wsInit.Activate
    
    '-----------v-----------DEBUG INFO-----------v-----------
    
    If (bDebugging = True) Then

        Debug.Print Format(DateTime.Now, "hh:mm:ss") _
        & " INFO " & sFunct & "| " _
        & "Complete [if not debugging, delete this print]" 
        
    End If
            
    Exit Sub
            
ErrHandling:   
    
        Debug.Print Format(DateTime.Now, "hh:mm:ss") _
            & " INFO " & sFunct & "| " _
            & " -> Failed" 
        
        MsgBox _
            Title:="Errors in the function: " & sFunct _
            , Prompt:=Err.Description _
            & vbNewLine & sErrMsg _
            , Buttons:=vbCritical 
        
End Sub