26、Excel VBA：查看隐藏工作表与多表多工作簿搜索

电竞养老选手

于 2025-11-25 11:54:29 发布

阅读量7

点赞数

CC 4.0 BY-SA版权

分类专栏： Excel VBA实战精讲文章标签： Excel VBA 隐藏工作表查看隐藏工作表

本文链接：https://blog.youkuaiyun.com/tensor9flow/article/details/155339257

Excel VBA实战精讲专栏收录该内容

29 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

Excel VBA：查看隐藏工作表与多表多工作簿搜索

1. 无需密码查看隐藏工作表

在Excel中，若对工作表设置密码隐藏，通常认为他人无法查看。但实际上，利用VBA语言可绕过这一限制。

1.1 创建隐藏工作表

操作步骤如下：
1. 点击菜单中的“Home”项。
2. 在功能区的“Cells”控件中点击“Format”（位于功能区右侧）。
3. 在弹出菜单的“Visibility”选项卡中选择“Hide & Unhide”。
4. 在进一步弹出的窗口中选择隐藏当前工作表。

1.2 创建用户界面（UserForm）

需插入一个新的UserForm，操作如下：在VBE菜单中选择“Insert | UserForm”。该UserForm应包含：
- 两个列表框：一个显示所有隐藏工作表，另一个显示所有可见工作表（作为目标工作表）。
- 两个标签控件：用于显示列表框标题。
- 两个命令按钮：分别用于“OK”和“Cancel”操作。

可通过点击表单并在“Properties”窗口中更改“Caption”属性来修改表单标题。

1.3 编写UserForm激活事件代码

Private Sub UserForm_Activate()
    ListBox1.Clear
    ListBox2.Clear
    For Each Worksheet In ActiveWorkbook.Worksheets
        If Worksheet.Visible = False Then
            ListBox1.AddItem Worksheet.Name
        End If
    Next
    For Each Worksheet In ActiveWorkbook.Worksheets
        If Worksheet.Visible = True Then
            ListBox2.AddItem Worksheet.Name
        End If
    Next
End Sub

此代码先清空两个列表框，然后遍历活动工作簿中的所有工作表，将隐藏工作表名称添加到第一个列表框，将可见工作表名称添加到第二个列表框。

1.4 编写命令按钮代码

在模块的声明部分插入全局变量：

Global canc as Integer

“OK”和“Cancel”按钮的点击事件代码如下：

Private Sub CommandButton1_Click()
    'code for OK button
    UserForm1.Hide
    canc = 0
End Sub

Private Sub CommandButton2_Click()
    'code for Cancel button
    UserForm1.Hide
    canc = 1
End Sub

点击“OK”按钮时， canc 设为0；点击“Cancel”按钮时， canc 设为1。

1.5 编写主程序代码

Sub hidden_sheets()
    UserForm1.Show
    If canc = 1 Then Exit Sub
    s1 = UserForm1.ListBox1.Text
    s2 = UserForm1.ListBox2.Text
    If s1 = "" Or s2 = "" Then Exit Sub
    Range(s1 & "!a1.xfd1048576").Copy
    Range(s2 & "!a1").PasteSpecial
    Range(s2 & "!a1").Select
End Sub

该代码先显示UserForm，用户选择隐藏工作表和目标工作表后，点击“OK”，隐藏工作表的内容将复制到目标工作表。

1.6 测试示例

在工作簿中创建一个工作表并输入数据，按上述步骤隐藏该工作表。
为整个工作簿设置密码保护：点击菜单栏中的“Review”项，在功能区的“Changes”控件中点击“Protect Workbook”图标，在弹出窗口中选择“Protect Structure and Windows”，勾选相关选项并设置密码。
尝试正常显示隐藏工作表，会发现无法操作。
运行代码，在新的UserForm中选择隐藏工作表和目标工作表，点击“OK”，隐藏工作表内容将显示在目标工作表上。

以下是该过程的流程图：

graph TD;
    A[创建隐藏工作表] --> B[创建UserForm];
    B --> C[编写UserForm激活事件代码];
    C --> D[编写命令按钮代码];
    D --> E[编写主程序代码];
    E --> F[测试示例];

2. 搜索多个工作表和工作簿

Excel自带的搜索功能只能搜索当前工作表，若要搜索多个工作表和工作簿，可通过VBA代码实现。

2.1 创建用户界面（UserForm）

该UserForm应类似于Excel现有的搜索表单，包含：
- 四个命令按钮：分别为“Find Next”、“Close”、“Replace”和“Replace All”。
- 两个文本框：分别用于输入“Find what”和“Replace with”内容。
- 两个组合框：用于选择搜索类型。
- 两个复选框：用于设置“Match Case”和“Find entire cells”。

可通过点击标题栏高亮显示表单，在“Properties”窗口中编辑“Caption”属性来修改表单标题。在模块的声明部分设置全局变量：

Global canc As Integer

2.2 编写UserForm模块代码

Private Sub CommandButton1_Click()
    'code for Find Next button
    FindDialog.Hide
    canc = 0
End Sub

Private Sub CommandButton2_Click()
    'code for Close button
    FindDialog.Hide
    canc = 1
End Sub

Private Sub CommandButton3_Click()
    'code for Replace button
    FindDialog.Hide
    canc = 2
End Sub

Private Sub CommandButton3_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ComboBox2.Visible = False
    Label3.Visible = False
End Sub

Private Sub CommandButton4_Click()
    'code for Replace All button
    FindDialog.Hide
    canc = 4
End Sub

Private Sub CommandButton4_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ComboBox2.Visible = False
    Label3.Visible = False
End Sub

Private Sub TextBox2_MouseMove(ByVal Button As Integer, ByVal Shift _
    As Integer, ByVal X As Single, ByVal Y As Single)
    ComboBox2.Visible = False
    Label3.Visible = False
End Sub

Private Sub UserForm_Activate()
    ComboBox1.Clear
    ComboBox1.AddItem "ByRows"
    ComboBox1.AddItem "ByColumns"
    ComboBox2.Clear
    ComboBox2.AddItem "Formulas"
    ComboBox2.AddItem "Values"
    ComboBox2.AddItem "Comments"
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift _
    As Integer, ByVal X As Single, ByVal Y As Single)
    ComboBox2.Visible = True
    Label3.Visible = True
End Sub

Private Sub UserForm_Terminate()
    canc = 1
End Sub

各按钮点击事件及鼠标移动事件的功能如下表所示：
| 事件 | 功能 |
| ---- | ---- |
| CommandButton1_Click | 点击“Find Next”，隐藏表单， canc 设为0 |
| CommandButton2_Click | 点击“Close”，隐藏表单， canc 设为1 |
| CommandButton3_Click | 点击“Replace”，隐藏表单， canc 设为2 |
| CommandButton3_MouseMove | 鼠标移动到“Replace”按钮上，隐藏相关控件 |
| CommandButton4_Click | 点击“Replace All”，隐藏表单， canc 设为4 |
| CommandButton4_MouseMove | 鼠标移动到“Replace All”按钮上，隐藏相关控件 |
| TextBox2_MouseMove | 鼠标在文本框中移动，隐藏相关控件 |
| UserForm_Activate | 表单激活时，清空并填充组合框列表 |
| UserForm_MouseMove | 鼠标在表单上移动，显示相关控件 |
| UserForm_Terminate | 表单关闭时， canc 设为1 |

2.3 编写主程序代码

Sub findsheet()
    flag = 0
    sflag = 1
    temp = ""
    Dim a As Range, s As Worksheet, w As Workbook
    FindDialog.TextBox1.Text = ActiveCell.Value
    Set a = ActiveCell
    For Each w In Workbooks
        Set a = w.Sheets(1).Range("xfd1048576")
        For n = 1 To w.Worksheets.Count
            loopa:
            If sflag = 1 Then FindDialog.Show
            sflag = 0
            If canc = 1 Then FindDialog.Hide: canc = 0: Exit Sub
            On Error Resume Next
            sstr = FindDialog.TextBox1.Text 'Search string
            rep = FindDialog.TextBox2.Text 'Replacement string
            If canc = 2 Then 'single replacement
                a.Replace sstr, rep
                sflag = 1
                GoTo loopa 'go back to form after replacement
            End If
            On Error Resume Next
            'set up li variable for 'look in'
            If FindDialog.ComboBox2.Text = "Formulas" Then li = -4123
            If FindDialog.ComboBox2.Text = "Values" Then li = -4163
            If FindDialog.ComboBox2.Text = "Coments" Then li = -4144
            'set up so variable for search order
            If FindDialog.ComboBox1.Text = "ByRows" Then so = 1
            If FindDialog.ComboBox1.Text = "ByColumns" Then so = 2
            mc = FindDialog.CheckBox1.Value 'Match Case flag
            la = FindDialog.CheckBox2.Value 'Look At flag
            If la = True Then lat = 1 Else lat = 2 'set Look At T flag _
            (lat)
            If canc = 4 Then
                p = w.Sheets(n).Range("a1", " xfd1048576"). _
                Replace(sstr, rep, lat, so,  mc) 'search string, replace string, _
                look at, search order, match case
            End If
            If a.Address = "" Then Set a = Sheets(n).Range("xfd1048576")
            Set a = w.Sheets(n).Range("a1", " xfd1048576"). _
            Find(sstr, a, li, lat, so, ,mc) 'search string, replace string, _
            look at, search order, match case
            If a.Address = "" Then sflag = 0 Else sflag = 1
            If InStr(temp, w.Name & Sheets(n).Name & a.Address) Or _
            a.Address = ""  Then sflag = 0: GoTo bypass 'search is
            'complete – break out of loop
            w.Activate
            Sheets(n).Activate
            a.Activate
            temp = temp & " " & w.Name & Sheets(n).Name & a.Address
            GoTo loopa
            bypass:
            temp = ""
        Next n
    Next w
    MsgBox "No further occurrences of " & sstr, vbInformation
    Exit Sub
End Sub

该代码先初始化变量，将“Find what”文本框设为活动单元格的值，然后遍历所有工作簿和工作表进行搜索。若找到匹配项，将光标移动到该位置；若未找到，显示提示信息。

以下是该搜索过程的流程图：

graph TD;
    A[创建UserForm] --> B[编写UserForm模块代码];
    B --> C[编写主程序代码];
    C --> D[运行搜索程序];
    D --> E{是否找到匹配项};
    E -- 是 --> F[移动光标到匹配项];
    E -- 否 --> G[显示提示信息];

通过上述代码和操作步骤，可实现无需密码查看隐藏工作表以及搜索多个工作表和工作簿的功能。

Excel VBA：查看隐藏工作表与多表多工作簿搜索（续）

2. 搜索多个工作表和工作簿（续）

2.4 代码详细解析

下面对 findsheet 主程序代码进行详细解析，了解其具体的执行逻辑和功能实现。

变量初始化 ：

flag = 0
sflag = 1
temp = ""
Dim a As Range, s As Worksheet, w As Workbook
FindDialog.TextBox1.Text = ActiveCell.Value
Set a = ActiveCell

- `flag` 和 `sflag` 作为标志变量，用于控制程序的执行流程。
- `temp` 用于存储搜索到的结果信息，避免重复搜索。
- 定义了 `a` 作为 `Range` 类型变量，`s` 作为 `Worksheet` 类型变量，`w` 作为 `Workbook` 类型变量。
- 将 `FindDialog` 文本框中的 “Find what” 设置为当前活动单元格的值，并将 `a` 指向当前活动单元格。

遍历工作簿和工作表 ：

For Each w In Workbooks
    Set a = w.Sheets(1).Range("xfd1048576")
    For n = 1 To w.Worksheets.Count

- 使用 `For Each` 循环遍历所有打开的工作簿。
- 将 `a` 指向每个工作簿中第一个工作表的最后一个单元格。
- 再使用 `For` 循环遍历当前工作簿中的所有工作表。

显示搜索表单并处理用户操作 ：

loopa:
If sflag = 1 Then FindDialog.Show
sflag = 0
If canc = 1 Then FindDialog.Hide: canc = 0: Exit Sub
On Error Resume Next
sstr = FindDialog.TextBox1.Text 'Search string
rep = FindDialog.TextBox2.Text 'Replacement string

- `loopa` 是一个标签，用于程序的跳转。
- 当 `sflag` 为 1 时，显示搜索表单。
- 将 `sflag` 置为 0，避免重复显示表单。
- 如果用户点击 “Close” 按钮（`canc = 1`），隐藏表单，重置 `canc` 并退出子程序。
- 获取用户输入的搜索字符串和替换字符串。

处理替换操作 ：

If canc = 2 Then 'single replacement
    a.Replace sstr, rep
    sflag = 1
    GoTo loopa 'go back to form after replacement
End If

- 当用户点击 “Replace” 按钮（`canc = 2`），对当前找到的单元格进行替换操作。
- 将 `sflag` 置为 1，跳转回 `loopa` 标签处，重新显示表单。

设置搜索参数 ：

On Error Resume Next
'set up li variable for 'look in'
If FindDialog.ComboBox2.Text = "Formulas" Then li = -4123
If FindDialog.ComboBox2.Text = "Values" Then li = -4163
If FindDialog.ComboBox2.Text = "Coments" Then li = -4144
'set up so variable for search order
If FindDialog.ComboBox1.Text = "ByRows" Then so = 1
If FindDialog.ComboBox1.Text = "ByColumns" Then so = 2
mc = FindDialog.CheckBox1.Value 'Match Case flag
la = FindDialog.CheckBox2.Value 'Look At flag
If la = True Then lat = 1 Else lat = 2 'set Look At T flag _
(lat)

- 根据用户在表单中选择的搜索类型和搜索顺序，设置相应的参数。
- 获取用户设置的 “Match Case” 和 “Look At” 标志。

处理 “Replace All” 操作 ：

If canc = 4 Then
    p = w.Sheets(n).Range("a1", " xfd1048576"). _
    Replace(sstr, rep, lat, so,  mc) 'search string, replace string, _
    look at, search order, match case
End If

- 当用户点击 “Replace All” 按钮（`canc = 4`），对当前工作表中的所有匹配项进行替换操作。

查找匹配项 ：

If a.Address = "" Then Set a = Sheets(n).Range("xfd1048576")
Set a = w.Sheets(n).Range("a1", " xfd1048576"). _
Find(sstr, a, li, lat, so, ,mc) 'search string, replace string, _
look at, search order, match case
If a.Address = "" Then sflag = 0 Else sflag = 1

- 如果当前 `a` 的地址为空，将其指向当前工作表的最后一个单元格。
- 使用 `Find` 方法在当前工作表中查找下一个匹配项。
- 根据查找结果，设置 `sflag` 的值。

避免重复搜索和处理结果 ：

If InStr(temp, w.Name & Sheets(n).Name & a.Address) Or _
a.Address = ""  Then sflag = 0: GoTo bypass 'search is
'complete – break out of loop
w.Activate
Sheets(n).Activate
a.Activate
temp = temp & " " & w.Name & Sheets(n).Name & a.Address
GoTo loopa
bypass:
temp = ""

- 如果当前找到的结果已经在 `temp` 中，或者 `a` 的地址为空，说明搜索完成，跳转到 `bypass` 标签处。
- 激活找到匹配项的工作簿、工作表和单元格。
- 将当前找到的结果信息添加到 `temp` 中。
- 跳转回 `loopa` 标签处，继续搜索。
- 在 `bypass` 标签处，清空 `temp`，继续处理下一个工作表。

搜索结束提示 ：

MsgBox "No further occurrences of " & sstr, vbInformation
Exit Sub

- 当所有工作表和工作簿都搜索完毕，没有找到更多匹配项时，显示提示信息并退出子程序。

2.5 运行搜索程序

运行 findsheet 子程序，即可启动搜索过程。具体操作步骤如下：
1. 打开包含代码的工作簿，确保所有需要搜索的工作簿都已打开。
2. 定位到代码窗口，将光标放在 findsheet 子程序的任意位置。
3. 点击 VBE 工具栏中的运行按钮，或者使用快捷键 F5 运行程序。
4. 在弹出的搜索表单中，输入搜索字符串和替换字符串（可选），选择搜索类型、搜索顺序、匹配选项等。
5. 根据需要点击相应的命令按钮，如 “Find Next”、“Replace”、“Replace All” 或 “Close”。
6. 程序将根据用户的操作进行搜索和替换，并在找到匹配项时将光标移动到相应位置。

以下是搜索过程中各步骤的详细列表：
| 步骤 | 操作内容 |
| ---- | ---- |
| 1 | 初始化变量和设置搜索表单初始值 |
| 2 | 遍历所有工作簿和工作表 |
| 3 | 显示搜索表单，获取用户输入 |
| 4 | 根据用户操作进行搜索和替换 |
| 5 | 处理搜索结果，避免重复搜索 |
| 6 | 显示搜索结束提示信息 |

3. 总结

通过使用 Excel VBA 代码，我们可以实现两个非常实用的功能：无需密码查看隐藏工作表和搜索多个工作表及工作簿。

查看隐藏工作表 ：通过创建用户界面和编写相应的 VBA 代码，我们可以绕过工作表的密码保护，将隐藏工作表的内容复制到可见工作表上进行查看。
搜索多个工作表和工作簿 ：利用 VBA 代码创建自定义的搜索表单，实现对多个工作表和工作簿的搜索和替换功能，弥补了 Excel 自带搜索功能的不足。

在实际应用中，这些功能可以帮助我们更高效地处理 Excel 数据，提高工作效率。同时，通过对代码的详细解析，我们也可以深入了解 VBA 编程的基本原理和技巧，为进一步开发更复杂的 Excel 应用程序打下基础。

希望以上内容对你在 Excel 数据处理和 VBA 编程方面有所帮助。你可以根据实际需求对代码进行修改和扩展，实现更多个性化的功能。

以下是整个操作流程的综合流程图：

graph LR;
    A[查看隐藏工作表或搜索多表多簿] --> B{选择功能};
    B -- 查看隐藏工作表 --> C[创建隐藏工作表];
    C --> D[创建UserForm];
    D --> E[编写UserForm激活事件代码];
    E --> F[编写命令按钮代码];
    F --> G[编写主程序代码];
    G --> H[测试示例];
    B -- 搜索多表多簿 --> I[创建UserForm];
    I --> J[编写UserForm模块代码];
    J --> K[编写主程序代码];
    K --> L[运行搜索程序];
    L --> M{是否继续搜索};
    M -- 是 --> L;
    M -- 否 --> N[结束搜索];