Microsoft VBA Excel 循环生成YAML配置文件

项目场景

简述：
有一个内容是全部产品的特性，名为SummaryTable的xlsm文件，主要操作对象是SummaryTable的sheet（名叫Output）和sheet（名叫Total）。需要在SummaryTable的宏中编写VBA代码实现循环生成多个配置文件。

背景知识——名称定义（Named Range）

在Microsoft Excel中，名称定义（Named Range）是一个功能，允许用户给单个单元格、单元格区域、公式或常量赋予一个有意义的名称。这些名称可以在工作簿的任何地方使用，以代替单元格引用，使得公式更易于理解和维护。

例如，假设用户有一个单元格区域A1:A10，这个区域包含了公司的所有部门名称。则用户可以给这个区域命名为“Departments”，然后在任何公式中使用这个名称，而不是使用“A1:A10”。

创建和使用名称定义的步骤通常如下：

1. 选择单元格或范围：首先选择Excel工作表中的单元格或单元格范围。

2. 定义名称：

   • 通过导航到“公式”选项卡下的“定义名称”或“名称管理器”来创建一个名称。
   • 在“名称管理器”对话框中，创建、编辑、删除和查找所有工作簿中的名称。

3. 使用名称：一旦名称定义好后，在公式中可以使用它们，例如 =SUM(Departments) 将会求和定义为“Departments”的范围A1:A10的所有值。

4. 管理名称：可以通过“名称管理器”查看和管理工作簿中所有的名称。

名称的命名规则包括：

• 名称必须唯一。
• 名称不能与工作表中的单元格引用冲突，例如，不能命名为“A1”。
• 名称可以包含字母、数字、下划线和点号，但必须以字母或下划线开头。
• 名称不能包含空格和大多数特殊字符。
• 名称是区分大小写的，但Excel通常将它们当作不区分大小写。

背景知识——YML 文件

YAML表示YAML是不是标记语言，而这种文件格式技术用于文档。这些文件都保存在纯文本格式，并附加了.yml扩展。高效的数据序列化是在开发商的主要目标.yml格式，因为它使用户能够创建.yml文件与任何特定的标记语言的内容无关。这些.yml文件也可以被用于创建，打开和编辑纯文本文件制定的任何文本编辑器阅读，无论是文字编辑软件，如Microsoft记事本和Microsoft写字板，或为Mac平台，如苹果的文字编辑软件，基于Microsoft Windows的系统。 YAML库也可用于将阳明格式纳入多种编程语言。这些编程语言可以是红宝石，C / C ++，Python和Perl中，PHP，Java和Javascript中，AJAX，C＃等。例如，在一个文件.yml扩展可以存储内容和作为与回报率（Ruby on Rails的）编程功能实现的数据库文件，以及这个数据库文件可用于在每次用户尝试连接到存储关于网络连接的信息网络数据库。¹

YAML 以数据为中心，使用空白、缩进和换行符对数据结构进行编码。因为 YAML 旨在易于读写，并且可直接映射到现代编程语言的数据结构，它成为了许多自动化工具、部署脚本和配置文件的首选格式。

以下是YAML的一些关键特性：

缩进和层次结构

YAML 的设计重点是人类的可读性。它的语法简洁、清晰，并且易于理解。YAML 文件中不使用花括号、方括号和逗号作为数据结构的界定符，而是使用空白和缩进。

YAML 使用缩进来表示层次结构，这类似于 Python 语言的做法。子元素比父元素缩进更多，从而形成了一个树状结构。缩进的空格数量不是固定的，但是同一层级的元素必须使用相同数量的空格缩进。

数据结构

YAML 支持几种基本数据结构：

• 标量（Scalars）：单个的、不可分割的值，如字符串、整数、浮点数、布尔值、null 等。
• 序列（Sequences）：一个有序的列表，通常表示为一个带短横线（-）的项目列表。
• 映射（Mappings）：表示键值对的集合，类似于 Python 中的字典或 JSON 中的对象。

键值对

键值对是 YAML 中表示映射的基本方式，其中键后面紧跟着一个冒号和一个空格，然后是值。键通常是字符串，但也可以是任何其他的序列化数据类型。

锚点和别名

YAML 允许定义一个锚点（使用 & 标记），可以在文件的其它地方通过别名（使用 * 标记）引用该锚点的值，实现重复内容的复用。

扩展

YAML 支持从其他文件中导入内容，或者扩展已有的结构。

注释

YAML 支持使用井号（#）添加注释。注释内容不会被解析器处理。

示例

下面是一个简单的 YAML 配置文件的例子：

# This is a comment
name: John Doe
age: 30
married: true
children:
  - name: Jane Doe
    age: 10
  - name: Doe Junior
    age: 8
address:
  street: 123 Main St
  city: Anytown
  zip: 12345

在这个例子中，定义了一个人的信息，包括姓名、年龄、婚姻状况、孩子信息和地址。孩子的信息是一个列表（序列），而地址是一个映射。

用途

YAML 常用于各种场景，包括：

• 配置文件：许多软件和服务使用 YAML 来配置程序选项和参数。
• 数据交换：YAML 可用于应用程序之间的数据交换，尤其是那些需要人类编辑的场景。
• 编排文件：在 DevOps 和云计算中，YAML 被广泛用于编写 Docker Compose 文件和 Kubernetes 的配置文件。

注意事项

• 缩进必须使用空格，不能使用制表符（Tab）。
• 缩进层级必须一致，以免引发解析错误。
• YAML 是区分大小写的。

---

方案详解

第一步：循环"Output"中"产品编号"这一列（第一个单元格被定义名称为No_Product）全部
有值单元格；

两种写法：

' 获取产品编号列的范围
Set rngProductNo = wsOutput.Range("No_Product").EntireColumn
' 获取最后一个单元格的行号
LastRow = wsOutput.Cells(wsOutput.Rows.Count, rngProductNo.Column).End(xlUp).Row

' 循环产品编号列
For Each cell In rngProductNo.Cells
    ' 如果已经超过了最后一行则退出循环
    If cell.Row > LastRow Then Exit For

' 循环No_Product列的值
For Each cell In wsOutput.Range("No_Product").Offset(1, 0).Resize(wsOutput.Cells(wsOutput.Rows.Count, wsOutput.Range("No_Product").Column).End(xlUp).Row - wsOutput.Range("No_Product").Row, 1)
    value = cell.Value
    If value = "" Then Exit For

第二步：在每一次循环中，判断"输出信号"这一列（第一个单元格被定义名称为Indicator）中的对应单元格是否是"是"，如果是"是"则需要把对应的"产品编号"输入到被定义名称为Input的单元格中，随后刷新"Output"和"Total"；记录"文件名称"这一列（第一个单元格被定义名称为FileName）中的对应单元格内容为字符串形式的FileName变量；随后将sheet（名叫Total）定义名称为SaveTo的范围输出到记事本中并保存为以".yml"作为后缀的文件（该文件名称为"config_"+ FileName变量），保存的地址是在定义为FileAddress的单元格中。

第三步：放入错误提示代码并保存文件。

---

总结

Sub ProcessSummaryTable()
    Dim wsOutput As Worksheet, wsTotal As Worksheet
    Dim rngProductNo As Range
    Dim cell As Range
    Dim LastRow As Long
    
    Dim FileAddress As String, FileName As String
    Dim SaveToRange As Range
    
    Dim FileSystemObject As Object, TextFile As Object

    ' 错误处理
    On Error GoTo ErrorHandler

    ' 设置工作表
    Set wsOutput = ThisWorkbook.Sheets("Output")
    Set wsTotal = ThisWorkbook.Sheets("Total")
   
    ' 获取文件地址
    FileAddress = wsOutput.Range("FileAddress").Value
    
    ' 获取产品编号列的范围
    Set rngProductNo = wsOutput.Range("No_Product").EntireColumn
    ' 获取最后一个单元格的行号
    LastRow = wsOutput.Cells(wsOutput.Rows.Count, rngProductNo.Column).End(xlUp).Row

    ' 循环产品编号列
    For Each cell In rngProductNo.Cells
        ' 如果已经超过了最后一行则退出循环
        If cell.Row > LastRow Then Exit For
        
        ' 判断输出信号列是否为"是"
        If wsOutput.Range("Indicator").Offset(cell.Row - wsOutput.Range("Indicator").Row, 0).Value = "是" Then
            ' 更新Input的值
            wsOutput.Range("Input").Value = cell.Value
            ' 刷新Output工作表
            wsOutput.Calculate
            ' 刷新Total工作表
            wsTotal.Calculate
            
            ' 获取文件名称
            FileName = wsOutput.Range("FileName").Offset(cell.Row - wsOutput.Range("FileName").Row, 0).Value

            ' 获取SaveTo范围
            Set SaveToRange = wsTotal.Range("SaveTo")
            ' 创建文件系统对象
            Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
            ' 保存文件
            Set TextFile = FileSystemObject.CreateTextFile(FileAddress & "\config_" & FileName & ".yml", True)
        End If
    Next cell
    
    ' 清理对象
    Set FileSystem = Nothing
    Set TextFile = Nothing
    Set SaveToRange = Nothing
    Set rngProductNo = Nothing
    Set wsTotal = Nothing
    Set wsOutput = Nothing
    
    ' 正常结束
    Exit Sub
    
ErrorHandler:
    ' 错误处理代码
    MsgBox "An error occurred: " & Err.Description
End Sub

---

1. 【搞定yml文件】.yml文件基本用法汇总 ↩︎