CODEWITHSUNDEEP
vbaexcel
teepee
teepee

Reputation: 2714

Function will not return array when range contains only one value

I have a function meant to return an array which is created out of a single-column list of data. I have been using this function's return value essentially as a pseudo-global variable (LINENAMES_ARRAY) which I pass to many functions. Those functions than do checks on it such as If Len(Join(LINENAMES_ARRAY)) = 0 Then or go through items with For Each statements. Here is the code:

  Function LINENAMES_ARRAY() As Variant
  'returns an array of all items in the main sheet linenames column
      LINENAMES_ARRAY = Application.Transpose(MAIN.Range( _
      MAIN.Cells(MAIN_HEAD_COUNT + 1, MAIN_LINENAMES_COLUMN), _
      MAIN.Cells(LINENAMES_COUNT + 1, MAIN_LINENAMES_COLUMN)))
  End Function

I recently stumbled on one of those you-don't-see-it-till-you-see-it problems while using this workbook for a new project, where if the array happens to be only 1 element, everything fails. Apparently in that case, this returns a single value so Join() will fail For Each __ in LINENAMES_ARRAY will too. Why won't it treat this as a 1x1 array rather than a free value? I have started to mitigate the problem by rewriting functions where this is called, to check whether it is an array, then do some other procedure. Things like:

For j = 1 To LINENAMES_COUNT
    LINES_BOX.AddItem lineNames(j)
Next j

is changed to:

If Not IsArray(LINENAMES_ARRAY) Then
     myListBox.AddItem CStr(LINENAMES_ARRAY)
Else
    For j = 1 To LINENAMES_COUNT
       LINES_BOX.AddItem LINENAMES_ARRAY(j)
    Next j
End If

However this becomes messy and is adding many extra checks to my code that I would prefer to handle in the LINENAMES_ARRAY function. Is there a way to return a 1x1 array? Or any other workaround?

Upvotes: 3

Views: 1476

Answers (1)

user4039065
user4039065

Reputation:

An array can have a single element if you create it as a single element array and populate it in an array manner.

Option Explicit

Dim MAIN_HEAD_COUNT As Long
Dim LINENAMES_COUNT As Long
Dim MAIN_LINENAMES_COLUMN As Long
Dim MAIN As Worksheet

Sub stuff()
    Dim arr As Variant
    Set MAIN = Worksheets("Sheet1")
    MAIN_LINENAMES_COLUMN = 2
    MAIN_HEAD_COUNT = 2
    LINENAMES_COUNT = 2

    arr = LINENAMES_ARRAY()
    Debug.Print IsArray(arr)
    Debug.Print LBound(arr) & ":" & UBound(arr)
End Sub

Function LINENAMES_ARRAY() As Variant
    Dim a As Long, tmp() As Variant
    ReDim tmp(0 To LINENAMES_COUNT - MAIN_HEAD_COUNT)
    For a = 0 To LINENAMES_COUNT - MAIN_HEAD_COUNT
        tmp(a) = MAIN.Range(MAIN.Cells(MAIN_HEAD_COUNT + 1, MAIN_LINENAMES_COLUMN), _
                            MAIN.Cells(LINENAMES_COUNT + 1, MAIN_LINENAMES_COLUMN)).Cells(a).Value2
    Next a
    'returns an array of all items in the main sheet linenames column
    LINENAMES_ARRAY = tmp
End Function

Results from the VBE's Immediate window:

True
0:0

Upvotes: 6

Related Questions