(VBA)适用于所有人，但一项具体规定除外

Question

大家好，我用VBA模块在excel上做了一个按钮，代码在活动表上工作，但是我想要的是应用到更多的工作表上，而不仅仅是放在按钮所在的活动工作表上。

Sub Botón1_Haga_clic_en()
    Call Worksheet_Calculate
End Sub


'apply cells colors from single-cell formula dependencies/links
Private Sub Worksheet_Calculate()
    Dim Cel       As Range
    Dim RefCel  As Range

    On Error Resume Next

    For Each Cel In ActiveSheet.UsedRange
        If Cel.HasFormula Then
            Set RefCel = Evaluate(Mid(Cel.Formula, 2))
            Cel.Interior.Color = RefCel.Interior.Color
        End If
    Next Cel

End Sub

Answer 1

试试下面的代码：

Option Explicit

Sub Botón1_Haga_clic_en()

Dim wsName As String
Dim ws As Worksheet

wsName = ActiveSheet.Name

For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name Like wsName Then '<-- is worksheet's name doesn't equal the ActiveSheet's
        ApplyCellColors ws ' <-- call you Sub, with the worksheet object
    End If
Next ws

End Sub

'=======================================================================    
'apply cells colors from single-cell formula dependencies/links

Private Sub ApplyCellColors(ws As Worksheet)

    Dim Cel       As Range
    Dim RefCel  As Range

    On Error Resume Next

    For Each Cel In ws.UsedRange
        If Cel.HasFormula Then
            Set RefCel = Evaluate(Mid(Cel.Formula, 2))
            Cel.Interior.Color = RefCel.Interior.Color
        End If
    Next Cel

End Sub

Answer 2

您的问题可以转化为这样的问题:如何遍历所有的工作表，而忽略其中的一个？

这是一个很好的方法：

Option Explicit
Option Private Module

Public Sub TestMe()

    Dim wks As Worksheet

    For Each wks In ThisWorkbook.Worksheets
        If wks.name = "main" Then
            Debug.Print "Do nothing here, this is the active sheet's name"
        Else
            Debug.Print wks.name
        End If

    Next wks

End Sub

非常肯定，您应该能够将它安装在代码中。