【工作表函数对数组的奇效】-劳动节的仪式感

大灰狼1976

【工作表函数对数组的奇效】

    劳动节我感觉需要有些仪式感，所以写了一些东西。

   一次偶然发现，有些工作表函数用于数组时竟然比VBA函数还便利（不谈效率），使我产生了兴趣，于是抽时间整理了以下的些许内容，自娱自乐不求关注。

事前说明

1、工作表函数应用于VBA效率不高，不建议在大数据处理时使用；

2、以下整理的各种工作表函数，都有一个共同点，就是能对数组进行整体处理（不循环），且可以改变数组元素；

3、统计类函数因为只是对数组的元素进行统计返回结果，并不改变数组内的元素，所以不在此次测试对象内；

4、一些经常接触的函数，如INDEX、TRANSPOSE、EVALUATE、TRIM等也不在测试对象内。

5、以下示例在EXCEL365全部通过，家里的EXCEL2007部分出错，其他版本未测试。

正文

【函数1：SUBSTITUTE】

    我们知道SUBSTITUTE的功能是替换字符串，一般用作工作表公式，功能相当于VBA里面的Replace()函数，但是在用于数组元素处理时，VBA的Replace()函数一次只能对一个数组元素进行替换，而Application.Substitute()却可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub SubstituteTest()

  arr= [row(1:1000000)&"abcdefghijk"]    '生成100万元素的数组，配置差的机器可适当缩小

  arr= Application.Substitute(arr, "fgh", "**")

MsgBox Join(Array(arr(1, 1), arr(2, 1), arr(3, 1)), vbCrLf)

End Sub

【函数2：TEXT】

    TEXT的功能是设置单元格内容的显示格式，功能相当于VBA里面的Format()函数，但是在用于数组元素处理时，VBA的Format()函数一次只能对一个数组元素进行替换，而Application.Text()却可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub TextTest()

  arr =[row(1:1000000)]    '生成100万元素的数组，配置差的机器可适当缩小

  arr = Application.Text(arr, "0-0")

  MsgBoxJoin(Array(arr(1, 1), arr(2, 1), arr(999999, 1)), vbCrLf)

End Sub

大灰狼1976

【函数3：REPLACE】

    REPLACE的功能也是替换字符串，但是不是根据字符特征，而是根据字符串中字符位置及长度进行替换，在用于数组元素处理时，Application.Replace()可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub ReplaceTest()

  arr =[row(1:1000000)&"abcdefghijk"]   '生成100万元素的数组，配置差的机器可适当缩小

  arr =Application.Replace(arr, 2, 2, "xx")

  MsgBoxJoin(Array(arr(1, 1), arr(2, 1), arr(3, 1)), vbCrLf)

End Sub

【函数4：ASC】

    ASC的功能是全角字符转半角字符，对我来说并不常用，在用于数组元素处理时，Application.Asc()可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub AscTest()

  arr =[row(1:1000000)&"Ａ１２"]    '生成100万元素的数组，配置差的机器可适当缩小

  arr =Application.Asc(arr)

  MsgBoxJoin(Array(arr(1, 1), arr(2, 1), arr(3, 1)), vbCrLf)

End Sub

【函数5：进制转换函数DEC2BIN、DEC2HEX、BIN2DEC...】

    这些函数用于数值的进制转换，对我爱好软件汉化的人来说比较有用，功能及用法相似所以只测试了其中1个DEC2BIN，在用于数组元素处理时，Application.Dec2Bin()可以批量处理，能够大幅减少代码的行数。

Sub Dec2BinTest()

  arr =[right(row(10:1000000),2)]    '生成100万元素的数组，配置差的机器可适当缩小

  arr =Application.Dec2Bin(arr)

  MsgBoxJoin(Array(arr(1, 1), arr(2, 1), arr(3, 1)), vbCrLf)

End Sub

大灰狼1976

【函数6：CONCAT】

    CONCAT的功能是连接字符串，对工作表区域有效，对数组同样有效，元素是数值和字符串都可以，二维数组时逐行连接，在用于数组元素处理时，Application.Concat()可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub ConcatTest()

  arr =[row(1:6774)&"a"]   

  s = Application.Concat(arr)  '连接后字符长度不能超过32767

  MsgBox s

End Sub

【函数7：TEXTJOIN】

    TEXTJOIN的功能也是连接字符串，但它可以设置分隔符，比CONCAT用途更广泛，对工作表区域有效，对数组同样有效，元素是数值和字符串都可以，二维数组时逐行连接，在用于数组元素处理时，Application.TextJoin()可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub TextJoinText()

  arr = [row(1:6775)]

  s = Application.TextJoin(",", True,arr)  '连接后字符长度不能超过32767

  MsgBox s

End Sub

【函数8：UNICODE和UNICHAR】

    UNICODE的功能是将一个字符转换成UNICODE编码，UNICHAR正好相反，和进制转换函数一样，对爱好汉化的我来说算是比较有用的。在用于数组元素处理时，Application.Unicode()和Application.Unichar()可以批量处理，能够大幅减少代码的行数。

示例代码：

Sub UniTest()

  arr = ["あ"&row(1:1000000)]　　'建立100万元素的数组

  arr =Application.Unicode(arr)    '仅能转换每个元素的第一个字符

  MsgBoxJoin(Array(arr(1, 1), arr(2, 1), arr(3, 1)), vbCrLf)

  arr =Application.Unichar(arr)

  MsgBoxJoin(Array(arr(1, 1), arr(2, 1), arr(3, 1)), vbCrLf)

End Sub

【补充】

还有一些工作表函数也可以对数组进行整体处理，但是因为有些功能存在重复或者实用性不高，所以不一一写示例了，有需要者可以自行测试：

Application.BahtText()：数字转泰语

Application.Dollar()：数字转金额（可以用TEXT代替）

Application.Fixed()：设置数字格式（可以用TEXT代替）

Application.NumberValue()：将含有数字的字符串转成数值，没有深入研究

Application.Roman()：数字转罗马字，最大支持3999

大灰狼1976

感谢超版大大关注以及加精！