excel - 宏调用将针对工作簿中所有打开的工作表运行的函数

Question

'我正在尝试运行一个宏，它将针对每个工作簿中的每个单元格运行一个自定义公共(public)函数(模块标题是 ClearHTML 或 StripHTML()。我通过一个名为 Remove_HTML 或 ClearHTMLShort() 的模块调用它。现在它只是指向一个事件工作表并专注于 D 列。我的目标是运行一个宏并让它遍历工作簿中每个工作表的所有填充单元格。抱歉，我对此有点陌生。

最好使用自定义函数，然后使用宏来调用单个列的函数。''

Public Function StripHTML(cell As Range) As String
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
Dim sInput As String
Dim sOut As String
sInput = cell.Text

    sInput = Replace(sInput, "\x0D\x0A", Chr(10))
    sInput = Replace(sInput, "\x00", Chr(10))

    sInput = Replace(sInput, "</P>", Chr(10) & Chr(10))
    sInput = Replace(sInput, "<BR>", Chr(10))
    sInput = Replace(sInput, "<li>", "-")

    sInput = Replace(sInput, "&#39;", "'")
    sInput = Replace(sInput, "&ndash;", "–")
    sInput = Replace(sInput, "&mdash;", "—")

    sInput = Replace(sInput, "", "`")

With RegEx
   .Global = True
   .IgnoreCase = True
   .MultiLine = True
   .Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.

End With
    sOut = RegEx.Replace(sInput, "")
    StripHTML = sOut
    Set RegEx = Nothing

End Function

调用函数的代码:

Sub ClearHTMLshort() 
Dim ws As Worksheet 
For Each ws In ThisWorkbook.Worksheets 
  Dim c As Range 
   For Each c In ActiveSheet.UsedRange.Columns("D").Cells 
     c.Value = StripHTML(c) 
   Next c 
Next ws 
End Sub

Answer 1

不妨这样回答:

你做的一切都是正确的，然后用一个错误的陈述把它搞砸了。当您使用 For Each Foo in Bar 循环某些内容时, Foo将是您要使用的对象。

改变:
ActiveSheet.UsedRange.Columns("D").Cells
至:
ws.UsedRange.Columns("D").Cells