VBA-Excel将“FALSE”字符串转换为“False”

This problem is driving me crazy! I have the following VBA Excel code:

这个问题让我抓狂！我有以下VBA Excel代码：

Sub Test()

Dim sTempVal As String

For i = 1 to 100
    sTempVal = ActiveSheet.Cells(i, 1).Value
    MsgBox sTempVal
Next i

As it is looping down the values in the first column, if it comes across the value FALSE or the value TRUE, it automatically reads it in as "False" or "True". Why is VBA changing the case automatically?? I really need it to retain the exact case of that value in the cell. I thought that by Dim'ing the sTempVal variable as a string, it would just read in the value in the cell - case and everything. Note that this ONLY applies to FALSE and TRUE. Any other value will be read in exactly as it appears in the cell.

因为它循环第一列中的值，如果它遇到值FALSE或值TRUE，它会自动将其读作“False”或“True”。为什么VBA会自动更改案例？我真的需要它来保留单元格中该值的确切大小写。我认为通过将sTempVal变量作为字符串进行Dim'ing，它只会读取单元格中的值 - 大小写和所有内容。请注意，这仅适用于FALSE和TRUE。将以与单元格中显示的完全相同的方式读取任何其他值。

2 个解决方案

#1

Sub Test()

Dim sTempVal As String

For i = 1 to 100
    sTempVal = ActiveSheet.Cells(i, 1).Text
    MsgBox sTempVal
Next i

.Text is "What you see is what you get."

.Text是“你看到的就是你得到的东西。”

EDIT#1:

编辑＃1：

When you use .Value VBA reads the item as a Boolean and, when converting to a string, it uses its own convention for that conversion.

For example, if you pick a cell and enter the formula:

使用时，.Value VBA将项目作为布尔值读取，并且在转换为字符串时，它会使用自己的约定进行转换。例如，如果您选择一个单元格并输入公式：

=1=1

and run this tiny macro:

并运行这个小宏：

Sub WhatIsTruth()
    MsgBox ActiveCell.Value
End Sub

you will see:

你会看见：

Using .Text avoids this problem.

使用.Text可以避免这个问题。

#2

Range.Value returns a Variant, not a String. You're assigning it to a String variable, so it is implicitly cast.

Range.Value返回Variant，而不是String。您将它分配给String变量，因此它是隐式转换的。

This line...

这条线......

sTempVal = ActiveSheet.Cells(i, 1).Value

...is equivalent to:

......相当于：

sTempVal = CStr(ActiveSheet.Cells(i, 1).Value)

VBA's cast from Boolean to String is proper cased - Excel's cast is upper cased. For example:

从布尔到字符串的VBA转换是正确的 - Excel的演员阵容是大写的。例如：

Debug.Print CStr(0 = 0) 'Prints "True"

If you want Excel's string representation of what is in the cell, you can use the Range.Text property instead:

如果您希望Excel的字符串表示单元格中的内容，则可以使用Range.Text属性：

sTempVal = ActiveSheet.Cells(i, 1).Text

#1

Sub Test()

Dim sTempVal As String

For i = 1 to 100
    sTempVal = ActiveSheet.Cells(i, 1).Text
    MsgBox sTempVal
Next i