微博热搜“用Excel做班级全家福”原理解析

古人说“见贤思齐”，杨老师看完视频后也一时技痒，想复现一下陈同学这个创意，顺便看看能不能把它也作为一个案例，讲解给有兴趣的VBA学习者。所以在录课间歇抽出了几个小时，做了一个简易模仿版，复原了核心功能，效果如下：

那么接下来，咱们就结合这个简易版的代码，分析一下怎样实现这个陈同学这个排图软件。不过事先声明：毕竟这是一个功能比较完整的小软件，所以肯定要用到VBA的常用语法，以及一些常用的编程技巧。如果把其中每一个环节都讲明白，无异于讲解一门完整的VBA课程，所以本文只能讲解其中的关键技术环节，也就是其他常见VBA程序中比较少见的部分。

补充：写完后发现，撰写本文讲这些知识点的时间，是之前编写这个程序的两倍。

二、原版软件的技术路线和布局思路

要想复原这个软件，当然先要整理出原版程序的功能和技术路线。不过新闻视频中只给出了一些片段，所以需要我们先进行一些对比分析。

首先通过原视频这个画面中的 xlsm 文件名，可以确定陈同学使用的是VBA。这也符合我们的预期，毕竟使用公式实现这么复杂的效果不太实际。

接下来杨老师很感兴趣的一个地方，就是陈同学“怎样把照片调成大小不同的图片、拼在一起又正好组成一个矩形？”

其实只要把这个视频中的画面多做几次比对，就会发现个中道理：

从上面的两个截图中可以发现：其实每个位置上的方块大小都是固定的，只不过每次插入的图片不同。那么“矩形”+“大小不同的方块”+ “Excel”会让你想起什么呢？当然是单位里各位前辈最喜欢的——“合并单元格”！

于是复原版的第一步工作，就是想上面这样，在工作表中画一个合并单元格布局。当然，如果愿意投入更多的精力，我们也完全可以把这个布局设计做成VBA程序，从而根据图片的数量自动设计布局。这本身也是一个很有趣的小程序，不过今天我们先不考虑它，因为另一个与排版有关的问题更现实：假如照片与单元格的形状不一致怎么办？

比如，假设我们想把一个 矩形 图片缩放后，放到一个 正方形 的格子中，那么除非把原图片拉伸变形，否则这个格子的上下或左右边缘就会露出空白缝隙。

那么原作者是怎样处理这种情况的呢？观察新闻视频中的截图可以发现：（1）所有格子都没有空白缝隙（2）所有照片都没有变形 。因此可以推断：作者一定是将每个图片都在保持比例的情况下进行拉伸（或缩小），使它的宽和高中有一个与格子相同、另一个则等于或超过格子的宽高。

既然图片可能会超出格子的范围，自然就要进行裁剪，把超出的部分裁掉。可是这又遇到一个问题：假如某位同学的照片被裁掉了头部，将来十年聚会时岂不是要被罚酒？

那么原作者是怎样处理裁剪的呢？对比几张图片，可以大胆猜测，他使用了一个简单却好用的规则：图片顶部对齐格子顶部，图片中线对齐格子中线，然后剪掉左右两边多出来的部分。

搞清楚了这些思路，就可以直接进入下一个环节：怎样把图片插入到上面某一个格子中。

三、图片插入与对齐

在2010版以后的Excel VBA中，插入图片的方式基本都是使用 Shape 对象，也就是“形状”。在 《全民一起VBA 实战篇》 专题五第八回“A1做原点千秋万代，Shape管形状一统江湖”中，我们讲解过：每个工作表都有一个Shapes集合，里面存放了这个工作表中的所有形状（Shape）对象，比如箭头、方框、ActiveX控件，以及插图。

而Shapes集合有一个AddPicture方法，顾名思义，就是将指定的图片文件插入到当前工作表中，并作为一个新的Shape形状对象。具体用法就是：

p = Shapes.AddPicture( 图片文件路径，是否作为链接文件，是否随工作簿保存，左边坐标，顶部坐标，宽度，高度）

所以如果需要把硬盘上一个图片文件插入到指定单元格（比如B2），并且要求该图片左上角与该单元格对齐，就可以使用下面的方式：

上面的代码展示了插入图片的基本操作，具体含义看注释即可，篇幅所限，涉及到的函数细节请大家自行搜索。另外这里为了照顾初学者，没有使用 with 块语句，实际应用时建议大家改用 with 以便简化语法、提高效率。

上面的代码最后把图片的宽度设为与单元格一致。这样对于“瘦高”型的图片很合适，因为只要它的宽度与单元格相同，那么它的高度就肯定会超出单元格，我们后面直接裁剪就好。

但是对于“扁宽”的图片，如果只是把宽度设为与单元格一致，它的高度就会小于单元格高度，结果露出一片空白。

所以我们还要修正上面的对齐方式。比如：先不管图片是什么形状，先把它的高度设为与单元格一致；设置好高度后，再检查它的宽度，看是否小于单元格宽度：

1. 如果是，那么进一步拉伸，将其宽度也设为与单元格相同、从而高度超出单元格，这样后面将超出的高度部分裁掉即可；
2. 如果不是，即图片宽度已经大于单元格宽度，则执行中间对齐，再裁掉两边多出来的宽度。

所以上面的代码进一步修改为：

于是下一个问题就是：怎样裁剪图片？

四、图片裁剪

现在已经将图片对齐并缩放，使其覆盖整个单元格。但是怎样按照第一节中分析的规则，对该图片进行裁剪呢？这里就需要用到 Shape 对象另外几个很少用的属性：Shape.PictureFormat.CropLeft，以及 CropRight，CropUp，CropBottom 。

在 PhotoShop 等图形处理软件中，Crop 就是“裁剪”的意思。所以很明显，上面这四个属性就是告诉 Office ：这个Shape对象的左边、右边、上边、下边 分别需要裁掉多少像素。比如对于一个图形对象 sh ，下面的代码就是把它的左边和下边分别裁掉 20 与 50 个像素：

sh.PictureFormat.CropLeft = 20

sh.PictureFormat.CropBottom = 50

而我们在 《全民一起VBA 提高篇》 里学过：代表单元格的Range对象都有 width 和 height 属性，代表这个单元格的宽度和高度。

所以各位可能会想：只要先通过 Range("B2").Width 得到单元格宽度，再通过 Shape.width 得到图片的宽度，然后就可以通过下面的公式，计算出怎样“以图片中线为中心，左右各减去相同的像素，使其与单元格宽度正好相同”：

sh.PictureFormat.CropLeft =（图片宽度 — 单元格宽度）/ 2

sh.PictureFormat.CropRight = （图片宽度 — 单元格宽度）/ 2

思路很正确，但是却会掉到一个“坑”里面！假如图片 sh 没有经过任何缩放，那么 sh.PictureFormat.CropLeft = 10 就是左边删掉10个像素，没有任何问题。但是假如 sh 已经缩放到原来的一半大小，也就是缩放比为 0.5 ，那么再执行 sh.PictureFormat.CropLeft = 10 ，实际删除的只有 10 X 0.5 = 5 个像素！

而我们前面的代码中，将图片的高度或宽度设为与单元格一致，就相当于对图片进行了缩放，所以再设定 Crop 属性时，最终裁剪出来的图片并不符合预期。

因此为了修正这个问题，我们必须计算出图片在对齐单元格后的缩放比例，然后用下面的公式进行裁剪：
sh.PictureFormat.CropLeft =（图片宽度 — 单元格宽度）/ ( 2 * 缩放比例 )

sh.PictureFormat.CropRight = （图片宽度 — 单元格宽度）/ ( 2 * 缩放比例 )

至于缩放比例的计算方法，只需要用单元格高度或宽度，除以图片缩放前的高度或宽度即可。因此综合上面的思路，可以写出一个函数，将任意图片缩放裁剪对齐到任意单元格：

五、其他问题

解决了一个图片的对齐，剩下的就是做一个循环，把所有图片都对齐到不同的单元格中。整体流程和一些需要特别注意的细节如下：

1. 读入文件夹中的所有图片，可以使用VBA自带的Dir函数，详见 《全民一起VBA 提高篇》 中的讲解：

2. 按上面的方法，使用 DIR 和 While 循环读入所有图片文件，每读入一个，就将其作为形状对象插入到工作表中，并建议将每个Shape对象的Name属性都定义为该图片的文件名，以便查找。同时建议创建一个全局数组，把这些文件的名字同时存入数组，以便后面进行随机打乱排序。

3. 每次运行随机排位程序时，先对 2 中创建的数组进行随机乱序，比如循环扫描数组中的每个文件名，并将其与数组中另一个随机数下标的文件名互换，最后得到一个“洗过牌”的文件名顺序。

4. 假设整个单元格区域（包含所有准备放照片的格子）是 B2:G8，那么就使用 For Each rng in Range("B2:G8").Cells 扫描其中每个单元格，每找到一个，就从数组中取得下一个图片，然后调用前面的“对齐图片到单元格”函数，将它插入进去。

5. 但是在上面的扫描过程中必须注意：即使多个单元格已经合并在一起，在For循环中仍然会被当做6个单元格分别扫描一遍，而实际上我们只应该把图片插入到一个合并区域的左上角单元格中。

所以这里需要用到一些 《全民一起VBA 提高篇》 第16回“多区域合纵连横身形变幻,单元格定位精准边界分明”中讲解的合并单元格技巧：首先通过 if rng.Address = rng.MergeArea.Cells(1, 1).Address then ，判断出该单元格 rng 是否为合并单元格的左上角；如果是，则将 rng.MergeArea 作为目标单元格参数，传递给前面的“对齐图片到单元格”函数，用于插入图片。

六、总结

如果家能够看到这里，说明你的VBA基础非常不错。本文重点分析了“将图片插入、裁剪、对齐到指定单元格”的相关技术，而对其他部分比如“读入所有文件”、“随机排序”、“找到所有合并单元格”等相对常见的功能，只能扼要提醒。不过只要大家愿意花点时间去动手实践，按照这里点明的思路，相信是可以完成这个小程序的。

最后再次为陈同学的创意和友情点赞！