Replace Space with Underscore Loop with VBA

[jeran042]

Good morning all,

I have a quick question, not actually a problem. I have a very simple piece of code that will replace all the spaces in a selection with underscores. I mainly use it to clean up header rows. My question is, based on the code below, is there a way that this can be done better? And is the error handling sufficient for what I am trying to accomplish?

Very much appreciated,

Sub Convert_Space_To_Underscore()

'Error handling
    On Error GoTo Error_Handler


    Dim sValue As String
    
    For Each CELL In Selection
        sValue = Trim(CELL.Value)
        sValue = Replace(sValue, " ", "_")
        CELL.Value = sValue
    Next CELL




Error_Handler:
            MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
            Err.Clear
            Exit Sub
    
End Sub

 

[sheetspread]

ctrl-h does this too, and can be recorded in a macro.

 

[mikerickson]

The only thing that see is the lack of Exit Sub immediately before the Error_Handler label.
As it is, you will always get a message, even if there isn't an error.

 

[Fluff]

Another way of doing it would be

Sub Convert_Space_To_Underscore()
With Selection
   .Value = Evaluate(Replace("if({1},substitute(trim(@),"" "",""_""),@)", "@", .Address))
End With
End Sub