CODEWITHSUNDEEP
vbaexcel
Pdfins
Pdfins

Reputation: 73

Excel VBA Public Dictionary object auto populating?

Have 2 Dictionary objects I'm using, declared Public bc I need them to persist outside each sub i use them in, but for some reason they're auto populating before I even load them up. Has anyone see this behavior?

Public itmsSpot As Dictionary, itmsFwd As Dictionary

Public sub runReport()
  Set itmsSpot = New Dictionary
  Set itmsFwd = New Dictionary

  GenData

End Sub

Public Sub GenData
...

GenData loops thru a sheet in my workbook and populates itmsSpot and/or itmsFwds depending on the data in the sheet, but for some reason it's adding values into one or both as soon as it steps into GenData before it even starts to loop thru the sheet.

I've tried Set itmsSpot = Nothing just before I call GenData, but it doesn't quite do the trick. Even if I wipe out the data in the sheet, it seems to autopopulate from the last time I ran it. Any thoughts on why this might be happening?

Thanks!

Upvotes: 1

Views: 253

Answers (1)

user4039065
user4039065

Reputation:

If you have instantiated and populated the public dictionary, there is no need to instantiate a second time for a second run. Setting the dict to a new dict is not the correct method of clearing the keys and items. Use .RemoveAll instead.

Public itmsSpot As Dictionary, itmsFwd As Dictionary

Public sub runReport()
    if itmsSpot is nothing then Set itmsSpot = New Dictionary
    if itmsFwd is nothing then Set itmsFwd = New Dictionary

    itmsSpot.removeall
    itmsFwd.removeall

    'set comparemode on empty dict if binary compare (default) is not desired
    itmsSpot.comparemode = vbtextcompare
    itmsFwd.comparemode = vbtextcompare

    GenData

End Sub

Public Sub GenData
...

Upvotes: 2

Related Questions