excel - 修剪工作簿中的所有单元格(VBA)

Question

我试图向一个正在开发的 excel 加载项添加功能，该加载项在使用的单元格末尾修剪前导空格，甚至可能解析文本，我需要这样做的原因只是让它变成一个超链接我已经在工作了，但那部分很好。

这是我到目前为止所尝试的，我已经修剪了 active.worksheet我很好，但我不知道如何:

修剪整个工作簿中使用的每个单元格。

如果可能的话还解析文本

这是我尝试修剪整个工作簿，它很简单，我只知道它，我就是想不通:

Sub DoTrim(Wb As Workbook)
Dim cell As Range
Dim str As String
Dim nAscii As Integer
Dim wsh As Worksheet

For Each wsh In Worksheets
    With wsh.UsedRange
        For Each cell In ActiveSheet.UsedRange
            str = Trim(cell)
             If Len(str) > 0 Then
                        nAscii = Asc(Left(str, 1))
                        If nAscii < 33 Or nAscii = 160 Then
                            If Len(str) > 1 Then
                              str = Right(str, Len(str) - 1)
                            Else
                                str = ""
                            End If
                        End If
                    End If
                    cell = str
        Next cell
    End With
Next wsh
End Sub

任何建议都会受到欢迎，我对这门语言相当陌生，如果我听起来像一个完整的新手，我很抱歉!

TL;DR Trims cells only worksheet am on，需要在整个工作簿上运行我不知道如何在整个事情上迭代它。

编辑:这也是修剪这些单元格的更快方法吗，为其设计的电子表格非常庞大，有时需要一段时间来修剪单元格

Answer 1

尝试这个

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