excel - 如何使用 VBA 将以 = 开头的字符串插入到单元格值中？

Question

我正在研究将各种类型的数据插入工作表(ws)的脚本。

Dim ws as Worksheet
Dim Index_Array(0 to 5) As Variant
Dim i as Integer

Set ws = ActiveSheet

Index_Array(0) = "This is some text."
Index_Array(1) = "This is also some text."
Index_Array(2) = "22004"
Index_Array(3) = 42
Index_Array(4) = 2.34657
Index_Array(5) = "=55"       'Yes, this should in fact be a string, not a formula or a number

For i = LBound(Index_Array) To UBound(Index_Array)
    ws.Cells(1, i + 1).Value = Index_Array(i)
Next i

问题是当我尝试插入字符串 =55进入单元格A5，它给了我

Run-time Error 1004: Application-defined or object-defined error.

除了这种情况，该脚本一切正常，我相信这是因为它试图使其成为一个公式。我不想强制一切都以 ' 开头字符，因为并非所有内容都是字符串。有没有一种简单的方法可以让 Excel 接受以等号开头的字符串作为单元格值？

Answer 1

在每个数组项前添加一个 ' 并使其成为 Excel UI 中的文本。

所以

Dim ws As Worksheet
Dim Index_Array(0 To 5) As Variant
Dim i As Integer

Set ws = ActiveSheet

Index_Array(0) = "This is some text."
Index_Array(1) = "This is also some text."
Index_Array(2) = "22004"
Index_Array(3) = 42
Index_Array(4) = 2.34657
Index_Array(5) = "=55"

For i = LBound(Index_Array) To UBound(Index_Array)
    ws.Cells(1, i + 1).value = "'" & Index_Array(i) ' add a "'" to make it a text value in excel UI
Next