如何快速将Excel表格列内的文本内容转换为可执行公式

问题说明 #

我有一个绑定了SQL数据库的Excel工作簿，库中多个字段存储的是可直接在Excel中运行的公式，典型示例如下：
=HYPERLINK("MAILTO:example@example.com","example@example.com")
但数据同步到Excel后，这些公式会被默认识别为普通文本，无法直接生效。

旧版实现方案（性能缺陷） #

很长一段时间我都采用逐单元格遍历赋值的方式完成文本到公式的转换，代码如下：

dim cell as range

With Blad2.ListObjects("example")
    for each cell in .ListColumns("Email").DataBodyRange
         cell.formula = cell.text
    next cell
End With

但随着SQL库中数据量持续增长，该方案的执行效率越来越低，大数据量场景下需要5~10秒甚至更久才能跑完。

优化方案（性能提升显著） #

我最终找到了效率更高的实现方式，同等数据量下仅需几毫秒即可完成转换，代码如下：

dim Data as variant

Data = Blad2.ListObjects("example").ListColumns("Email").DataBodyRange
Blad2.ListObjects("example").ListColumns("Email").DataBodyRange.formula = Data

该方案通过将整列数据一次性读取到Variant变量中，再批量写入单元格的公式属性，省去了逐单元格操作的大量开销，性能提升幅度非常大。

内容的提问来源于stack exchange，提问作者Ramon Zwirs