Variable Data Type

[kittronald]

How can you tell the data type of a variable ?

The defined name "Test" has a Refers to: field of =SUM(A1,B1).

1) In the example below, what would the data type for "v" be ?

v = Application.Evaluate(ActiveWorkbook.Names("Test"))

2) For a ComboBox containing text values, what would the data type for
"x" be for the currently selected value ?




- Ronald K.

 

[Martin Brown]

How can you tell the data type of a variable ?

The defined name "Test" has a Refers to: field of =SUM(A1,B1).

1) In the example below, what would the data type for "v" be ?

v = Application.Evaluate(ActiveWorkbook.Names("Test"))

Depends on whether =SUM(A1,B1) gives a valid answer or not.

Success will be a Double or failure will be an Error
(there might be other possibilities)

Simple clip to test

Sub test()
x = 1
y = "hello"
Z = Application.Evaluate("=SUM(A1..B2)")

Debug.Print x, TypeName(x)
Debug.Print y, TypeName(y)
Debug.Print Z, TypeName(Z)
End Sub

2) For a ComboBox containing text values, what would the data type for
"x" be for the currently selected value ?

I think either a Long internally or a Double in the linked cell
depending on exactly how you ask the question. Do the experiment to
check it to be sure.

Regards,
Martin Brown

 

[Clif McIrvin]

Depends on whether =SUM(A1,B1) gives a valid answer or not.

Success will be a Double or failure will be an Error
(there might be other possibilities)

Simple clip to test

Sub test()
x = 1
y = "hello"
Z = Application.Evaluate("=SUM(A1..B2)")

Debug.Print x, TypeName(x)
Debug.Print y, TypeName(y)
Debug.Print Z, TypeName(Z)
End Sub


I think either a Long internally or a Double in the linked cell
depending on exactly how you ask the question. Do the experiment to
check it to be sure.

Regards,
Martin Brown

I have on occasion used typename() against a range.value to test for
error conditions -- that is one advantage of using the variant type
instead of specific typing. If memory serves, the iserror() function can
be useful when working with variants and formula results.

 

[kittronald]

Clif,

Thanks for the help.



- Ronald K.

 

[kittronald]

Martin,

Using Debug.Print helped a lot.

The problem I was running into was that the Refers to: field had an IF
statement (i.e., =IF(SUM(A1,B1)>3,SUM(A1,B1),"Error").

So the result could be a number or a text value.

Is "As Variant" the appropriate data type for this type of situation ?

Thanks again for the help.



- Ronald K.