if statement and implicit conditional returns type mismatch

[Brian Miller]

I'm using vba within excel and based upon whether a cell contains data, any
kind of data, I want to perform an action. If the cell has string data the
implicit conditional returns a type mismatch and if the cell has numeric data
the conditional behaves as expected. The result can be duplicated and
generalized and produces similar results in VBA and in VBScript. If Not
IsNull() doesn't seem to work either. Any suggestions would be appreciated.
Thanks,
Brian

This is a VBA script that produces the type mismatch:
Sub Main()
subTestValues
Worksheets(1).Range("A1").Value = 1
Worksheets(1).Range("A5").Value = 5
subTestValues
Worksheets(1).Range("A1").Value = "string"
Worksheets(1).Range("A5").Value = 5
subTestValues
End Sub
Sub subTestValues()
If Range("A1") Or Range("A5") Then 'Using the Cells property produces
the same result.
subMsg ("Either A1 or A5 has a value.")
Else
subMsg ("Neither A1 or A5 has a value.")
End If
End Sub
Sub subMsg(strText As String)
MsgBox strText
End Sub

 

[Steve Rindsberg]

Does this represent the problem in a more simplified way?

Sub SimplifiedCase()
Dim Value1 As String
Dim Value2 As Double

Value1 = "String"
Value2 = 1.234

If Value1 Or Value2 Then
MsgBox "It works"
End If
End Sub

This errors with a type mismatch on the OR comparison (I suppose because VB will
quietly convert numerics of various sorts to longs/booleans but won't deal with
strings that way.

For the same reason:

Cbool("String") will error
Cbool(1.234) won't

I think it should work if you do:

If Val(Range("A1")) Or Val(Range("A5")) Then

 

[Brian Miller]

Thanks Steve. I'll try that. Any ideas about equivalent syntax for
VBScript. The Val conversion is missing in VBScript.

Brian

 

[Steve Rindsberg]

Thanks Steve. I'll try that. Any ideas about equivalent syntax for
VBScript. The Val conversion is missing in VBScript.

I haven't had time to learn VBScript, much though I'd like to. As I recall,
strongly typed variables aren't its long suit.

I wonder whether you could work something out using VarType or TypeName to
determine whether a given variable holds a string or numeric before doing any
comparisons?