我的天

回到家后我依然控制不住自己，要打开Excel的VBA实现各种功能。

前几天说过，我想把一个一维数组输出到Excel的列单元格。发现即便我用了转置，依然无法实现，于是我硬是把那个一维数组变成二维数组，然后不需要转置就可以把那输出到列单元格。这样我就需要在赋值的时候多写一些字，输出到单元格那里时完全按照一维的大小去设定。后来我在某天尝试之中偶然发现普通的数组的确是可以用一个转置输出到列，教程也是这么说的，但是如果那个数组里面有一个空值，就会导致错误。系统就会显示出现“类型不匹配”。到底这个不匹配是个什么东西？我觉得如果这是python，如果这是在VSCode里，问题会被描述得很精确，实际上就是因为我输出的那个一维数组里面有空值。为什么二维数组里面有空值可以随意输出，但一维数组却不行？这是让我觉得非常无语，因为我就是对同样的数据用过一维数组跟二维数组输出，就内容来说，两个没有任何区别，但问题就是VBA这个奇怪的东西，当你把有空值的一维数组输出到列单元格就会出错。一维数组正常输出到行单元格的时候是不会出错的，而列的时候之所以出错，是因为输出到列必须在外层套一个转置。处理掉那个null对我来说很简单，因为我要输出到列的那个数组是从其它数组里赋值过去的，赋值的时候我多一条判断，如果那是空就直接送一对双引号，也就是让那个元素变成空字符串，而不是null就行。这样的话我就可以在一维数组外面套一个转置，然后输出到列单元格。对所有编程语言来说，null都是一个让人觉得很敏感的东西，很难说得清那到底是什么，所以几乎所有编程语言都有个isnull去判断那个东西是不是null。如果你要对某些字段进行运算，一旦在里面遇到null，你的汇总全部会失败。那种风格跟在Excel里面处理器空的单元格，有没有都无所谓完全不一样。

第一次我是在Power Query里遇到null，当时我就觉得很奇怪，为什么明明应该是有数的，但我连加之后汇总列却没有数？后来发现原来被连加的某些列是空的，这样就导致其它列即便有数，最后汇总也是空。所以我就经常做很无聊的事情，如果我要连加，我就先把那些列全部转为0，然后在整个操作结束以后，再把那些0全部转为空。这些看上去很无聊，多此一举的事情如果不干，根本得不到正确的结果。当然，如果我不是在PQ里可视化操作，我完全可以在汇总列那里用条件语句把之前连加的那些列全部都用公式先判断，然后再加。如果所有这些事情都是在高级编辑器里面完成，显然修改汇总列会比较简单，但如果不想敲键盘，直接选定那些可能被连加的列，然后设为0，最后再把那设为空，从鼠标和人的逻辑操作上来说是比较直观的。

我发现如果要在VBA要进行工作簿open的操作，界面会闪，但如果用的是ADO+SQL的查询，完全没有那种感觉，但实际上ADO+SQL是可以进行写入操作的，所以我到底能不能通过那个东西无感新建文件呢？

周四的傍晚时分，我突然有点码农蓝调的感觉，因为好像无论我怎么整，前面总有无数的奇奇怪怪的问题。这些问题居然没有大路的答案。原因是Excel的SQL已经被阉割到一种没人能说得清的程度了。我就想知道到底Excel里的SQL有什么样的函数，知道有什么函数，知道函数怎用，才能以各种叠加的方式得出我的招数，但问题是人微软自己的手册都没有说清楚到底Excel里的SQL可以怎么个用法？相比之下，Access写清楚了，SQL Server也写清楚了，不同版本的函数不一样，些高版本能轻而易举函数就能实现的功能旧版本也有替代方法。但是Excel里的SQL像一个谜一样。你得不断尝试直到绝望。因为你拿着那个问题去搜索，没有结果，结果都是其它数据库的，虽然都叫做SQL，但差别真的很大。

的确用VBA+ADO+SQL搭配能解决一些小数据的问题，而且速度很快，但为什么微软在这个基础上还要继续整出 Power Query和Power Pivot，因为他们知道在操控数据方面，VBA本身真的有很多限制。当我死磕了一周以后，我发现VBA要死要活折腾半天出来的东西如果在PP里两下就搞定了，而且那还是在可视化的情况之下。至于PP，那是不允许你用不可行的方式去操控的，所以虽然三个都在考验逻辑，但是在Excel的SQL里面，我觉得对我最大的考验是，我明明知道要那么干，我明明知道用其它工具应该怎么干，但是无论如何我在这个Excel VBA里面就干不出来。

我遇到的某些问题，跟SQL没有关系，纯粹是VBA数组的问题。VBA的一元数组，如果要输出的话，它会在一行里输出，但如果你要把这个一维数组在列里面输出，你就得做个转置。我遇到的问题是，即便我已经设定了转制。系统依然说我的类型错误，最后我是怎么干的呢？明明我那个是一维数组就可以实现了，但为了可以顺畅输出，我硬是把那个东西设置为了二维数组，另外一维完全是空的。这样的话在我输出到单元格的时候只给予一列的空间也就是那空的第二维根本不用管他。经过SQL处理生成的记录集，如果要输出到数组，通常是一个二维数组，那个二维数组跟VBA自己的数组又是转置的关系的，那个记录集的数组编码是从0开始的，VBA默认的数组是从1开始的。如果在VBA里把一个字符串打断赋值给数组那又是从0开始的。在python里，默认就是从0开始，什么东西都从0开始了，所以你不需要为长度跟起始数值还有突然间又有个转置之类烦恼。

周四我遇到一个算是逻辑意外的事件。我要筛选某个表里某一字段不包含某个关键词的记录，但问题是那个字段里的东西有关键词也有空，我需要筛选出来的记录是关键词以外的其它字符以及空的。当我where 字段A not like ‘%关键词%’的时候，结果出乎我的意料。因为那个关键词是包含的关系，所以我没有办法精确控制，所以我必须在关键词的前后加上%。这句筛选的结果是字段里所有那个字段的记录都没有被筛选出来。不就是一个包含的关系吗？Excel的SQL里面允许用正则吗？最终我用的方式是在where里面用两句话，一个是not like，筛选到那个字段里没有关键词，但是有其它字符的记录，另外一个是用or的关系搭配一个isnull(字段A)，这到底是什么情况呢？如果在其他地方，一个contain之类的东西就能表达出来，如果允许用正着，正则也能很好表示不包含关键词，Excel的SQL到底允许我用什么工具呢？

SQL in Excel这把刀到底应该怎么玩？？？

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

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

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

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