我的天

第一次加VBA+ADO+SQL里跨表查询到一个表之后，发现运行时间只需要0.12秒。那个时候我就觉得如果我要完成之前已经在PQ、PP和python的那个方案，在VBA里估计只需要很短的时间，有可能是一秒以内实现，但最长时间我感觉不会超过三秒。之所以有这种感觉是因为之前我用VBA的时候，如果我使用的是数组，使用的是数据最后一次性的打印出来，中间不显示，通常运行时间不会超过一秒。如果我不是用数组操作，而是在折腾单元格，比如做一些打印格式的转换，时间可能会长一点。那个时间的长短跟我电脑正在处理的任务多少有关，如果电脑比较空闲，时间会很短，但如果电脑正在运行其他的东西，比如FireFox里打开了N个网页，那么这个运行时间可能会长一点，甚至会超出我的想象。最终，当我把整个之前的那跨表查询方案在VBA里实现的时候，我办公室的那台电脑大概需要1.3秒。这个1.3秒是不确定的，有时可能需要1.4甚至1.5秒，但是也有可能1.2秒就可以。

当我在进行了一些数据的优化，比如当那个字典完全赋值给数组以后，就把字典关掉，又或者是减少一些变量，比如在我用SQL进行查询的时候，因为有些步骤太长，我的脑子又转不过来，所以我一个步骤跨了好多段。多段实际上是一路到底，后面不需要引用步骤中的数据，在写下一段的时候，我就不再定义一个新的变量，而直接沿用上一个变量名。连续三段，都是为了得出最后一个答案，在我优化之前，每一段我都会用一个新的变量名。仅仅是在使用完字典以后就把它关闭掉这个操作就让我的运行时间马上提升了0.2秒。我不知道为什么，效果居然如此明显，字典是个非常高效的东西，但原来之所以这么高效，非常有可能是因为它占用了资源。虽然释放字典这个操作基本上是在整个程序的最后部分，但依然能明显提速。当我慢慢地研究一行又一行的合并变量名以后。整个程序的运行时间有可能在一秒之内。通常是1.1秒，如果我同一个时间刷新多次的话，非常有可能会出现0.9秒。但就使用而言，你怎么可能就为了那个0.9秒，把它刷多次呢。因为刷新多次，实际上也花了好几秒，这是完全没有意义的。写这个VBA就是为了让你打开文件，输入相关参数以后进行查询，一次能查到的那个时间才是意义所在。PQ输入参数，点击刷新，第1次的刷新时间肯定是最长的，在成功的刷了第1次以后，继续刷，时间你会觉得明显缩短。所以的确最后这个VBA文件有可能在我的同一台电脑上刷出0.9秒，但在我的能力范围之内，我顶多能改到在那台电脑上首次刷新小于1.1秒。

运行效率这个东西在不同的电脑上效果是不一样的，在我做python方案的时候，我就已经明显感觉到了。python方案的运行时间大概6秒。在我家的电脑上大概需要7-8秒，在我那个不插电源的笔记本电脑上需要10秒。这个让我挺惊讶，因为笔记本电脑购买的时间比我家台式机组装的时间晚起码5年以上。之前我就试过，在笔记本电脑上插电和不插电压片，结果发现插电的时候性能会明显飙上去，CPU的使用率会飙起来。据我观察，在python方案的时候，CPU的使用程度要比内存大，运行PQ的时候刚好反过来。所以电脑的CPU越好，python方案的运行时间会越短。宿舍那台神舟miniPC5的运行时间大概跟我家里的电脑差不多。VBA的方案我没有在宿舍的电脑测试过，也没在笔记本电脑上测试过，但家里的那台电脑，我感觉运行时间有点不稳定，有可能会超过两秒，但有可能会是1.3秒。为什么会这么不稳定呢？VBA的那个脚本，我同事的运行时间通常能在一秒以内，曾经试过0.8秒，她办公室的电脑购买时间大概比我的晚两年。就我俩办公室的电脑来说，差距大概是0.1秒。对运行时间得大概6秒左右的python来说，她只需要5秒多一点。

运行一个跨表查询，VBA+ADO+SQL这套方案就只需要一秒，其实已经很快了。如果我把那个VBA根据输出表格的类型拆分为两个，我觉得运行时间能进一步的提升。毕竟其实输出的4个表格通常不会一起用到，但是如果现在需要一秒，单独一个也需要0.6秒，我为什么要做这个拆分呢？

接下来我会继续研究一下还能怎么改进，研究完以后再把这个发给专业同事，询问一下改进意见。

明明想好了，要早点回宿舍动感单车，但是还是不知不觉的拖到了9点多。明明想好了回到宿舍就不再开电脑做VBA相关的东西，结果还是忍不住，我满脑子都是那个玩意。所以周四的晚上，我又只是匆匆忙忙我完成了一个30分钟的动感单车课程。理论上周三对我来说是动感单车的大课，但实际上我只上了一节15分钟的Y5强度，虽然强度很够意思，但持续时间太长短。周四晚上，本来我打算好好干一场，结果又被时间所限。

周四晚上11点多我才开始研究把字符串分组连接的数组。思路很简单，就是建立一个字典，如果键是相同的，就把键值通过顿号连接起来。这只需要一个循环就能做到，但我在那里磨叽了好久，最大的原因是我没有踏踏实实地手动进行前几轮的试验，而是大概凭感觉写好就丢给程序，于是一次又一次地被程序报错。有些时候是下标超限。有些时候是出来的结果完全不是我想要的。最终我整出来了，但就好像只是运气，碰对了。最让我觉得自己很无语的是，明明我在里面设置了if，如果这个键跟上一个键的内容是一样的，那么就把这个数值通过顿号连接到上一个键的键值那里，但是我却一直没有写 else，实际上我只需要再写一条，余下的就把这个数值成为这个键的键值。我处理的是非常典型的字典，没有比这更近更简单的了，但是我却在那里兜圈子，我自己也觉得自己很无语。

搞定了这个字典以后，我又试着把之前已经汇总好的表跟这个字典合并起来。思路也是非常简单，因为我只需要在原来的那个表那里加一列。当那个字典里面的键等于这个表某一列的某个数据，那么就把这个键值放在数据所在行最后一列的下一列你。结果我折腾了半天，为什么就是没有结果呢？最后发现我一直被数组的最大值给搞迷糊了。什么是1，什么2。通常来说我喜欢从1开始计数，但实际上记录集直接输出来数组是从0开始的，而且那个行列结构刚好和从单元格获取数据形成数组是一个转置的关系，本来1和2就已经迷糊，然后还得实际上得2变成1。因为我要新增一列，所以应该是加在什么地方呢？大半夜脑子本来就不太好使，再加上这种转置加1就让我更加迷糊。所以我已经不记得系统弹出过多少次下标超限。最后还是那一招，当系统说我超限的时候，我用鼠标看一下参数，果然发现了我进行for循环的那些设置都是乱来的，虽然肯定就是两个循环一次互换的操作，但里面的i，j我乱写，于是我又在那里折腾了好长时间。搞笑的是那个合并的数组的确没有问题了，在用Ctrl+F8运行的时候，我的确看到那个数组的里面已经齐全了我要的东西，但为什么输出到工作表的单元格就是看不到我想要的呢？最终发现又是数组最大行与列的问题。显然我的大脑非常不善于处理这个。因为我需要的那个数据在我单元格输出区域以外的地方，所以工作表里死活看不到。如果我一开始就耐心点的，我就不需要走这些弯路，但是这两个数组方案我都是一开始抱着大概差不多的心态瞎写的。

学习C语言的时候，我没有现在那么好的调试工具，现在的调试工具时的确强大了，尤其是如果我在VSCode里面写python，但就是因为这些东西很强大，它们可以把我的问题描述得很清楚，所以一定程度上我也习惯了大概差不多瞎写，出现什么问题再去改，但是这种习惯如果用在VBE里就会让我经常碰壁、非常绝望。

昨天说到用VBA做多条件单汇总的分组，要怎么个做法呢？

VBA这个东西其实我并不太熟悉，我没有系统学习过，或者说其实是系统过的，但只是皮毛，而且已经是多年以前了，之后用得不多，所以忘记得差不多了。所以现在我只是需要用什么的时候就去研究。在开始之前实现我的目标之前，我也先去搜索了，得出大概两个方案，一个是用字典，另外一个是用SQL。就思路来说，我感觉SQL比较直接，因为那调用的是列的信息，各种聚合会非常方便。我不知道VBA里面的SQL到底是怎么个执行法。因为2003或者以前的office，如果要做跨表查询就得用SQL。我已经不记得那些跨表查询到底只是一个工作簿里面不同表，还是直接可以跨不同的工作簿，但可以肯定的是，如果某Excel文件里运用了SQL，那么这个Excel文件一旦移动，比如发给了别人或者是U盘拷贝到另外一台电脑就会查询失败。之所以这样是因为实际上Excel在电脑的某个地方存储了与SQL有关的某些东西。虽然你把Excel文件挪走了，但是存储在你电脑里面的某个东西还在原来的地方，那是一个绝对地址。不管那个到底是绝对地址还是相对地址，简而言之就是实际上你只能挪走了一部分的东西，另外一部分还在那个地方，但即便你那么高难度，那个电脑深处的东西也挪走了。即便把它像放在另外一台电脑相对位置一样的地方，但还是识别不出来。所以最终你只能放弃那个查询，然后贴同样的代码，在另外一台电脑上重新建一个，这个操作很逆天，非常不方便。正是因为我知道这个，所以虽然在合并查询上SQL很方便，但是不到迫不得已我不会这么干。如果我真要这么干，我就不用Excel，我用了Access。以前的 Office 2003专业版都是自带Access。说起数据库的操作，在那个地方用SQL就很自然。

想到分组的时候，我当然有考虑过SQL，但是我知道以前的Excel对这个很不友好。所以别人做的某些方案，SQL版本干的事情很简单，但前面一大堆的参数引用会让你眼花缭乱。相对而言，字典是VBA的原生，所以说VBA一定能很好支持，因为VBA的字典是提升VBA性能的一个重要体现。我觉得不仅仅是在VBA，在python里，用字典和不用字典做同样的查询，效果差别也非常明显。我想要的多条件单汇总要怎么做字典呢？我首先想到的当然是把条件都合并起来作为键，然后把要汇总的那个作为键值。这就保证了前面那些条件一定是唯一的，后面的键值做累加就行了。当然，你也可以不把前面所有条件都合并成键，你只需要把一些特征条件合并就行。把多条件合并起来，做一个循环，用分隔符把它们连起来就可以了。耗费我比较长时间的是，我建立了这个字典以后，我就需要把键和键值都输出重新组合成一个数组，那就是我想要的多条件单汇总结果。但问题是怎么才能把合并起来的键重新分解输出呢？我研究了半天。理论上被我连接起来的那些条件放的键实际上是一个字符串，而且里面有规律的分隔符。我利用分隔符就能把它重新打开，然后我新建一个数组接收这些被打断的字符串。这个接收了打断字符串的数组外加字典的键值，合并起来再复制给新的数组就是我想要的东西。思路是这样的，但是当我要执行的时候，我却一直卡在那个地方。关键就我在定义这个接受打断字符串数组的时候加了括号。为什么其它数组在定义的时候就得在名称后面加括号，但是这种接收字符串的数组在定义的时候就不能加呢？这个是我搞不懂的。事实是不加我就可以实现功能，加了就会说类型错误。这种接收打断字符串的东西不应该称作数组？我已经不记得到底我是从哪里学回来的这一招接收打断字符串形成数组的这种做法。生成一个表格标题的时候，这样做非常简单高效。

先把多条件合并成一个字符串，然后再把这个字符串打断。这种方案我在网上也看到了，别人也是怎么干，但是别人之所以没有像我这样卡住，是因为把字符串打断的时候他们并不是赋值给数组，而是直接在单元格里输出了，于是他们也就没有定义数组名称时加了个括号就卡住的烦恼。

最终，我实现了自己的目标用VBA接收源数据，然后进行加工处理，做一个多条件单汇总，最终在一个固定格式的表格里输出并另存为一个新文件。整个过程你只需要在VBA所在的文件里面按一下初始化按钮，然后把需要整理的数据贴进去，然后再按一个相应的生成文件按钮。文件生成的过程可以说是秒杀，几乎不需要时间。生成的文件会自动保存在VBA文件的相同目录。这个VBA文件你可以点保存，整理后的文件内容也会在VBA文件的相应页面输出。如果你需要查询，直接可以在那里看，如果你根本不想看，这个VBA文件你甚至不需要按保存。因为如果粘贴源数据前内容是空的，下一次你再进来的时候就不需要初始化，所以步骤就更为简单，下一次你只需要做粘贴以及点一下生成。明明转换这些数据很简单，VBA可以秒杀完成，为什么PQ就得转那么好几秒呢？之所以在做这个事情上我不做python方案，是因为你还得把源数据贴到Excel里面，保存关闭，再按python生成。这就意味着。虽然你只打开关闭了Excel文件一次，但是python在运行的时候又得把Excel文件打开关闭一次，这样就得耗双倍的开关时间。如果数据量很大，VBA很慢，又或者那得进行多个文件的拼接才能取得一个很大的数据，汇总的数据超过了VBA的处理范围，那么可能我会选择python方案，但现在，顶多每次就几百条数据，难度是非常低的，高效完成是我最看重的，因此VBA是最佳选择。

用过VSCode以后，当我在Excel的VBA里编写代码，真的非常痛苦，因为当你一句话没写完，就要在其它地方复制某些东西过去的时候，就会给你弹窗报错。为什么你非得给我弹窗呢？你给我下面波浪红线不好吗？又或者当我要运行的时候，你才告诉我那个不对不行吗？我写一半就不写了，并不是因为那是我写错了，而是因为我需要做其它复制粘贴，又或者是切换个窗口做一下别的事情。如果能在VSCode面直接编写VBA，然后能直接在Excel里面调试运行，不需要把代码贴回VBA那么折腾该多好。

我可以忍受VBA的各种规则，但是我真的不喜欢Excel自带的VBE。

VBA多条件单汇总字典方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

'crr（i, 1 To 7）为数组，多行7列，前6列为条件，最后1列为数据，需要汇总
Set dic = CreateObject("scripting.dictionary") '建立字典
    For i = 1 To UBound(crr)
        x = crr(i, 1)
        For j = 2 To 6
            x = x & "|" & crr(i, j) '合并分组依据
        Next
    dic(x) = dic(x) + crr(i, 7)
    Next
Dim err, frr() As Variant
ReDim frr(1 To dic.Count, 1 To 7) '新汇总数组
i = 1
For Each x In dic.Keys
    err = Split(x, "|") '打碎分组依据
    For j = 0 To 5
        frr(i, j + 1) = err(j)
    Next
    frr(i, 7) = dic(x)
    i = i + 1
Next

说到就做到的，感觉非常的美妙。

前天说到我应该可以在VBA上更进一步，把数据整理以及最基本的汇总通过VBA一步搞定，结果我昨天真这么做到了。前天也说到VBA的copy让我非常的烦恼，但在我印象之中，以前我的烦恼并没有这么明显，之所以这样是因为以前粘贴时我不是直接粘贴单元格的内容，而是先把单元格的内容赋值给数组，然后处理数组，然后再贴回单元格。copy看上去很简单，但是在单元格的层面直接赋值好像无论如何都没有任何效果。把所选区域赋值给数组，相对好理解一些，但是要把数组再贴回单元格，相对于python的解决方案来说，VBA就很不人道了。因为我习惯了用python的方风格，所以回到Excel，之所以贴来贴去贴半天都得不到结果，就是因为我在数组复制内容到单元格的时候并没有设定单元格区域。不设定那个区域，永远都是贴了个寂寞。除非你使用Excel自己的那个方案，那个特殊粘贴的方案。我觉得这挺奇怪，既然我用特殊粘贴的方案可以不选定区域，为什么我直接粘贴数组就必须得这样呢？情况就像数组一开始就得设定好，得多大。当然有些数组是不需要事先设定长度，比如如果那个数组传入的数据是一个被打断的字符串。我不知道为什么被打断的字符串就不需要这么干。VBA数组一旦设定了大小，很难改变的。长度如果一开始长了，但最终你没用那么多，我也知道了最终的长度是多少，今天我的解决方法是先把这个二维的数组转置，在保留原数据的基础上限定最终的长度，然后我再把数组转置回来。之所以这么绕，是因为VBA只允许修改最后一个维度的长度。如果你是一个二维的数组。第1个维度是行，第2个维度是列，所以我的方式是行列转置，再转回来，但如果数组是三维的或者多维的，中间的某些要改，显然就不能这样了，但如果你执意要这么干的话，估计可以再建一个数组，设定好目标长度，通过套叠循环的方式把前一个数组的数据赋值到下一个数组里。但是为什么非得这样干呢？如果要限定数据输出的话，你只要限定数据输出的单元格是你最终的范围就可以了。但实际上，像我这种得到了这个数组之后，还得加工，还得在这个基础上汇总，我的数组就不能有空行。比如这一次我要在得出的数组的基础上再进行一个多条件的单汇总，如果我的数组里面有空行，最终得到的字典将出现一个键的内容为空，键值为0的玩意。我也不想这么折腾但是不这么折腾，真的没法玩，当然了，在输出字典的时候依然可以先做一个判断，让键是空的内容不输出。

字典这个概念，我首先是在学习python的时候了解到的。在学习python字典的时候，我就曾经用字典做过字母或者单词的数量计算。现在，如果我要进行前面我说到的多条件单汇总，我就需要用到字典。

至于在VBA里怎么用字典法实现我的目标，下回分解。

当我把电子书的列表从800多KB改成几个以后，整个静态网站的生成速度就从之前的120秒降为20多秒。20多秒的生成速度跟生成markdown文件没什么区别了。准确来说，生成速度更快了，因为少了一个markdown转换的过程，我猜可能是这样吧。虽然我已经绕了一个大圈又重新做了一个判断，如果我直接从点点转换成静态网站，而不是先格式化为wordpress标准的XML格式，估计速度会更快，但可以肯定的是，如果那样的话，我还是得做不少的判断，因为点点的文件里面不同类型的核心内容是不一样的。其实最简单的方法，是我生成wordpress格式文件的时候把分类继续放在分类，不把博客的名字放在分类，不把分类作为其中一个标签，相对来说这样的改动是最简单的。其实现在我绕了一个圈再回去，也没麻烦多少，因为那个标签是第1个，而我的判断是，如果找到了某个标签，就马上停止循环，所以虽然每篇日志的标签有n个，但判断第1个以后就结束了。就循环来说，没耗多少时间，只是代码会显得又长又臭。

近段时间我一直在纠结如何把手动输入的字典搞得好看些。除了好看，也要容易维护。最明白的方式当然是自己写键值对，但是那么多的引号，那么多的冒号，那么多的逗号，想想都觉得好疯狂。最整齐最不容易出错的方式是一行一个，但那样的话，好像有点奢侈了。所以有时我也搞不懂自己，到底是想节省空间，还是维护容易。

昨天晚上，我纠结一个问题，如果某个单词被我用作变量，在字典里那个单词又是一个key，同时这个单词也是个文本。有没有某个函数能把某个变量只当作是某个名字的字符串呢？如果这样，我的某句话就可以写得很简洁。否则的话，当我调用函数的时候，我就要把这个单词写一遍，当作字符串再写一遍。或者你会说，我直接把这个变量等于这个字符串不就好了吗？显然，我之所以把那个单词当作变量，肯定是因为其内涵跟字符串不一样。所以我试试是不是自己挖了个坑给自己跳呢？我明明不应该把这两个东西命名成一样。

有些时候我会问一些很弱智的问题，明明我是知道的，但是一下子就是想不起来。归根到底，我觉得是我的基础还不够扎实。在完成了静态博客的部署以后。我还没想好我是继续把Think Python那本书从我中断的地方继续看下去，还是应该从头开始，复习一遍，加深印象，因为那些很基础的东西在用着用着的时候，我觉得自己已经忘光了。所以到用的时候，我又得翻箱倒柜。那些东西，我明明应该已经掌握的。

现在的静态网站转换，我是用很低端的字符串连接整出来的。有些字符串是一成不变的，有些字符串是变量。我就在变量的之前之后把静态字符串断开，储存在某个文件里。最后就像穿珠子一样，把动态和静态的东西连在一起，最终合成一个网页。实际上，这是一种模板的思路。接下来，我要利用python的模板引擎，把静态的东西写在模板里，把动态的东西放在某些参数中。这才是我的网页转化应有的方式，但我不确定，这样的转化效率会不会比我现在的低端做法还要低。对我来说，那是一个未知的世界，我非常想，立马通过实践得出答案。

人在求知的路上会越发明白到自己的无知。