CODEWITHSUNDEEP
vbaexcel
ChasVA
ChasVA

Reputation: 11

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

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

Trim all cells within a workbook(VBA)

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!

Upvotes: 0

Views: 81

Answers (1)

Socii
Socii

Reputation: 545

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

Upvotes: 3

Related Questions