【2.使用VBA自动填充Excel工作表】

前言

上一篇文章我使用了Excel中的Index和Match函数实现可以根据之前打过的数据去自动填充内容。在对账的时候可以帮助我们节省很多时间。但是，这样不够直接、更不够快。
做过对账单和送货单的人都知道，送货单在出货的时候要打，可能一天一个公司打个一两次。要是我在打送货单的时候，对账单也能自动根据送货单的内容自动填充就好啦。

如此细致的功能，我目前只想到了使用Python和VBA。

如果要实现日常的使用，VBA应该就够用了。

什么是VBA

VBA是Visual Basic for Applications的缩写，它是一种事件驱动的编程语言，由微软公司开发。VBA主要用于Microsoft Office套件中的应用程序，如Excel、Word、Access、PowerPoint等，允许用户通过编写宏和自定义函数来自动化任务和扩展应用程序的功能。

VBA基于Visual Basic编程语言，它提供了一套丰富的编程工具和对象模型，使得开发者可以创建复杂的自动化脚本和自定义用户界面。使用VBA，用户可以：

自动化重复性任务：通过编写宏来减少手动操作，提高工作效率。
数据处理和分析：在Excel中进行复杂的数据操作和分析。
用户界面定制：在Word或Excel中创建自定义的对话框和表单。
与其他应用程序交互：通过VBA编写的代码可以与其他应用程序或数据库进行交互。

发明背景

VBA的发明时间可以追溯到1994年左右，当时随着Office 95的发布，VBA作为其宏语言的一部分被引入。它与传统的宏语言不同，提供了面向对象的程序设计方法，并拥有相当完整的程序设计语言。VBA易于学习掌握，可以使用宏记录器记录用户的各种操作并将其转换为VBA程序代码，从而实现工作自动化。

VBA与Visual Basic（VB）具有相似的语言结构，从语言结构上讲，VBA是VB的一个子集，它们的语法结构是一样的。VB是一种独立的开发工具，而VBA则必须依附于Office应用程序。VBA专门用于Office的各应用程序中，如Word、Excel、Access等，它与主应用程序之间的通信变得简单而高效。

VBA的编程能力介于低级语言和高级语言之间，它继承了Visual Basic的部分特性和语法，因此具有更强的编程能力和灵活性。然而，VBA的运行速度相对较慢，因为它是通过解释执行而非编译执行的。此外，VBA主要用于Office软件的宏编程和自动化处理，对于其他领域的开发可能不太适用。

如何使用Excel中的VBA

参考链接小步教程
首先，要想使用VBA，需要新建启用宏的工作簿，文件后缀名是xlsm
然后再文件-选项中-自定义功能区 选中开发工具

简单基础入门

控制台输出信息

使用

Debug.Print ""

以及立即窗口(控制台)

定义过程（功能）

Sub 函数名End Sub

定义变量

Dim 变量名 As 数据类型
Dim a As String

常用的数据类型

• Workbook 工作簿 表示整个Excel文件，包括所有的工作表
• Worksheet 工作表 表示单个工作表
• String 字符串
• Long 存储大整数，比如行号 (-2,147,483,648 到 2,147,483,647）

Set

给自己定义的变量指向一个对象

' 设置源工作表Set wsSource = ThisWorkbook.Sheets(sourceSheetName)' 打开目标工作簿Set wbTarget = Workbooks.Open("C:\Users\Administrator\Desktop\nosee\EXCEL\测试填充目标文件.xlsm")

循环

VBA循环结构包括四类结构：For To、For Each、While、Do While。

For To

退出当前循环

退出整个循环

我的需求

要有图形化界面

• 可以自己选择需要对账的工作表和送货表
• 自己可以选择要对账的日期和范围 (可能要用到正则表达式)
• 使用用户界面交互 选择送货单的位置和工作表 对账单的位置和表

开发过程

效果演示

视频如下：

文件情况

测试填充源文件

（这是代码里的名称，在实际工作中就对应送货单）

如上图，比较值得注意的点是：

• 序号那种字段名称居然占了两行，这就导致有效数据是日期行加3，不知道做这个送货单的人怎么想的🙄，当时没注意看坑死我了。
• 还有就是第九行的内容看起来横跨了很多单元格，但是其实只是在A列而已，只是没有展开
• 还有一个细节就是，对比一下可以看出，在DATE 2024/8/31的下面三行就是有用的数据，而且有用的数据那一行的C、D列都有数据，这些都是我后面用来判断数据有效性的依据

测试填充目标文件

(这个也是代码里的名称，实际中对应的是对账单)

这个没什么好说的，以后希望也能把日期附在内容前以方便分开

全部完整的代码

sheet1中的代码，对应A公司工作表

    '全局变量 用来接收文件信息Public targetSheetName As StringPublic sourceSheetName As StringPublic targetFilePath As StringPublic dateToCheck As StringSub UserForm_Initialize()UserForm1.Show '打开用户界面
End SubSub CopyDataBasedOnDate() '定义函数  用户界面Dim wsSource As Worksheet '定义变量 Worksheet 是Excel中工作表的对象类型，表示单个工作表。源工作簿不用设置Dim wbTarget As Workbook  'Workbook 是Excel中工作簿的对象类型，表示整个Excel文件，包括所有的工作表。Dim lastRow As LongDim targetRowA As LongDim targetRowC As LongDim targetRow As Long 'Long 是VBA中的一种数据类型，用于存储较大的整数（-2,147,483,648 到 2,147,483,647）' 设置源工作表Set wsSource = ThisWorkbook.Sheets(sourceSheetName)' 打开目标工作簿Set wbTarget = Workbooks.Open(targetFilePath)' 找到目标工作表Dim wssTarget As Worksheet' ############设置异常？On Error Resume Next '这行代码是一个错误处理语句，它告诉VBA在遇到错误时不要显示错误消息，而是继续执行下一行代码。这是错误处理的开始。Set wssTarget = wbTarget.Sheets(targetSheetName) '通过 wsTarget 工作簿对象来访问名为 targetSheetName 的工作表，If wssTarget Is Nothing Then ' Nothing 表示一个变量没有引用任何对象。MsgBox "目标工作表不存在"Exit Sub  '这行代码会立即退出当前正在执行的子程序（Sub）。如果没有找到工作表，程序将不会继续执行后面的代码。End IfOn Error GoTo 0 '这行代码用于关闭之前启用的错误处理。在遇到错误时停止执行并显示错误消息。这行代码通常放在错误处理代码的最后，以确保错误处理的范围仅限于特定的代码块。' ############获取源工作表的最后一行  这里可能要调lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).RowtargetRowA = wssTarget.Cells(wssTarget.Rows.Count, "A").End(xlUp).RowtargetRowC = wssTarget.Cells(wssTarget.Rows.Count, "C").End(xlUp).RowtargetRow = Application.Max(targetRowA, targetRowC) + 2    '因为最后一行也是两行合并' ##############遍历源工作表中的所有行  应该要换个判断条件或者方式 根据日期，直到下一个日期Dim i As LongFor i = 1 To lastRow '遍历循环If wsSource.Cells(i, "A").Value = dateToCheck Then '如果第i行是要对账的日期For j = i + 3 To lastRow '日期内循环If j - i > 13 Then '3是必要的内容 因为有客套话 假如内容十行的话 就够了'最好的办法还是判断日期内循环有没有到下一个日期 到了就直接跳 结束了i = j - 1Exit ForEnd IfIf wsSource.Cells(j, "A").Value = dateToCheck Then '如果已经碰到下一个日期 比如同一天的 还有可能不是同一天的i = j - 1 '节省遍历循环的时间 跳出循环Exit ForEnd IfIf Not IsEmpty(wsSource.Cells(j, "C").Value) And Not IsEmpty(wsSource.Cells(j, "D").Value) Then'如果没有到下一个日期并且数据有效  c列d列不都为空就可以了wsSource.Rows(j).Copy Destination:=wssTarget.Rows(targetRow) 'targetRow = targetRow + 1End IfNext jEnd IfNext i' ############保存并关闭目标工作簿wbTarget.Save
'    wbTarget.CloseMsgBox "数据复制完成"
End Sub

UserForm1的代码

Sub dateToCheckCommandButton_Click()Sheet1.dateToCheck = dateToCheckTextBox.Text
End SubSub sourceSheetNameCommandButton_Click()Sheet1.sourceSheetName = sourceSheetNameTextBox.Text ' 把文本框的内容赋值给源工作表名字变量
End Sub
Sub targetFilePathCommandButton_Click()Sheet1.targetFilePath = targetFilePathTextBox.Text
End SubSub targetSheetNameCommandButton_Click()Sheet1.targetSheetName = targetSheetNameTextBox.Text ' 把文本框的内容赋值给目标工作表名字变量
End SubSub CommitCommandButton_Click()Sheet1.CopyDataBasedOnDate '调用复制程序
End Sub

待续

基础逻辑完善

获取行号

获取源文件行号是从下往上找获取A、C两个必定会有内容的列的单元格不为空的逻辑，但是别人的工作表不一定是这样的

• 现在使用的是cell和max函数

lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
targetRowA = wssTarget.Cells(wssTarget.Rows.Count, "A").End(xlUp).Row
targetRowC = wssTarget.Cells(wssTarget.Rows.Count, "C").End(xlUp).Row
targetRow = Application.Max(targetRowA, targetRowC) + 2    '因为最后一行也是两行合并

搜了一下还可以用下面这些方法，日后试试

• 使用UsedRange属性：
  UsedRange属性可以返回工作表中使用过的范围，然后可以通过这个范围来找到最后一行。

Dim lastRow As Long
lastRow = ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count

• 使用SpecialCells方法：
  这个方法可以用来找到最后一行，特别是当表格中有空行时。

Dim lastRow As Long
On Error Resume Next ' 如果没有找到单元格，避免错误
lastRow = ThisWorkbook.Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
On Error GoTo 0 ' 重新启用错误报告

遍历日期算法逻辑

For i = 1 To lastRow '遍历循环If wsSource.Cells(i, "A").Value = dateToCheck Then '如果第i行是要对账的日期For j = i + 3 To lastRow '日期内循环If j - i > 13 Then '3是必要的内容 因为有客套话 假如内容十行的话 就够了'最好的办法还是判断日期内循环有没有到下一个日期 到了就直接跳 结束了i = j - 1Exit ForEnd IfIf wsSource.Cells(j, "A").Value = dateToCheck Then '如果已经碰到下一个日期 比如同一天的 还有可能不是同一天的i = j - 1 '节省遍历循环的时间 跳出循环Exit ForEnd IfIf Not IsEmpty(wsSource.Cells(j, "C").Value) And Not IsEmpty(wsSource.Cells(j, "D").Value) Then'如果没有到下一个日期并且数据有效  c列d列不都为空就可以了wsSource.Rows(j).Copy Destination:=wssTarget.Rows(targetRow) 'targetRow = targetRow + 1End IfNext jEnd IfNext i

我在源文件遍历日期的方法是从第一行查找到最后一行，如果遇到匹配的字符串，就再内嵌一个循环，因为一个日期内有很多数据需要一行行地复制过去，还要判断数据的有效性

这里解释一下
For j = i +3是因为序号那一行是两行合并，所以有效数据是日期+3行 ，j-i>13是因为一般来说一次送货不会超过十种货，也就是对应十行数据了

提升对单元格等对象的操作知识

可以看出我对对单元格本身不了解 内置函数和属性不熟 所以判断数据有效性的条件很抽象

功能升级

• 目前可以实现在页面不关闭的情况下一次插入一天的对账信息，但是这样也不够快，要是能一次插入一个时间范围内的对账信息就好了,估计要新增很多判断逻辑，以及对字符串的一些操作处理（因为有时候你的源工作表不一定就是2024/8/31这样的数据）,最好是再做一个控件。

• 输入文件路径有点麻烦，尝试改进成浏览文件的方式选中。

• 未来要加入判断插入的数据是否是已有数据的逻辑才行，否则会重复插入 而且最好能提示用户“重复插入了”

• 从送货单复制的数据到对账单并不是会完美一一对应，因为对账单和送货单所需要的信息不一定是一一对应的，可能会多或者少或者顺序不对，还有一些比较奇葩的是粘贴的目标列合并了，所以要怎么匹配列去进行粘贴 是一个问题