CODEWITHSUNDEEP
vbaexcel
Quantum_Entanglement
Quantum_Entanglement

Reputation: 1633

Passing collections of objects as parameters in vba

I am completely new to VBA and I would appreciate some help with something that should be trivial. I have the following code:

Sub sub1()
Dim buys As Collection
Dim sells As Collection
Set buys = New Collection
Set sells = New Collection


handleBuy rowCounter, buys, sells

End Sub




Sub handleBuy(ByVal rowNum As Integer, ByRef listBuys As Collection, ByRef listSells As Collection)
'do something here with the collections

End Sub

The above collections contain objects instantiated by a class I define. When I try to run the above code I get a runtime error 424 "Object Required" The error occurs where the call to the handleBuy takes place. What am I missing here? Any help is greatly appreciated.

Upvotes: 0

Views: 5992

Answers (2)

chris neilsen
chris neilsen

Reputation: 53146

The error occurs because since rowCounter is not Dim'ed as Integer (and in the absence of Option Explicit) is of the default type Variant. Which is incompatable with the declared Sub handleBuy(ByVal rowNum As Integer

Solution: use Option Explict - this will help prevent this type of error And declare Dim rowCounter As Integer

BTW, you should use Long rather than Integer

Upvotes: 1

glh
glh

Reputation: 4972

2 options:

  1. Define the rowCounter variable; or
  2. Remove the Option Explicit

1 is preferred.

Upvotes: 1

Related Questions