You can also use it to write data back to a worksheet.
However, there is a workaround to overcome this array limit.
The following macro returns an array vertically to cell range A1: A65537 and Transpose is not even used.
In the following example, the code reads in data from a range, doubles it and then writes the variant array of values back to the worksheet: Sub test4() Dim v Data() As Variant Dim r As Integer Dim c As Integer v Data = Active Sheet.
'Declare, dimension and assign a string array with 3 elements Dim departments(2) As String departments(0) = "Engineering" departments(1) = "Finance" departments(2) = "Marketing" 'Declare an undimensioned string array and then dynamically assign with 'the results of a function that returns a string array Dim state Names() As String state Names = VBA.