VBA return dynamic array and assign to variable

Question

Return dynamic array from function VBA got me part of the way on this problem. I realized I should know size prior to invoking the function.

Function GetHeadersFromRange(DataRange As Range, Size As Integer) As Variant
    Dim Column As Integer
    Dim Headers As Variant
    ReDim Headers(0 To Size)

    For Column = 1 To DataRange.Columns.Count
        Headers(Column) = DataRange(1, Column).Value
    Next
    GetHeadersFromRange = Headers
End Function 

Sub TestGetHeadersFromRange()
    Application.DisplayAlerts = False
    Set wb = ThisWorkbook
    Set TestSheet = wb.Sheets.Add()

    TestSheet.Range("A1").Value = "my_header"
    TestSheet.Range("A2").Value = "val"

    Dim DataRange As Range: Set DataRange = TestSheet.Range("A1:A2")
    Dim Size As Integer: Size = DataRange.Columns.Count
    Dim Result As Variant

    ' Gets type mismatch
    Set Result = GetHeadersFromRange(DataRange, Size)
End Sub

Not entirely sure what to do here. I need to use this function in multiple places which is why it is a function in the first place.

Edit: Clarify problem Set Result = GetHeadersFromRange(...) gets a type mismatch.

Because your UDF returns a 1-D array of values (not ranges) , you should not use Set — Gary's Student
– Gary's Student, Commented Mar 14, 2019 at 23:39
You've just declared Result as Variant and then you use Set Result!? Could you explain what will you do with Result in the Future? — VBasic2008
– VBasic2008, Commented Mar 14, 2019 at 23:43
Headers is zero based but you don't adjust Size. If there are five columns then Headers is 0 to 5 for a total of six array elements. You ignore Headers(0). — user11198948
– user11198948, Commented Mar 14, 2019 at 23:44
Always helps to explain exactly what the problem is. Is it just the "Type mismatch" ? — Tim Williams
– Tim Williams, Commented Mar 15, 2019 at 0:23

VBasic2008 · Accepted Answer · 2019-03-15 10:11:32Z

1

Header Function

Improvement

Your error occurs because you are using Set (used for objects) on an array.
A more efficient (faster) way than looping through a range is looping through an array.
When you copy a range to a variant (possibly array), if the range contains one cell, the variant will contain one value only. But if the range contains multiple cells, it will be an array, whose size is returned with UBound. Therefore there is no need for a Size argument.
IsArray is used to determine if a variant is an array. In our case we can check if the number of columns (elements) is greater than 1 instead.

Option Explicit

Function GetHeadersFromRange(DataRange As Range) As Variant

    Dim vntR As Variant   ' Range Variant
    Dim vntH As Variant   ' Header Array
    Dim Noe As Long       ' Number of Elements
    Dim j As Long         ' Range Array Column Counter,
                          ' Header Array Element Counter

    With DataRange
        ' Calculate Number of Elements.
        Noe = .Columns.Count
        ' Calculate Header Range.
        ' Copy Header Range to Range Variant.
        vntR = .Resize(1, Noe)
        ' Note: Range Variant (vntR) is a 2D 1-based 1-row array only if
        '       DataRange contains more than one column. Otherwise it is
        '       a variant containing one value.
    End With

    '' Check if Range Variant is an array.
    'If IsArray(vntR) Then
    ' Check if Number of Elements is greater than 1.
    If Noe > 1 Then
        ' Resize 1D 0-based Header Array to number of columns (2) in Range
        ' Array minus 1 (0-based).
        ReDim vntH(Noe - 1)
        ' Loop through columns of Range Array.
        For j = 1 To Noe
            ' Write value at first row (1) and current column (j) of Range
            ' Array to current element (j-1) of Header Array.
            vntH(j - 1) = vntR(1, j)
        Next
      Else
        ' Resize 1D 0-based Header Array to one element only (0).
        ReDim vntH(0)
        ' Write Range Variant value to only element of Header Array.
        vntH(0) = vntR
    End If

    GetHeadersFromRange = vntH

End Function


Sub TestGetHeadersFromRange()

    Dim TestSheet As Worksheet  ' Source Worksheet
    Dim DataRange As Range      ' Data Range
    Dim Result As Variant       ' Result Variant (possibly Array)
    Dim i As Long               ' Result Array Element Counter

    ' Add a new worksheet (Source Worksheet).
    ' Create a reference to the newly added Source Worksheet.
    Set TestSheet = ThisWorkbook.Sheets.Add()

    ' In Source Worksheet
    With TestSheet
        ' Add some values.
        .Range("A1").Value = "my_header"
        .Range("A2").Value = "val"
        .Range("B1").Value = "my_header2"
        .Range("B2").Value = "val2"
    End With

' Test 1:
    Debug.Print "Test1:"
    ' Create a reference to DataRange.
    Set DataRange = TestSheet.Range("A1:A2")
    ' Write Data Range to 1D 0-based Result Array.
    Result = GetHeadersFromRange(DataRange)
    ' Loop through elements of Result Array.
    For i = 0 To UBound(Result)
        ' Write current element of Result Array to Immediate window.
        Debug.Print Result(i)
    Next

' Test 2:
    Debug.Print "Test2:"
    ' Create a reference to DataRange.
    Set DataRange = TestSheet.Range("A1:B2")
    ' Write Data Range to 1D 0-based Result Variant.
    Result = GetHeadersFromRange(DataRange)
    ' Loop through elements of Result Array.
    For i = 0 To UBound(Result)
        ' Write current element of Result Array to Immediate window.
        Debug.Print Result(i)
    Next


End Sub

edited Mar 15, 2019 at 10:11

answered Mar 15, 2019 at 0:55

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Adam Over a year ago

I'll give this a shot.

user11198948 Over a year ago

If you are trying to get a zero-based 1-D array from one row and many columns you need to double up application.transpose. If you want a one-based 2-D array you don't need to transpose anything.

Adam Over a year ago

Can you explain the difference? I am using this in conjunction w/ VBA-Web and need to send a JSON request that takes an array as an argument.

T.M. Over a year ago

If you are happy with a 1-dim 1-based array you can shorten the function code to a one-liner: GetHeadersFromRange = Application.Transpose(Application.Transpose(Application.Index(DataRange.Value, 1, 0)))

Adam Over a year ago

This appeared to get the array as desired.

|

Collectives™ on Stack Overflow

VBA return dynamic array and assign to variable

1 Answer 1

Header Function

Improvement

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Header Function

Improvement

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related