Type conversion, single value converts Variant to String

**virgilio** · 01-31-2020, 11:56 AM

Why does a variant with a single value type convert to string?

I have filtered a list in another sub function, and then I want to parse out the values in column A. It works fine if there are multiple values, but when the variant "temp" has only one item, it becomes a string and I get Type Errors when it gets to the For/Next loop.

I tried to force it back to a variant with the red code, but it still comes out as a string!

Private Function assignTime()

   Dim Lrow As Long
   Dim temp As Variant
   Dim Value As Variant
   
   ReDim temp(0)
    
   With Worksheets("Sheet2") 'Find last cell
     Lrow = .range("A" & Rows.Count).End(xlUp).Row
     temp = .range("A3:A" & Lrow).Value 'temp contains all values in A
   End With

   If TypeName(temp) = "String" Then
      temp = CVar(temp)
   End If
   
   For Each Value In temp
     'Parse a bunch of stuff
   next value

end function

**CK76** · 01-31-2020, 12:01 PM

Variant is variable type. So variant temp can hold any given type.

Once temp is assigned some value/object type of variable held is implicitly set to that of the value/object.

What is the purpose of using CVar()?

If variable is declared as variant then you can use it below without issue. If it's declared as specific type, it can only hold value/object of that type.
Ex:

Sub Demo()
Dim temp As Variant

temp = 1234
Debug.Print TypeName(temp)
temp = "ABCD"
Debug.Print TypeName(temp)

Set temp = Range("A1")
Debug.Print TypeName(temp)
End Sub

EDIT: Personally, I've never seen code or algorithm which required use of CVAR().

**Andy Pope** · 01-31-2020, 12:06 PM

rather than it's type check whether it's a single item or not.

    If IsArray(temp) Then
        For Each Value In temp
        'Parse a bunch of stuff
        Next Value
    Else
       'Parse a bunch of stuff
        Value = temp
    End If

To save duplicating your 'parse a bunch of stuff' put that code in another routine

**CK76** · 01-31-2020, 12:13 PM

Oh, if you need to convert string to single item array.

   If TypeName(temp) = "String" Then
      temp = Array(temp)
   End If

**virgilio** · 01-31-2020, 12:21 PM

Thank you,

I'm new to VBA and I guess I didn't fully understand what a Variant is. Andy Pope's solution worked perfectly for me. Thank you all.

Edit: Thank you for the suggestion to convert to array, I think that was what I was trying to accomplish with CVar()

Type conversion, single value converts Variant to String

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Type conversion, single value converts Variant to String

Re: Type conversion, single value converts Variant to String

Re: Type conversion, single value converts Variant to String

Re: Type conversion, single value converts Variant to String

Re: Type conversion, single value converts Variant to String

Thread Information

Users Browsing this Thread

Similar Threads

Single string into a variant issue

[SOLVED] maximum length of a string / variant type

Need help with formula that converts table into columns and additional conversion

Problem with Type conversion- String to Int

convert textbox value from string to either single or double numeric data type

Array problem: Key words-Variant Array, single-element, type mismatch error

Array problem: Key words-Variant Array, single-element, type mismatch error

Bookmarks

Bookmarks

Posting Permissions