Calling a function that returns an ArrayList

Question

I am mostly experienced in other programming languages like java and C++. VBA is still very new to me and I hardly know what I'm doing. I attempted to return an arraylist from a function and I keep getting error 5 - invalid procedure call or argument.

Dim months As ArrayList
Dim temp As Integer

Set months = New ArrayList

'Copy and Paste State and Quarter Specific Data
Sheets("The Data (2)").Select
ActiveSheet.Range("$A:$T").AutoFilter Field:=6, Criteria1:=stateName
months = getMonths(Year, Quarter)
ActiveSheet.Range("$A:$T").AutoFilter Field:=17, Criteria1:=months.Item(0), Operator:=xlOr, Criteria2:=months.Item(1), Operator:=xlOr, Criteria3:=months.Item(2)
Range("$A$1:$$T$1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("State Rate Planning Template.xlsm").Activate
Sheets(3).Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select

I receive the error at "months = getMonths(Year, Quarter)". Year and Quarter are strings declared above the code I have here.

Here is the function getMonths:

Function getMonths(Year As String, Quarter As String) As ArrayList

    Dim i As Integer
    Dim month As String
    Dim monthList As ArrayList
    
    Set monthList = New ArrayList
    
    If (StrComp(Quarter, "1", compare) = 0) Then
        For i = 1 To 3
            month = Year & "-0" & i
            monthList.Add month
        Next i
    
    ElseIf (StrComp(Quarter, "2", compare) = 0) Then
        For i = 4 To 6
            month = Year & "-0" & i
            monthList.Add month
        Next i
    
    ElseIf (StrComp(Quarter, "3", compare) = 0) Then
        For i = 7 To 9
            month = Year & "-0" & i
            monthList.Add month
        Next i
    
    ElseIf (StrComp(Quarter, "4", compare) = 0) Then
        For i = 10 To 12
            month = Year & "-" & i
            monthList.Add month
        Next i
    
    End If
    
    Set getMonths = monthList

End Function

Any help would be appreciated for a VBA newbie :)

`ArrayList` is not a data type known to VBA. Without looking at the rest of your code, start with `Dim months As Variant`. Note that you don't need to `Select` anything. Range("$A$1:$$T$1").Select: Range(Selection, Selection.End(xlDown)).Select: Selection.Copy has the effect of `Range("$A$1:$$T$1").Copy` - and $$T$1 is a syntax error. — Variatus, Jul 10 '20 at 00:48
I assume you added a reference to mscorlib.dll. Even then, the code does not compile. Add Option Explicit at the top of each module/class, then Debug/Compile. Correct your code and update your question. — Excelosaurus, Jul 10 '20 at 00:51

Super Symmetry · Answer 1 · 2020-07-10T01:41:57.173

Change the error line to

Set months = getMonths(Year, Quarter)

and you don't need the line

Set months = New ArrayList

months holds a reference to an object (using c++ and java terms) and to set that reference you need to use VBA's Set keyword.

Edit:

And if you use @Variatus's getMonths() function (which returns VBA's own Array I am pretty sure you can simplify your filter like this:

ActiveSheet.Range("$A:$T").AutoFilter Field:=17, Criteria1:=months

This is not tested and I stand to be corrected as the array might need to be a litteral one. But it doesn't harm to try.

score 1 · Answer 2 · answered Jul 10 '20 at 01:26

Please try this code.

Private Sub Test()
    ' 066
    
    Dim Months()    As String
    Dim Temp        As Integer
    
    Months = getMonths(2020, 4)
    For Temp = 1 To 3
        Debug.Print Months(Temp)
    Next Temp
End Sub

Function getMonths(ByVal Year As Integer, _
                   ByVal Quarter As Integer) As String()
    ' 066

    Dim Fun(1 To 3) As String               ' function return array
    Dim i           As Integer
    
    For i = 1 To 3
        Fun(i) = Format(((Quarter - 1) * 3) + i, CStr(Year) & "-00")
    Next i
    getMonths = Fun
End Function

score 1 · Answer 3 · answered Jul 10 '20 at 02:04

@Super Symmetry's answer is exactly what you need to keep going.

Others who might stumble upon this question should know that to be able to directly work with the ArrayList type from VBA (i.e. in early binding), one has to add a reference to mscorlib.dll from Tools / References / Browse, then browsing to the proper .NET Framework folder (e.g. C:\Windows\Microsoft.NET\Framework\v4.#.#####) and choosing file mscorlib.tlb. Late binding would let your macros run on computers with different versions of the .NET framework (YMMV) at the expense of Intellisense.

You mentioned you were new to VBA, so here is some additional information.

Option Explicit statement

Use Option Explicit at the top of all your modules and class modules. You can configure the VBA editor to do this for you automatically by checking Tools / Options / Editor / Require variable declaration. You can then use Debug / Compile to quickly find issues.

How to avoid using select in Excel VBA

For robust and clean code, avoid using the select method. If you record macros, let it only be for discovery purposes; clean them up with the techniques given in this popular answer.

Finally, I could not help but shorten your getMonths function. Here goes, with a test sub:

Option Explicit

Function getMonths(iYear As Integer, iQuarter As Integer) As ArrayList
    Dim monthList As ArrayList
    Dim i As Byte
    Dim item As String
    
    Set monthList = New ArrayList
    
    'Assumption: iYear and iQuarter are both within valid ranges.
    For i = (iQuarter - 1) * 3 + 1 To (iQuarter - 1) * 3 + 3
        item = CStr(iYear) & "-" & Format(i, "00")
        monthList.Add item
    Next
    
    Set getMonths = monthList
End Function

Public Sub Test()
    Dim months As ArrayList
    Dim iYear As Integer
    Dim iQuarter As Integer
    Dim monthItem As Variant
    
    iYear = 2020
    For iQuarter = 1 To 4
        Set months = getMonths(iYear, iQuarter)
        For Each monthItem In months
            Debug.Print monthItem
        Next
    Next
End Sub

Calling a function that returns an ArrayList

3 Answers3