如何使用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接受以等号开头的字符串作为单元格值？

- SandPiper

---

因为Excel将其视为公式。如果您想要第一个字符为=，则需要添加撇号，或者将单元格格式设置为文本。 - JohnyL

在你的for循环中，你可以检查字符串是否以=开头，并添加一个撇号。这将是一个简单的if语句，使用Left(string, 1)来检查=。 - Alex P

如果我在单元格中插入那个撇号，那只会影响Excel显示该值的方式，还是它实际上会影响其他尝试使用它的东西，例如字符串连接？ - SandPiper

Answer 1

在Excel用户界面中，将每个数组项前添加一个'并将其转换为文本。

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

- DisplayName

---

在任何字符串值前添加 ' 解决了该问题，我还验证了尝试从单元格读取值会返回您预期的值，而不需要这个前导 '。作为额外的好处，如果您的字符串有一个有意的前导撇号，这也会解决相同的问题。 - SandPiper

Answer 2

我认为正确的方法是从第一个点开始将您的值转换为字符串。
第一种方法：

Index_Array(5) = "'=55"

或者

Index_Array(5) = cstr("'=55")

如果您无法在定义数据时更改数据，并且只想在以 = 开头的数据中发生这种情况，请使用带有 left(array(i),1) = "=" 的 if，并在其前面添加 "'"：

For i = LBound(Index_Array) To UBound(Index_Array)
    if left(array(i),1) = "=" then 
        ws.Cells(1, i + 1).value= "'"& array(i)
    else 
        ws.Cells(1, i + 1).value = array(i)
    end if
next i

敬礼，
M

- Mahhdy

---

CStr函数中的值需要用双引号括起来，但是 CStr("'" & Index_Array(5)) 可以正常工作。 - SandPiper

是的，那是个打字错误，但无论如何，我的建议是改变值，而不是改变将其写入单元格的方式。;-) - Mahhdy

Answer 3

常规格式 vs 文本格式

当单元格中使用常规格式时：

For i = LBound(Index_Array) To UBound(Index_Array)
    If i = 5 Then
      ws.Cells(1, i + 1).NumberFormat = "@"
      ws.Cells(1, i + 1).Value = Index_Array(i)
     Else
      ws.Cells(1, i + 1).Value = "'" & Index_Array(i)
    End If
Next

- VBasic2008

---

我认为依赖于数组中变量位置的条件是编码的一种非常错误的方式！我假设上面的代码只是一个例子，因此我们必须提供更常见的解决方案。此外，OP希望它作为字符串值，Numberformat只会改变其外观，而不是值。 - Mahhdy