CODEWITHSUNDEEP
vbaexcel
Ans
Ans

Reputation: 1234

Collection.Add: Wrong number of arguments or invalid property assignment

I have a sub that creates a Collection and adds Collections inside it. But I get an Wrong number of arguments or invalid property assignment error when adding a first collection in the loop:

Sub testState()
    Dim stateCopy As State
    Set stateCopy = New State
    stateCopy.setStateName="some name"
    stateCopy.storeBudgetWorkbooks
    stateCopy.storeBudgetDatas  'error on this line
End Sub

Sub storeBudgetDatas()  'inside a class named State
 ...
  Dim year As Integer
  Dim i As Integer
  i = 1
  For year = 2014 To 2017
      Set budgetWorkbook = 
          ExcelApp.Application.Workbooks.Open(budgetWorkbooks(i))
      MsgBox ("still here")  'this message appears

      allBudgetItems.Add getBudgetData(budgetWorkbook, year) 'this line is likely to cause problems


      MsgBox ("and here")  'this message doesn't appear
      budgetWorkbook.Close
      i = i + 1
  Next
End Sub

Function getBudgetData(budgetWorkbook As Workbook, year As Integer)
  ...
  Dim budgetItems As Collection
  Set budgetItems = getBudgetItems(year)
  ... 'setting attributes
  getBudgetData = budgetItems(year)
End Function

Function getBudgetItems(year As Integer)
  ...
  Dim resultCollection As Collection
  Set resultCollection = New Collection
  Dim itemCopy As Item
  Dim i As Integer
  For i = LBound(budgetItemNames) To UBound(budgetItemNames)
      Set itemCopy = New Item
      ... 'setting attributes
      resultCollection.Add itemCopy
  Next

  Set getBudgetItems = resultCollection
End Function

I'm not sure what's wrong here. getBudgetItems returns a collection. getBudgetData returns a collection as well. I tried adding/removing parenthesis but to no avail.

Upvotes: 0

Views: 1034

Answers (2)

robinCTS
robinCTS

Reputation: 5886

Since you haven't shown us all the relevant parts of your code, the best I can do is guess you're missing a Set:

Function getBudgetData(budgetWorkbook As Workbook, year As Integer)
  ...
  Dim budgetItems As Collection
  Set budgetItems = getBudgetItems(year)
  ... 'setting attributes
  Set getBudgetData = budgetItems(year) ' Need Set here
End Function

Upvotes: 1

Ans
Ans

Reputation: 1234

Figured it out. There should have been Set getBudgetData = budgetItems(year) instead of getBudgetData = budgetItems(year).

Upvotes: 1

Related Questions