宏 VBA

Excel 工具箱

Patrice T

4.98/5 (13投票s)

2023年7月17日

CPOL

5分钟阅读

23615

751

我为了简化我的程序员生活而制作的 Excel 工具

下载源代码 - 31.5 KB

引言

我每天都在处理巨大的 Excel 电子表格，一个工作簿包含 50,000 个公式是很常见的。

多年来，我创建了函数和宏来简化我的 Excel 公式和用法。

我使用 VBA 来兼容 Excel 2003，因为我从那时开始接触 Excel 编程，距今已有 20 年了。

我的标准工作表设置

只有用户输入的单元格是解锁的，其他所有单元格都已锁定。
工作表已受保护，用户不会意外删除公式。

这确保了工作表有最低限度的安全性。为了更高级别的安全性，工作表受密码保护。

Samples.xls

“下载”文件中的 Samples.xls 文件展示了以下代码片段的示例用法。

Formula()

这个微小的函数没有任何其他目的，就是显示另一个单元格的公式。

它只是用于 Excel 截图的辅助工具。

' 20230717 Patrice_T
Function Formula(Adrs As Range) As String
    Formula = Adrs.Formula
End Function

ExcelToolBox.zip 中的 Formula.bas.

扫描宏

主要是为了检查目的，我创建了扫描工作表的宏。有两种宏，一些宏扫描整个工作表，另一些则从活动单元格开始。

扫描整个工作表

有时，我希望宏扫描整个工作表，因为某些问题需要强制修复。

这些宏使用这个骨架。

For Each Cel In ActiveSheet.UsedRange
	' Do stuf or Checks
	' Set Condition
    If Condition Then
        Cel.Activate
        MsgBox "Error at Cell " & Replace(ActiveCell.Address, "$", "")
        Exit Sub
    End If
Next

宏会扫描工作表中的每个单元格，检查条件，并在遇到时停止。

从活动单元格开始

有时，我希望宏从活动单元格开始扫描，因为某些条件并非总是需要修复的问题。

这些宏使用这个骨架。

Dim RowS As Long
Dim RO, CO
RowS = ActiveSheet.UsedRange.RowS.Count
RO = ActiveCell.Row
CO = ActiveCell.Column
Rupt = ActiveCell.Value

For Scan = RO To RowS
   	' Do stuf or Checks
   	' Set Condition
    If Condition Then
        ActiveSheet.Cells(Scan, CO).Activate
        Exit Sub
    End If
Next

此代码从活动单元格开始在列中扫描某个条件。

宏

宏是可以通过菜单或功能区（GUI）运行的程序。

在公式中搜索 #REF! 错误

任何包含 #REF! 的公式都存在错误，因为最初引用的单元格已被删除。这有时会在删除工作表的部分内容时发生。任何此类错误都必须纠正，因此需要对整个工作表进行扫描。

Excel 提供了这样的工具，但必须选择有问题的单元格才能查看该单元格中是否存在此类问题。当需要检查 50,000 个单元格时，这确实不方便。否则，需要使用 Excel 的错误定位功能，但在我的情况下，我添加了针对我正在处理的工作表的其他检查。

' 20220201 Patrice T
Sub ScanRef()
    ' TP recherche de #ref! dans la feuille
    For Each Cel In ActiveSheet.UsedRange
        If InStr(Cel.Formula, "#REF!") > 0 Then
            Cel.Activate
            MsgBox "Erreur Cellule " & Replace(ActiveCell.Address, "$", "") _
                    & vbLf & " Erreur #REF! dans formule (formule cassée)"
            Exit Sub
        End If
    Next

End Sub

ExcelToolBox.zip 中的 ScanRef.bas.

搜索孤立公式

孤立公式是指引用空单元格的公式。这可能是一个问题，但不总是。宏从活动单元格开始扫描。

此截图显示了 #REF! 错误，并且该公式也是孤立的，因为它引用了 C2，而 C2 是空的。

Excel 可以突出显示此类公式，但我的宏也可以允许引用空单元格，如果它们在特定范围内。例如：在银行账户的交易列表中，有存款和取款列，持续的余额将引用空单元格，这是正常的。

正则表达式

在这个正则表达式中，我只想匹配单元格引用、范围和定义名称。但由于公式很复杂，我发现更容易匹配不需要的部分以防止误报。被匹配为不需要的匹配部分不会导致误报。

不需要的：匹配字符串
需要的：匹配可选的工作表名称和范围的单元格地址
不需要的：匹配函数名称
需要的：匹配定义名称

    Dim xRegEx As Object
    Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
    '   The RegEx
    '   Match a string
    '   (""[^""]*"")
    '   match sheet name without space
    '   ([a-zA-Zé0-9_]+!)
    '   match sheet name with space
    '   ('[a-zA-Zé0-9\s_]+'!)
    '   match cell adress or range
    '   \$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?
    '   match a function name
    '   ([a-zA-Zé0-9\._]+\()
    '   match defined name
    '   ([a-zA-Zé_][a-zA-Zé0-9_]+)
    With xRegEx
        .Pattern = "(""[^""]*"")|(([a-zA-Zé0-9_]+!)|('[a-zA-Zé0-9\s_]+'!))_
        ?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?|_
        ([a-zA-Zé0-9\._]+\()|([a-zA-Zé_][a-zA-Zé0-9_]+)"
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With

ExcelToolBox.zip 中的 Orphan.bas.

定义允许孤立公式的范围

    '   List of ranges where orphans are allowed
    '    List = Array("F1:H20")
    List = Array()

ExcelToolBox.zip 中的 Orphan.bas.

代码会区分匹配项。

            If Cel.HasFormula Then
                ' c'est bien une formule
                Set Tmp = xRegEx.Execute(Cel.Formula)
                For i = 0 To Tmp.Count - 1
                    If Left(Tmp.Item(i), 1) = """" Then
                        ' ne pas traiter les chaines de caractères
                    ElseIf InStr(Tmp.Item(i), "(") <> 0 Then
                        ' nom de fonction, sauter
                    ElseIf InStr(Tmp.Item(i), "TRUE") <> 0 Then
                        ' sauter
                    ElseIf InStr(Tmp.Item(i), "FALSE") <> 0 Then
                        ' sauter
                    Else
                        ' si Cel dans ranges de list, alors sauter la vérification
                        ' Vérifier la formule
                        Set Target = Range(Tmp.Item(i).Value)
                        Verif = True
                        If ActiveSheet.Name <> Target.Worksheet.Name Then
                            ' WS différent, sauter
                        Else
                            For Each Ligne In List
                                If Not Application.Intersect(Range(Ligne), Target) _
                                    Is Nothing Then
                                    Verif = False
                                End If
                            Next
                        End If
                        If Verif Then
                            If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
                                If Target.Formula = "" And Target.Locked Then
                                    Cel.Activate
                                    ' MsgBox "Cellule " & Replace(Cel.Address, _
                                    ' "$", "") & "fait référence à une cellule vide" & _
                                    ' vbLf & Replace(Ref.Address, "$", ""), vbYesNoCancel

                                    Exit Sub
                                End If
                            End If
                        End If
                    End If
                Next
            End If

ExcelToolBox.zip 中的 Orphan.bas.

自动化计划

由于工作表非常大，我使用计划（Plan）来隐藏/折叠部分内容，以方便导航。宏负责创建计划。

预留一列用于指示哪些行是标题或详细信息（参见 Samples.xls）。