Why does the last message box return FALSE when testing a cell for a string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

Try
MsgBox (LCase(TypeName(ActiveCell.Value)) = "string")

or of course simply change "string" to "String"

For this kind of test I would use

MsgBox VarType(ActiveCell.Value) = vbString

Regards,
Peter T


"ArthurJ" wrote in message
...
Why does the last message box return FALSE when testing a cell for a
string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

Hi,

Try this. Note a few less parenthesis

Sub Test()
'The active cell contains the string "ABC"
MsgBox ActiveCell.Value 'Displays 'ABC'
MsgBox TypeName(ActiveCell.Value) 'Displays 'String'
MsgBox TypeName(ActiveCell.Value) = TypeName("String") 'Displays 'FALSE'
End Sub

Mike

"ArthurJ" wrote:

Why does the last message box return FALSE when testing a cell for a string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

As others have pointed out, you needed to use "String", not "string".
Whenever you do a logical test against a String constant (text within quote
marks), you must always be wary of case sensitivity. Some functions (like
InStr or Replace) have optional arguments whereby you can make the
comparison case insensitive; but others (like TypeName) do not.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

Try this. Note a few less parenthesis

Sub Test()
'The active cell contains the string "ABC"
MsgBox ActiveCell.Value 'Displays 'ABC'
MsgBox TypeName(ActiveCell.Value) 'Displays 'String'
MsgBox TypeName(ActiveCell.Value) = TypeName("String") 'Displays
'FALSE'
End Sub

Mike

"ArthurJ" wrote:

Why does the last message box return FALSE when testing a cell for a
string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

Along similar lines, to avoid case sensitivity with string comparisons could
head the module -

Option Compare Text

In general though best not to use that without specific reason.

Regards,
Peter T

"Rick Rothstein" wrote in message
...
As others have pointed out, you needed to use "String", not "string".
Whenever you do a logical test against a String constant (text within
quote marks), you must always be wary of case sensitivity. Some functions
(like InStr or Replace) have optional arguments whereby you can make the
comparison case insensitive; but others (like TypeName) do not.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

Try this. Note a few less parenthesis

Sub Test()
'The active cell contains the string "ABC"
MsgBox ActiveCell.Value 'Displays 'ABC'
MsgBox TypeName(ActiveCell.Value) 'Displays 'String'
MsgBox TypeName(ActiveCell.Value) = TypeName("String") 'Displays
'FALSE'
End Sub

Mike

"ArthurJ" wrote:

Why does the last message box return FALSE when testing a cell for a
string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

"Peter T" wrote:

Try
MsgBox (LCase(TypeName(ActiveCell.Value)) = "string")

or of course simply change "string" to "String"

For this kind of test I would use

MsgBox VarType(ActiveCell.Value) = vbString

Regards,
Peter T

Thanks for the suggestion to use the VarType function. Unlike IsNumeric it identifies a string that looks like a number (ie, with a leading apostrophe). VarType is exactly what I needed.

Art

When comparing strings, I usually use StrComp rather than the "="
operator. With StrComp, you can specify whether the comparison is
case-sensitive, regardless of the Option Compare setting of the
module. E.g.,

If StrComp(VarName, "AbCd", vbTextCompare) = 0 Then
' strings match
Else
' string do not match
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Sun, 12 Oct 2008 15:36:00 +0100, "Peter T" <peter_t@discussions
wrote:

Along similar lines, to avoid case sensitivity with string comparisons could
head the module -

Option Compare Text

In general though best not to use that without specific reason.

Regards,
Peter T

"Rick Rothstein" wrote in message
...
As others have pointed out, you needed to use "String", not "string".
Whenever you do a logical test against a String constant (text within
quote marks), you must always be wary of case sensitivity. Some functions
(like InStr or Replace) have optional arguments whereby you can make the
comparison case insensitive; but others (like TypeName) do not.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

Try this. Note a few less parenthesis

Sub Test()
'The active cell contains the string "ABC"
MsgBox ActiveCell.Value 'Displays 'ABC'
MsgBox TypeName(ActiveCell.Value) 'Displays 'String'
MsgBox TypeName(ActiveCell.Value) = TypeName("String") 'Displays
'FALSE'
End Sub

Mike

"ArthurJ" wrote:

Why does the last message box return FALSE when testing a cell for a
string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

The only thing I don't like about using StrComp is it uses 0 for equality.
Of course, I understand why... it makes perfect sense when you consider it
uses +1 for greater than and -1 for less than... but my mind always equates
0 with False in logical comparisons and so, when checking for string
equality, testing StrComp to 0 (a False type value) just seems so wrong to
me.

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
When comparing strings, I usually use StrComp rather than the "="
operator. With StrComp, you can specify whether the comparison is
case-sensitive, regardless of the Option Compare setting of the
module. E.g.,

If StrComp(VarName, "AbCd", vbTextCompare) = 0 Then
' strings match
Else
' string do not match
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Sun, 12 Oct 2008 15:36:00 +0100, "Peter T" <peter_t@discussions
wrote:

Along similar lines, to avoid case sensitivity with string comparisons
could
head the module -

Option Compare Text

In general though best not to use that without specific reason.

Regards,
Peter T

"Rick Rothstein" wrote in message
.. .
As others have pointed out, you needed to use "String", not "string".
Whenever you do a logical test against a String constant (text within
quote marks), you must always be wary of case sensitivity. Some
functions
(like InStr or Replace) have optional arguments whereby you can make the
comparison case insensitive; but others (like TypeName) do not.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

Try this. Note a few less parenthesis

Sub Test()
'The active cell contains the string "ABC"
MsgBox ActiveCell.Value 'Displays 'ABC'
MsgBox TypeName(ActiveCell.Value) 'Displays 'String'
MsgBox TypeName(ActiveCell.Value) = TypeName("String") 'Displays
'FALSE'
End Sub

Mike

"ArthurJ" wrote:

Why does the last message box return FALSE when testing a cell for a
string?
I have the same problem checking for numeric values such as Double.

Sub Test()
'The active cell contains the string "ABC"
MsgBox (ActiveCell.Value) 'Displays 'ABC'
MsgBox (TypeName(ActiveCell.Value)) 'Displays 'String'
MsgBox (TypeName(ActiveCell.Value) = "string") 'Displays 'FALSE'
End Sub

The only thing I don't like about using StrComp is it uses 0 for equality.

No argument from me on that score.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

On Sun, 12 Oct 2008 11:54:00 -0400, "Rick Rothstein"
wrote:

The only thing I don't like about using StrComp is it uses 0 for equality.
Of course, I understand why... it makes perfect sense when you consider it
uses +1 for greater than and -1 for less than... but my mind always equates
0 with False in logical comparisons and so, when checking for string
equality, testing StrComp to 0 (a False type value) just seems so wrong to
me.