Class module / multidimensional array?

New to class modules....not sure I really understand them properly.

What I'm trying to do is create a list of engineers, log the dates each has worked and produce a count of jobs per day to calculate an average with.

So....what I was thinking of was something like an array of Engineers(1 to 200) as string and two other variables, one being WorkDay(1 to 10000) date and JobDay(1 to 10000) as integer

However, a co-worker suggested using a class module instead:

Please Login or Register  to view this content.

Now, I've managed to get it to create multiple instances of the class:

Please Login or Register  to view this content.

And I can read them back in sequential order:

Please Login or Register  to view this content.

Now comes the complete bonehead question:

How do I reference an individual instance of the class in order to assign values to the other variables?
Like.....if code is scanning through a data set, finds the engineer name, searches the collection for a matching engineer name......how does it assign the date worked to WorkDay(n) of that particular instance?

Or am I barking up the wrong tree completely and this can be done easily with a multidimensional array (also new to me).


Google has only managed to get me this far....

Thanks Andy, still not getting it though I'm afraid.

I get the indexing - need that to access the array within the class. How do I access the indexed array JobDay(n) for one particular engineer, ie one specific instance of the class?


Say I've got 10 instances of the class, each with a different engineer.name
Each instance has got multiple entries in the array JobDay(n) with a corresponding value in the WorkDay(n) array.

What I need to do is get the next engineers name from the pre-existing dataset held on a worksheet, search through all the instances of Engineers until I find the one with Engineer.Name = whatever and then search through the JobDay(n) array for that instance to see if there's already an entry matching the date worked from the dataset.....if there is, add 1 to the WorkDay(n) value and if not, add the date worked to the next empty value in the JobDay(n) array and stick a 1 in the same WorkDay(n).

I suspect a collection/dictionary approach may be more useful:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Though to be honest, I'd use a pivot table

Originally Posted by Andy Pope

Not following your explanation 100%.

What I mean is, when I'm searching through WorkDay(n) to find a matching date, how to I ensure I'm searching through the correct instance of WorkDay(n) and not the WorkDay(n) array of another engineer?

This is what I've come up with:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

However, I'm getting an overflow error trying to add the very first value here:

Please Login or Register  to view this content.

Doesn't mine do that?

I'm still trying to decipher yours.... :-)

Think I've cracked it.....I had the Class module definitions completely screwed up. Should be:

Please Login or Register  to view this content.

With mine, unless I've missed something (proably )

class cEngineer:

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

Though this would be faster for a lot of data:

Please Login or Register  to view this content.

But I reckon a pivot table would be even quicker

I bow to your superior knowledge....but since I don't understand pivot tables either I'll stick with what works and move on to the next thing I don't understand.