在Excel中使用VBA生成树状表格.pdf

64Computer Era No. 10 2007 在Excel中使用VBA生成树状表格 李心玉， 葛莹 （河海大学土木工程学院，江苏南京210098 摘要介绍了利用VBA在Excel中编写 “宏＂，生成树状表格的方法。借助于该方法可以提高制作表格的效率。 关键词 VBA Excel; 树状表格；控件；插入；合并 0引言 在实际应用中，我们经常会用到形如图1 表式1和表式 2所示的表格形式。因为这种类型的表格可以被抽象为一种树 状结构，所以也称为树状表格叽这种树状形式的表格应用范围 很广，它可以是整 个表格，也可以是表格中的某些部分（如表 头）。目前，使用Micros oft Office中的Excel可以直接手工绘制 树状表格，但其操作过程复杂，效率低，缺少灵活性；特别是对 于表格的层次和形状都没有确定的情况，手工直接绘制的方 式，就会很不方便。 Excel作为大多数用户所青眯的表格处理软件， 其强大优 势还在于它提供了宏语言VisualBasic for Appl ic ationVB A。 VBA是新一代标准宏语言， 是基于VisualBasic for Windows 发展而来的，两者有着相似的语言结构，严格地说， VBA是VB 的子集。通过VBA进行编程，可以提高Excel的效率和增加新 的功能。 本文结合笔者在实际编程过程中所写的一个应用程序，介 绍使用ExcelVBA语言生成树状表格的方法。这种方法能够 提高表格生成的效率和灵活性。（这里只介绍生成表式1的表 格形式，要生成表式2的表格形式，只要把程序中对应的行列 坐标变化一下即可。） 1实现过程 1.1实现思路 下面以图1中表式1为例，来描述生成树状表格的具体实 现思路。为了便于说明，给表式1的树状表格中的单元格赋予 相应的字母符号（见图2。根据树状结构，该树状表格有4层， 其中A为根结点；B、C、..、、J为支结点；K、L、R为叶结点。 二／三 表式1表式2 图1图2 表式l 在Excel中表格的单元格组成是由行列坐标所确定的。绘 制树状表格时，叶结点占一个单元格，父结点所占的单元格数 可能不止一个，此时就要合并相应的单元格。而父结点所占的 单元格数应是其所有叶结点的数量。例如图2 中，其中B 有 K、L,M、N共4个叶结点，所以B 所占的单元格数为4;而A有 K,L、R共8个叶子，所以A所占的单元格数为8。 根据这 一特�. 我们可以从作为根结点的单元格A见图3 1开始，生成最简单的树状表格（见图32,其中A为父结 点，B、C,D为子结点。从表格的形式来看，B、C,D分别占一个 单元格，则A由原先的一个单元格生成为占3个单元格的合并 单元格，此时数字3正好是父结点A的分支度。然后再分别从 B 、C,D结点开始，分别生成以B、C,D为父结点的树状表格（见 图33。B、C,D由原先的 一个单元格生成为分别占2 、2 、2个 单元格的合并单元格， 而此时数字2也分别是结点B、C,D的 分支度；同时A也随之生成为占6个单元格的合并单元格。如 此反复，逐层生成最终就可以生成所需的树状表格。 区 ｀ 1 2 3 图3 实际上，由一个父结点生成其子结点的过程可以看作是一 个独立的过程，所以整个树状表格的生成过程也就是这个过程 的重复进行。 此过程需要解决的问题主要有两个 一是如何控制父结点 生成相应数量的子结点；二是如何确保父结点的合并单元格所 占的单元格数始终与叶结点的数量相同。 Excel中，在合并单元格的范围内，在相应的位置插入或删 除任意的行数或列数，合并单元格所占的单元格数也会随之增 加或减少。同时， ExcelVBA是一种采用对象屈性”、“对象 方法 ”的语法结构的面向对象程序语言。 笔者根据这些特点，采用插入列然后合并单元格的方法来 控制生成其子结点，并使父结点所占的单元格也随之生成。同 时在Excel工作表中以作为父结点的单元格为对象， 通过控件 并利用VBA语言对此过程进行编程。 1.2主要算法和代码 1打开Excel应用程序，新建 一个工作表；在该工作表中 创建一个如图4所示的控件， 并将控件的位置、大小均设置为 固定。 妇树状表格1 图4 2在工作表中创建一个名为BuildTree的宏，其主要实现 代码如下 计算机 时代 2 0 0 7 年 第 1 O期 6 5 。生成树状表格的代码 S u b B u i ld T r e e D i m n A s I n t e g e r ．X As I n t e g e r ，Y As I n t e g e r’确定 当前激活 单元格 的行列 坐标 X， Y 以及 分支度 n n 0 ’ 选择将作为父结点的单元格为 当前活动单元格 ； 并在活动单元格 内输入其分支度， 即父结点含有的子结点数量 n Ac t ive Ce l 1 ．Va l u e x Ac t iv eCe l 1 ． Ro w y Ac t iv e Ce l 1 ． Co l u mn ’ 根 结点的 生成过程 ’因为在 E x c e l 中， 插入行或列的操 作默认 的是向上插入 行 ， 向左插 入列 。所 以在所合并的单元格的范 围内， 除 了最上 边一行 和最左 边一列 的位置 以外的任何单元格 位置插入 行或 列 ， 合 并单元格都 会随之增加相应的单元格 C e l l s x-1 ， y ． S e le c t m S e le c t io n ．Co l u mn ’判断 当前活动单元格 的位置。根据 当前 活动单元格上一行单元格 的列坐标 来确 定 当前 活动单元格是 否 位 于树状表格的最左端 F o r i 1 T o n 1 ’ 利 用循环 完成列的插入 ， 插入的列数是 由所输 入的分 支度来确定 I f m y T he n C o lu mn s y 1 1 _ I n s e r t ’如果 位于最 左端 ， 则从 下一 列开始 插 入 新 列 El s e C o lu mn s y ． I n s e rt ’ 如果没有位 于最左端 ， 则从 当前列开始 插入新列 En d l f Ne x t i R a n g e Ce l l s x ，y ．C e l ls x ，Y n -1 ． S e l e c t Se l e c t i o n．Mer ge S e l e c t i o n ． C l e a r C o n t e n t s ’合并所选择 的单元格 ， 从 而完成根结 点 的生成过程 设置所生成树状表格的格式， 以下只是绘制了表格的边框线， 可以 根据需要设置字体、 线型等其他格 式 R a n g e Ce l l s x ，y ，Ce l ls x 1 ．yn -1 ． S e le c t S e l e c t i o n ． B o r d e r s x l D i a g 0 n a l D 0 wn ． L in e S t y l e x l N o n e S e l e c t i o n ． B o r d e r s x l D i a g o n a l U p ． L in e S t y le x l N o n e W it h S e l e c t io n B o r d e r s x l E d g e L e f t ．Line St y l e xlCon t i n u ou s En d W i t h W it h S e l e c t i o n ． B o r d e r s x l E d g e T o p ．Lin eSt y l e xlC0 n t i n u 0u s En d W i t h W it h S e l e c t i o n ． B o r d e r s x l E d g e B o t t o m ．Lin eSt y l e xlC0n t i n u 0u s En d W i t h W it h S e l e c t i o n ． B o r d e r s x l E d g e Rig h t ．Line St y l e xlC0n t i n u 0u s En d W it h I f n 1 Th en Wit h S e l e c t io n ． B o r d e r s x l l n s i d e V e rt i c a 1 ．L i n eSt y l e xlCo nt i n u ou s En d W it h Wit h Se l e c t i o n ． B o r d e r s x l l n s i d e H o r iz o n t a 1 ．L i n eSt y l e x lCo nt i n u ou s En d W it h En d I f En d Su b 3 将所创建的控件指定到宏 B u i l d T r e e 。 1 ． 3 具体 操作过 程 下面以生成图 l中的表式 l为例， 描述使用该程序实现树 状表格的制作过程 见图5 。 1 确定树根 A的起始单元格， 可以是任意的单元格 ， 并输 入分支度 3 ； 2 再重新选择该单元格， 使其成为当前活动单元格， 单击 “ 生成树状表格” 控件 ， 生成形如图 5 3 的树状表格； 3 在支结点 B所在的单元格， 输入 B的分支度 2 ； 4 再重新选择该单元格作为父结点， 使其成为当前活动单 元格 ， 单击“ 生成树状表格” 控件， 生成形如图5 6 中的树状表格； 5 重复 3 、 4 臊 作过程。整个过程的关键是分别选择将作 为父结点的单元格为当前活动单元格， 并确定其分支度。 f1 2 f 3 4 t 5， 6 j L ， 图 5 2结束语 1 本方法的主要思想就是把制作整个树状表格的过程看 成是制作形如图 3 2 所 示的树状表格的重复过程，从而简化了 整个制作过程。 2 只要根结点所在的单元格位置确定后 ，可以按层的 顺序生成树状表格 ，也可以按照子树的顺序生成树状表格 比较具有灵活性 ；而且可以根据需要生成任意层和形状 的树状表格 。 3 本程序制作的树状表格的格式比较简单 ，读者可以根 据需要添加用来设置表格格式的程序。 4 本程序对分支度有一定的限制。一是在父结点生成在其 子结点之前， 必须明确其分支度， 而在生成子结点之后， 不易再 改动； 二是当父结点的分支度为 l 时， 则其子结点的分支度也只 能为 l 。对于上述限制以外的情况， 程序需要进一步完善。 参考 文献 【 1 】蒙德龙， 岑尉平， 刘宗伟． 使用D e l p h i E x c e l 制作树状表格【 J 】 ． 计算机 时代， 2 0 0 3 ． 8 4 4 - 4 6 【 2 】 美 J e le n ， B ． 美 ，J e le n ， B ． ， S y r s t a d ， T ． 著， 王军等译． 巧学巧 用Exc e l 2 0 0 3 VB A 与宏 中文版 【 M】 电子工业出, 2 0 0 5 ． 母 一 一 一 一 ■ 一 ～ 一 一 一 一 王蔓 ”