VBA-Excel Converting "FALSE" string to "False"

Question

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

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.

Answer 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."

EDIT#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:

=1=1

and run this tiny macro:

Sub WhatIsTruth()
    MsgBox ActiveCell.Value
End Sub

you will see:

Using .Text avoids this problem.

Answer 2

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

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:

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:

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