Based on String return Array in Access 2010 vba

Question

I've been working on this for two days and thrashing around and here's where I am now.

Private Function SetColumns(sRptVer As String, iColumns() As Integer)

If sRptVer = "Q1" Then
    iColumns(1) = 5 '<- Subscript out of range error here Hovering shows _
                    '"iColumns(1)=<Subscript out of range>"
    iColumns(2) = 6
    iColumns(3) = 7
    iColumns(4) = 17
End If

If sRptVer = "Q2" Then
    iColumns(1) = 5
    iColumns(2) = 6
    iColumns(3) = 7
    iColumns(4) = 8
    iColumns(5) = 9
    iColumns(6) = 10
    iColumns(7) = 17
End If

SetColumns = iColumns()

End Function

Private Sub Test2()

Dim iColValue() As Integer
Dim sRptVer As String
Dim iColumns() As Integer

sRptVer = "Q1"

iColValue() = SetColumns(sRptVer, iColumns())

For i = 1 To 10

    Debug.Print iColValue(i)
Next i

End Sub

The goal is to be able to be able to pass a string designating the quarter and return an array that will serve to set the columns I will iterate over to get values out of an Excel spreadsheet. (I'm pulling data from Excel into Access). I've tried starting with iColumns(0). No difference. NOTE: Access vba does not like to set arrays like so iColumns()={1,2,3,4}. It balks at the "{}". Access vba doesn't seem to like "ReDim" either, I get a compile error: Syntax error when I "redim icolumns() as Integer" in the SetColumns function. Thanks in advance.

Change Dim iColValue() As Integer to ReDim iColValue(10) as Integer(), then Dim iColumns() As Integer to ReDim iColumns(10) as Integer and then add as Integer() to the end of function definition. That should get you started — user2140173

mwolfe02 mwolfe02 · Accepted Answer · 2014-10-07T16:01:41

You don't need two separate arrays in your calling procedure to do what you want. Try this instead:

Private Function SetColumns(sRptVer As String) As Integer()
    Dim iColumns() As Integer
    Select Case sRptVer
    Case "Q1"
        ReDim iColumns(1 To 4)
        iColumns(1) = 5
        iColumns(2) = 6
        iColumns(3) = 7
        iColumns(4) = 17
    Case "Q2"
        ReDim iColumns(1 To 7)
        iColumns(1) = 5
        iColumns(2) = 6
        iColumns(3) = 7
        iColumns(4) = 8
        iColumns(5) = 9
        iColumns(6) = 10
        iColumns(7) = 17
    End Select
    SetColumns = iColumns
End Function

Private Sub Test2()
    Dim sRptVer As String
    Dim iColValue() As Integer

    sRptVer = "Q1"

    iColValue() = SetColumns(sRptVer)
    Dim i As Integer
    For i = LBound(iColValue) To UBound(iColValue)
        Debug.Print iColValue(i)
    Next i

End Sub

I made a few other changes:

I used ReDim to dynamically size the array based on the parameter passed to SetColumns.
I passed both lower and upper bounds to the array in the ReDim statement. Arrays in VBA are zero-bound by default*, so ReDim iColumns(4) is actually equivalent to ReDim iColumns(0 To 4).
Because we no longer know the size of the array ahead of time, I use the LBound() and UBound() functions to walk the array.
I changed your two If statements to a Select...Case statement. Based on your limited sample code that you provided this seemed appropriate. If your actual code is more complex, it may not be.

*NOTE: Unfortunately, you can change the starting array base in VBA using the Option Base statement. Please don't do this. Ever. You will only confuse yourself and others. If you want a specific array to start from 1 then be explicit when you Dim or ReDim the array as I showed in my example.

Based on String return Array in Access 2010 vba

1 Answers