修剪工作簿中的所有单元格 (VBA)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21015840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trim all cells within a workbook(VBA)
提问by Studento919
I have attempted to add functionality to an excel add-in ave been developing which trims the leading spaces at the end of used cells, and maybe even parse the text, The reason I need to do this is simply to have it turn into a hyperlink which I have already working but that parts fine.
我试图向一直在开发的 excel 加载项添加功能，该加载项在使用的单元格末尾修剪前导空格，甚至可能解析文本，我需要这样做的原因只是为了让它变成超链接我已经在工作了，但那部分很好。
This is what I have attempted so far, I have it trimming the active.worksheetam on which is fine but I can't figure out how to:
这是我到目前为止所尝试的，我已经修剪active.worksheet好了，但我不知道如何：
- Trim Every cell being used across the whole workbook.
- And also parse the text if possible
- 修剪整个工作簿中使用的每个单元格。
- 如果可能，还要解析文本
This is my attempt at Trimming the entire workbook, Its something simple I just know it, I just cant figure it out:
这是我修剪整个工作簿的尝试，它很简单，我只知道它，我只是想不通：
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
Any advice would be welcome am fairly new to this Language so sorry if I sound like a complete Newb!
欢迎任何建议，我对这种语言相当陌生，如果我听起来像一个完整的新手，那么抱歉！
TL;DR Trims cells only worksheet am on, needs to run across whole workbook I cant figure out how to iterate it across the whole thing.
TL; DR Trims 单元格只在工作表上，需要运行整个工作簿我不知道如何在整个事情中迭代它。
EDIT: Is that also a quicker way of trimming these cells, the spreadsheets that are created for whom am designing this are massive and takes a while to trim the cells at times
编辑：这也是修剪这些单元格的更快方法吗，为谁设计的电子表格很大，有时需要一段时间来修剪单元格
回答by Siddharth Rout
Untested
未经测试
Is this what you are trying?
这是你正在尝试的吗？
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
回答by MarkHone
I agree with Siddarth:
我同意悉达斯：
For Each cell In ActiveSheet.UsedRange
Should be:
应该：
For Each cell In wsh.UsedRange
I would have thought you should be able to remove with 'With wsh.UsedRange' statement around the loop as well.
我原以为你也应该能够在循环中使用 'With wsh.UsedRange' 语句来删除。
As you are passing in a WorkBook reference, perhaps you should consider changin your outer For loop from:
当您传入 WorkBook 引用时，也许您应该考虑从以下位置更改外部 For 循环：
For Each wsh In Worksheets
to:
到：
For Each wsh In Wb.Worksheets
