Home>

Hello. This is a question in VBA.
It seems that the result of pasting on the sheet is different between the array created by split and the array created by array. (See ①②)
Even if you check the contents of the array in the watch list, because it is the same "string" type, the array created with either instruction will appear to be "same".

Why is the pasting result different between ① and ②?

[① When creating an array with split]

From the code below, column B in the first row is stored in numeric type (right-justified).
However,B column of 2 row names is stored in character type (left justified).

Sub test ()
    Dim arr As Variant
        arr = "Oh, 2"
    Dim ww
        ww = Split (arr, ",")
    Cells (1, 1) = ww (0)
    Cells (1, 2) = ww (1)
    Range (Cells (2, 1), Cells (2, UBound (ww) + 1)) = ww
End Sub

【②Create an array with array】

From the code below, column B in the first row is stored in numeric type (right-justified).
B row name B column is also stored in numeric type (right justified).

Sub test2 ()
    Dim arr As Variant
        arr = Array ("Oh ah", "2")
    Cells (1, 1) = arr (0)
    Cells (1, 2) = arr (1)
    Range (Cells (2, 1), Cells (2, UBound (arr) + 1)) = arr
End Sub

Thank you for this time.

  • Answer # 1

      

    Even if you check the contents of the array, it ’s the same"string"type

    The array is certainly a string, but
    The type of the array itself is a little different.
    ① is Variant/String, ② is Variant/Variant.
    I don't know if this is the cause, but this is the only difference.

  • Answer # 2

    In the first example, it can be divided into "quote ah" and "quote 2".
    I think it will be a character type because there is a space at the beginning.

    arr ="Oh, 2"
    Please try it with