CODEWITHSUNDEEP
excelvba
Shobhit
Shobhit

Reputation: 85

Can variant data type store fixed length string data type values in VBA?

As per the official Microsoft.com documentation:

A Variant is a special data type that can contain any kind of data except fixed-length String data. (Variant types now support user-defined types.) A Variant can also contain the special values Empty, Error, Nothing, and Null. You can determine how the data in a Variant is treated by using the VarType function or TypeName function.

I just need to understand why below code is working when variant cannot store fixed length strings?

Sub Test()

    Dim y As Variant
    Dim x As String * 10
    y = x
    MsgBox y

End Sub

Upvotes: 3

Views: 544

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The y = x casts the fixed length string into a normal string like x = 10 would cast that number into a string. Test it with y = x & "123456789012345" and you will see it is not fixed length anymore.

Sub Test()    
    Dim y As Variant
    Dim x As String * 10
    y = x
    MsgBox y    

    'but this works too
    y = x & "123456789012345"
    MsgBox y    
End Sub

That means x is a fixed length string but y is not fixed in length anymore. If you do not explicitly convert one type into another VBA implicitly casts them into the other type if possible (if not you get a type mismatch).

That means for example that you can push a number into a string without explicitly converting it:

Dim str As String
str = 12345

will cast the number 12345 into a string like str = CStr(12345).

Similar happens when you push a fixed length string into a variant. It implicitly casts into a non fixed length string.

Upvotes: 3

Vityata
Vityata

Reputation: 43575

When String * 10 is cast to Variant, the Variant becomes a string. Check the y in the Watch window:

Sub Test()

    Dim y As Variant
    Dim x As String * 10        
    x = "12345678900"
    y = x
    y = "12345678900"        
    Debug.Print y

End Sub

enter image description here

Upvotes: 2

Related Questions