VBA开发：自动添加单元格注释以实现类似三维表格的单元格展开功能

目录

1 给单元格添加注释的意义—实现类似三维表格的功能

2 阅读本文可能涉及到的几个VBA知识点

3 实现添加注释的示例

3.1 实现自动添加注释的步骤

3.2 示例的基本描述

3.3 示例编程

3.3.1在Worksheet的SelectionChange事件中编程

3.3.2分解单元格以反斜杠”/“分隔的字符串

3.3.3读取供应商数据表，为每一个供应商代码找到对应的供应商名称。

4. 总结

---

1 给单元格添加注释的意义—实现类似三维表格的功能

        我们用VBA开发程序的时候，需要将数据设计成比较清晰的数据库格式，便于阅读和检索。一个程序往往需要处理的不只一个数据表单，很多时候，是多个表单之间的数据相互关联。不同表单的数据之间有一对多的关联，无法在一个工作表中将这些一对多的关联全部展示出来。

        Excel提供的是一个二维的表格，但是这个二维表格中的每一个单元格，可能包含另一个数据表的关键字，这种时候如果我们能将这个表格展开，就提供了一种类似三维表格的功能。

        简单的说，我们的用户在阅读一个表格，但是对某一个单元格的内容需要进一步了解，这时候，我们可以设计成让用户点击一下这个单元格，程序就自动去查找另一个表得到进一步的信息，并将这些信息变成这个单元格的注释，这样就相当于对这个单元格进行了一次展开。通过这种形式，我们就把excel的二维表格，变成了一种类似三维表格的功能。

        设想一下，通过这种形式，我们可以给用户提供一个非常简洁的界面，但是用户可以根据需要即时展开，是不是体验很好？

2 阅读本文可能涉及到的几个VBA知识点

        这一小节是我的一个尝试，我试图将本文后面示例用到的一些关键的知识点列出来，读者们在继续阅读之前可以先简要看一下，如果对这几个知识点都很了解，那就不用浪费时间继续看了，如果对部分或全部不太了解，那就欢迎继续。

1. 工作表WorkSheet的SelectionChange事件编程。
2. 了解在事件编程中的Target参数的含义。
3. 通过Target.AddComment方法来给单元格添加注释。
4. 通过Target.Comment.Shape属性来设置注释的字体，字号，注释显示的宽度和高度。
5. 如何区分选择的是多个单元格还是一个单元格。
6. VBA中的字符串操作函数Left，Right，Instr。
7. VBA设计函数时，ByRef和Byval两种不同的传参方式。
8. VBA使用Public和Private定义函数的作用域区别

3 实现添加注释的示例

3.1 实现自动添加注释的步骤

        要实现给单元格自动添加注释信息，步骤如下：

1. 在Worksheet的SelectionChange事件中编程，选择满足条件的单元格时，分析单元格的内容，决定要添加何种注释。
2. 拆解单元格的内容，将单元格中包含的文本拆解成为包含另一个数据表单的关键词的数组。
3. 根据拆解得到的关键词数组，搜寻另一个数据表单，得到相关的信息。

        下面，我们通过一个实例来讲解如何实现自动添加备注。

3.2 示例的基本描述

        如果我们设计一个采购系统，可能对不同的采购物资，需要指定只允许从几个供应商之间采购，采购订单中只是显示允许的供应商的编码，而供应商的具体信息则保存在另外一个表格里。这时候，我们的采购人员在看订单数据时，就特别希望能够直接在采购页面能看到供应商名称等信息，在这种情形下，通过程序自动给单元格添加注释就可以完美满足这种需求。

        我们设计的例子有两个表。表1就是我们的采购订单，我们将它放在工作簿的Sheet5中，如图1所示。表2 就是我们的供应商名录，放在Sheet6中。现在我们表1中的指定供应商显示的是类似于L001/L002/L003这样的供应商编码，但是我们非常希望，将鼠标移到某个指定供应商单元格时，能自动查询这个代码的含义，并将供应商名称自动添加到这个单元格的注释中，如图1所示。

表1 需要添加注释的采购信息表

序号	采购物资	指定供应商
1	铅笔	L001/L002/L003
2	圆规	L002/L003
3	鼠标	L004/L005
4	笔记本电脑	L003/L004/L005
5	5号电池	L001/L006

表2 供应商数据表

供应商编码	供应商名称
L001	A城文具公司
L002	B城铅笔厂
L003	C城先进贸易公司
L004	D城电脑公司
L005	E城电脑公司
L006	F城文具公司

         接下来我们一步一步实现这个示例。

3.3 示例编程

3.3.1在Worksheet的SelectionChange事件中编程

        这一步，我们主要是要在SelectionChange这个事件发生时，选择满足条件的单元格。并调用自己编写的函数MyGetSupplier()来得到需要添加的注释信息，然后使用Target.AddComment来将注释添加到选择的单元格。

        然后我们通过Target.Comment.Shape属性，来设置注释的字体和字号，以及注释显示框的宽度和高度。

        需要注意的是，如果不采取任何别的措施，在选中多个单元格，或者选中的单元格是空白时会报错。我们可以使用Target.Count属性来指定只在选中一个单元格时添加注释，可以使用Target.Value属性来指定只有选中的单元格非空白才添加注释。

        SelectionChange事件，是Worksheet对象的事件，在某一个工作表中，使用鼠标或键盘方向键更改选择区域时触发。而上文提到的Target，它不是一个Excel VBA的基础对象，它是SelectionChange事件的参数，类型是Range对象类型，表示我们在SelectionChange事件触发时，选择的目标集合。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)‘事件编程
Dim supplierlistarr As Variant‘存储供应商列表信息
ReDim supplierlistarr(1 To 1000, 1 To 2)
If Target.Count = 1 Then‘只在选择一个单元格时起作用
If Target.Value <> "" Then‘只在所选单元格非空时起作用
      If Target.Column = 3 Then '供应商代码
         If Not Target.Comment Is Nothing Then Target.Comment.Delete‘删除原有注释
           supplierlistcount = Sheet6.Range("a65536").End(xlUp).Row – 1‘找到供应商数据表的行数
           supplierlistarr = Sheet6.Range("a2:b" & (supplierlistcount + 1))‘将供应商数据读到数组
           ‘添加注释，并设置注释的各类重要属性
           Target.AddComment (MyGetSupplier(Target.Value, supplierlistarr, supplierlistcount))
           With Target.Comment
                 .Shape.TextFrame.Characters.Font.Size = 11  '设置字号11
                 .Shape.TextFrame.Characters.Font.Name = "黑体"  '设置字体黑体
                 .Visible = False  '不点击时隐藏
                 .Shape.Width = 280‘设置注释的宽度
                 rowA = 45
                 RowNumbers = Int(LenB(.Text) / rowA) + 1‘计算注释需要的行数
                 .Shape.Height = 20 * RowNumbers‘设置注释的高度
            End With
          End If
        End If
    End If
End Sub

3.3.2分解单元格以反斜杠”/“分隔的字符串

        接下来，我们自己编写一个Mystringbreakdown函数，将选中单元格中以“/“反斜杠分隔的字符串分解成为一个包含全部指定供应商代码的数组。

        这个实现比较简单，主要用到了Left，Right，Instr这几个函数。相信读者们都很容易实现，我这里这个示例仅供大家参考。注意这里我们将函数定义成为Public，将来别的工作表需要用到这个函数时就也可以直接调用。

Public Function MyStringbreakdown(ByRef mystring As Variant, ByRef stringcount As Integer, ByRef stringarr As Variant) '分解自定义字符串
If mystring <> "" Then
     '''分置到临时数组中
     stringcount = 0
     str = mystring
‘寻找分隔符“/“，将每一个分隔符前面的字符串提取出来
     Do Until InStr(str, "/") = 0
         stringcount = stringcount + 1
         stringarr(stringcount)=Left(str, InStr(str, "/") - 1)
         str=Right(str,Len(str)-InStr(str, "/"))
      Loop
      If str <> "" Then
           stringcount = stringcount + 1
           stringarr(stringcount) = str
      End If
 Else
        stringcount = 0
  End If
End Function

3.3.3读取供应商数据表，为每一个供应商代码找到对应的供应商名称。

        接下来，我们自己写一个MyGetSupplier函数，将选中单元格的内容传入，并将供应商数据表以数组形式传入，并返回一个用于添加注释的文本。

        在这个函数中，我们首先调用前面编写的MyStringbreakdown函数，来分解单元格文本，得到一个供应商代码数组，然后查表，得到供应商名称，最后转换成文本形式返回。

        注意在这个函数中，我们用到了ByVal和ByRef两种不同的函数参数类型，这里稍微解释一下。在VBA中，ByVal表示传值，形参和实参没有关联，在函数内部改变这个参数的值，并不会影响调用函数时的那个参数。而ByRef表示传址，在函数内部改变的话，函数外部也同样会改变。我们一般对数值型的参数采用传值法，而对数组、指针型的产生采用传址法，能取得较高的效率。需要特别指出的是，字符串类型的变量是属于指针型变量。

        之所以说指针型的变量传址效率更高，因为传值法需要将实参拷贝到形参，传址法只需要传递一个起始地址即可。一般指针型的变量涉及到较多的数据，很显然，传址法可以节省大量系统开销。

Public Function MyGetSupplier(ByRef supplierstr As Variant, ByRef supplierlistarr As Variant, ByVal supplierlistcount) As Variant 
    Dim tempcount As Integer
    Dim temparr As Variant
    ReDim temparr(1 To 200)
        tempstr = supplierstr
        tempcount = 0
        Call MyStringbreakdown(tempstr, tempcount, temparr)‘调用分解函数
        For j = 1 To tempcount
            tempfound = 0
            For k = 1 To supplierlistcount '供应商代码总数
                If Replace(temparr(j), " ", "") = Replace(supplierlistarr(k, 1), " ", "") Then
                    tempfound = 1
                    If MyGetSupplier = "" Then
                        MyGetSupplier = supplierlistarr(k, 1) & "--" & supplierlistarr(k, 2)
                    Else
                        MyGetSupplier = MyGetSupplier & Chr(10) & supplierlistarr(k, 1) & "--" & supplierlistarr(k, 2)
                    End If
                    Exit For
                End If
            Next
            If tempfound = 0 Then
                MyGetSupplier = "编码" & tempstr & "部分或全部无定义."
                MsgBox "编码" & tempstr & "部分或全部无定义."
                GoTo plannederrorlabel‘错误处理标签
            End If
        Next
    Erase temparr‘清空临时数组变量
    Exit Function
plannederrorlabel:‘错误处理，此处空置
End Function

4. 总结

        这篇文章其实就讲了很简单的一件事，如何给单元格添加注释。写得这么长，其实主要是本人认为，编程本身不是重点，重点是如何利用编程来实现有意义的功能。我们掌握了VBA的全部语法，其实也不一定有多大意义，反而，如果能先设计一个有意义的需求，然后根据需求去搜寻学习一些必要的VBA知识点来实现这个功能，价值会更高。所以，本文是先从功能设计的高度来剖析的，希望能获得部分读者的共鸣。有兴趣的朋友，可以留言进一步讨论。