在 Excel 中顺手写了一个宏

Sep 13, 2016 ·
6 分钟阅读

在工作周报中每周都要新建一个 Sheet 来填写日志，实在是一件很繁琐重复的事情。而工具，就是为此而诞生的。

首先是接收到了需求：根据日历选择日期，显示不同周的工作周报（不跳转 Sheet）。

下面来讲讲我是怎么从零开始构建这部分代码的（尽管很简单，大部分需要的资料 Google 就能找到）。

零、重新理解需求。

如果按照上述描述的需求，应该是这样的实现逻辑：

从这上面的逻辑来看，就算不跳转 Sheet，所有周的周报内容也必须存在某地方来储存，所以实际需求可以转换成这样：根据日历选择日期，跳转到相应的 Sheet，如果相应日期还没有 Sheet，从模板里新建一个，并命名为日期。

一、构建实现的逻辑

因为之前有过使用宏来处理数据的经验，大概对简单的代码能实现的功能有基本概念，用一些简单逻辑就可以完成上面的需求：日期控件选择日期 -> 表格联动 -> 快捷键激活宏 -> 检索所有工作表 -> 有对应工作表则跳转 -> 没有对应工作表则复制一份 Sheet1（模板工作表） -> 重命名工作表 -> 跳转到此工作表。

二、开始代码部分

直接编写全部 VBA 代码对于几年没写过 VB 的我来说是有难度的。所以采用了分解步骤 + 手工录制宏的方法来解决部分代码问题，其余代码问题由 Google 解决。

分解及录制的宏主要有：工作表跳转，工作表复制和检索所有工作表名称并判断。

其间还遇到一个小问题：日历控件联动的日历格式是带 / 号的，而工作表名称是不允许带这个符号的。所以在此又写了一段符号替换的代码。

后来又对逻辑进行了一个小优化，不采用快捷键而是增加一个按钮来点击跳转，原因是选择日历上的时间后，直接点快捷键是不起作用的，必须先随意点一个单元格，与其这么繁琐，干脆就改成按钮的操作（而且并不是每个人都像我一样是快捷键狂魔）。

Private Sub CommandButton1_Click()


    Range("G3") = Replace(Range("G3"), "/", ".")
    ' 日期格式转换


    Dim SheetName As String
    SheetName = Range("G3")
    ' 指定工作表名变量


    For i = 1 To Sheets.Count
        ' 设置变量在“1”到“工作表数量”之间循环
        If Sheets(i).Name = SheetName Then
            ' 判断名称为 SheetName 的工作表是否存在
            Sheets(SheetName).Select
            ' 当名称为 SheetName 的工作表存在时，跳转到该工作表
            Exit Sub
            ' 如果存在“Sheet4”工作表，则退出该过程
        End If
    Next


    Sheets("2016.9.9").Select
    Sheets("2016.9.9").Copy After:=Sheets("2016.9.9")
    ' 如果不存在则复制空白的模板工作表


    ActiveSheet.Name = SheetName
    ' 插入后会自动激活新工作表，对此表重新命名为 SheetName


End Sub

三、协作编辑

为了让周报更加方便的使用，我们将其设置成了共享文档，放到了公用的服务器上，这样部门的人员就能互不影响地同时编辑了。

四、其他优化

现周报还有一个问题是，在日历上会显示最后一次点击的日期，所以难免会让人误解当前工作表的日期，解决方法倒是有二：

  1. 让日期控件远离填写区域
  2. 填写区域新增一个当前周日期（代码自动获取）

第一点完成了，第二点有空再完成吧。

噢对了，OSX 下的 Office 没有测试过，Windows 下一切正常。

10.10 更新： 最近有空，增加了一个功能，选择的日期在 Tab 不存在时，点击跳转自动复制模板并命名为选择的日期，明年可以不一个个复制 Tab 了。但是经过一个月的试用，这个日历功能只对 Boss 和每周第一个填周报的同事比较有用，哈哈。

编辑于 Nov 19, 2024
© 2025 Bluepikachu