Hi all. I have a pretty vague/broad question on which I am looking for learned advice. Being a mechanical engineer, my computer science skills are limited, and much of object oriented programming is trial-and-error for me. Regardless, I've managed to put together a pretty promising set of modules and userforms in Excel/VBA that are beginning to look like they'll work for what I need.
I am trying to do an in-house document tracking and cataloging system. I've got zero budget, and that's why I went to VBA in Excel. At the root of the project is a need to know things about individual files, and to be able to search on one or more characteristics or tags for those files, both system created and user created. For example, I might like to find 3D drawing files created on a certain date (system created info), or those that apply to a particular manufacturer (user created info).
I think that this is a situation in which some sort of array would be useful. For each file, I could make a new entry in a multi-dimensional array, with each entry representing a certain bit of information. Like this:
I'm not entirely sure that the Dim and ReDim is the proper way to declare the array, but it's the hack that I've gotten to work thus far. I'm not sure how to make the array persistent, though. I'd prefer not to have to re-build it every time the module is run, so I suppose I could also dump the array, value by value, into a spreadsheet/database file, and re-make it each time from there. That's a problem for further down the road.
My question is this:
Is an array the right way to store this data? I'm worried that if the data changes, say, if files are added to a folder that's being tracked and cataloged, then the old files' positions will be shifted and thus need to be managed very carefully. File 18 might now be file 19 because a new file with a higher alphabetically sorting placement was added. I have vague, fuzzy memories from my CS100 class that makes me wonder if there's not a better data structure to use. Could I make on Object (or Class??? or Function???, I'm not sure which might be right) that has Properties which can be defined and given a desired value? Maybe some pseudo code will illustrate my thinking:
It seems, though, that I'd still need some sort of array functionality in order to have one collection of properties for each individual design file. Like this, conceptually:
So, the bottom line is that I'm not very well versed on what the best approach is, but I think I could do a brute force array + database file system that would get there. I just wonder if there's a better, more programming-savvy method that I'm not aware of, but would really be the best fit for my project.
I hope this has all made some amount of sense to people, and that someone can maybe offer some advice or point me in the direction of a useful resource. I really feel like this is a pretty obvious/common problem, and that I'm just not at the computer science guru level necessary to solve it elegantly.
Bookmarks