How do I execute a sub that doesn't display as a macro?

Question

I found an excellent piece of code on this site that I'd like to add to my workbook:

The code there is:

Sub DoTrim(Wb As Workbook)
    Dim aCell As Range
    Dim wsh As Worksheet

    '~~> If you are using it in an Add-In, it is advisable 
    '~~> to keep the user posted :)
    Application.StatusBar = "Processing Worksheets... Please do not disturb..."
    DoEvents

    Application.ScreenUpdating = False

    For Each wsh In Wb.Worksheets
        With wsh
            Application.StatusBar = "Processing Worksheet " & _
                                    .Name & ". Please do not disturb..."
            DoEvents

            For Each aCell In .UsedRange
                If Not aCell.Value = "" And aCell.HasFormula = False Then
                    With aCell
                        .Value = Replace(.Value, Chr(160), "")
                        .Value = Application.WorksheetFunction.Clean(.Value)
                        .Value = Trim(.Value)
                    End With
                End If
            Next aCell
        End With
    Next wsh

    Application.ScreenUpdating = True
    Application.StatusBar = "Done"
End Sub

I've tried copying and pasting this into an Excel module, but can't figure out how to execute it. I'm really new at all this, so I apologize in advance if this is a stupid question. I've searched on why the Sub won't show up as a macro, and the best possible answer I've found is that it is because it contains variables?

I realize the original poster was trying to modify an Add-In; I'm just hoping there's a way to invoke this on a workbook.

Thanks!

Your sub has an argument `Wb` so you must call it from another procedure, e.g. `DoTrim(Wb1)` where wb1 is a workbook variable declared and assigned in the calling procedure. — SJR, Nov 08 '17 at 12:00

Socii · Answer 1 · 2017-11-08T16:09:51.920

The subroutine is expecting a Workbook as a parameter Sub DoTrim(Wb As Workbook), so you won't be able to run it from the macros window

If the Excel Module is in the same Workbook you want to trim, then you can remove the parameter and use the ThisWorkbook object instead to loop through the Worksheets in the Workbook, as below:

Sub DoTrim()
    Dim aCell As Range
    Dim wsh As Worksheet

    '~~> If you are using it in an Add-In, it is advisable 
    '~~> to keep the user posted :)
    Application.StatusBar = "Processing Worksheets... Please do not disturb..."
    DoEvents

    Application.ScreenUpdating = False

    For Each wsh In ThisWorkbook.Worksheets
        With wsh
            Application.StatusBar = "Processing Worksheet " & _
                                    .Name & ". Please do not disturb..."
            DoEvents

            For Each aCell In .UsedRange
                If Not aCell.Value = "" And aCell.HasFormula = False Then
                    With aCell
                        .Value = Replace(.Value, Chr(160), "")
                        .Value = Application.WorksheetFunction.Clean(.Value)
                        .Value = Trim(.Value)
                    End With
                End If
            Next aCell
        End With
    Next wsh

    Application.ScreenUpdating = True
    Application.StatusBar = "Done"
End Sub

I'd add that an alternative to `ThisWorkbook` could be `ActiveWorkbook` which would execute the code on the workbook that's selected (i.e. on top in the Excel Window) rather than the workbook containing the code. — Darren Bartrup-Cook, Nov 08 '17 at 17:08
Instead of removing the variable, you could also make it optional and default it to `ThisWorkbook` or `ActiveWorkbook` so that you can still call it specifically if you want. — NickSlash, Nov 08 '17 at 18:50

How do I execute a sub that doesn't display as a macro?

1 Answers1