Pfthb

I am not sure why VB is doing this since I don't have a lot of experience. Basically, the first time I run my script the output is what I expect, but the second time it looks completely wrong.

Here's the code:

'define queue type
Dim Queue As New Collection


'define job list
Dim JobList As New Collection

'loop parameters
Dim rng As Range, rng2 As Range
Dim rng_w As Range, rng_w2 As Range

Dim TimeRange As Double




'loop over list and get jobs at frequency
Dim i As Double
Dim j As Integer



Sub Generate_Queue()
ThisWorkbook.Sheets("AGV capacity").Activate
Set rng = Range("L6:L7")
Set rng2 = Range("M6:M7")

'fill JobList

Dim Frequencies As Range
Set Frequencies = rng
Dim Jobs As Range
Set Jobs = rng2
Dim dividend

TimeRange = ThisWorkbook.Sheets("AGV capacity").Range("O6") * 3600



For i = 1 To rng.Count
 Set tmp = New Transport
 tmp.Frequency = (TimeRange / Frequencies(i))
 tmp.SourceDest = Jobs(i)
 JobList.Add tmp
Next i

i = 1

'while loop over time range (e.g. 24 hours)
While i < TimeRange
 'for loop over number of entries in job list
 For j = 1 To JobList.Count
 'check if i is mod Frequency, meaning it should be added to the queue
 If ((i Mod JobList(j).Frequency) = 0) Then
 Set tmp = New Transport
 dividend = (i / JobList(j).Frequency)

 tmp.Frequency = ((dividend * JobList(j).Frequency) / 3600)
 tmp.SourceDest = JobList(j).SourceDest
 Queue.Add tmp
 End If
 Next j
i = i + 1
Wend

ThisWorkbook.Sheets("order queue").Activate
Set rng_w = Range("A2", "A" & Queue.Count)
Set rng_w2 = Range("B2", "B" & Queue.Count)

i = 1

For i = 1 To 1000
 rng_w(i).Value = ""
 rng_w2(i).Value = ""

Next i

For i = 1 To Queue.Count
 rng_w(i).Value = Queue(i).Frequency
 rng_w2(i).Value = Queue(i).SourceDest

Next i 
End Sub

I am working on two different Excel sheets, but the data that's read stays the same over the two runs. When I watch the different collections, they change with the same data set and sometimes are even created twice! Coming from other languages, I expected them to be deleted each run, but that does not seem to be the case. How can I "reset" these object?

The answer is to not use the New keyword when you Dim the variable, especially when you define it outside of a procedure, since you don't directly control when it will be instantiated.

If you need Queue and JobList to be available more widely then they're defined in the correct place, but when you execute Generate_Queue, that's probably when you should actually create the variables.

So, at the top of your module:

Dim Queue As Collection
Dim JobList As Collection

And within Generate_Queue:

Set Queue = New Collection
Set JobList = New Collection

So every time Generate_Queue is called, those two variables are created fresh (if that's what you want to happen).