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.

Gary's Student · Accepted Answer · 2016-09-30 16:44:52Z

10

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.

edited Sep 30, 2016 at 16:44

answered Sep 30, 2016 at 14:48

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave Over a year ago

Can you add a quick line explaining for the OP why this is happening with .Value as they did specifically ask that question?

Dave Over a year ago

Ta for that. Just helps the OP understand better :)

D. Orcutt Over a year ago

Thank you so much! This did the trick. I can't believe I've been using VBA for like five years on some pretty complex stuff, and I've never known about the Cells.Text property. Thanks again!

Comintern · Accepted Answer · 2016-09-30 14:56:37Z

4

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

answered Sep 30, 2016 at 14:56

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related