VBA: creating sheet array using Range.Formula = "={values}" but failed

[gifariz]

I want to create array in sheet by VBA. I am trying using kind of Range.Formula = "={values}" but it becomes "=@{values}" which doesn't produce desired array (please see attached image).
Any solution please? TIA.

Here is sample code:

Sub xx()
    Dim x, y
    x = Array(3, 4)
    y = Join(x, ",")
    Range("A1").Formula = "={" & Join(x, ",") & "}"
End Sub

 

[James006]

Hi,
Have you tested:

Sub xx()
    Dim x, y
    x = Array(3, 4)
    y = Join(x, ",")
    Range("A1") = "(" & y & ")"
End Sub

 

[Alex Blakenburg]

Try changing Formula to Formula2

 

[gifariz]

Try changing Formula to Formula2

Great, thank you!
Just knew they added Formula2 for dynamic arrays-enabled excel.

 

[Alex Blakenburg]

You're welcome. Glad we could help.
PS: I think adding the link was a good idea.