I am looking to create a collection of functions for structural calculations. The idea is to have a module or similar I can import into an Excel workbook, and start using the functions in VBA for what I might need.
Something I'd need to do initially is to define some standard variables with data constants. For example a collection of pipe data: commercial names, inner diameter, outer diameter, weight per meter, etc. It's a constant library: industry pipes are all the same, and don't really change much over time.
What would be the best way to do this in VBA? Initially I thought a regular old public array or dictionary might do it. With a dictionary I could do something along the line of:
pipe_diameter = pipes("HSS100x100x3")
But there's the problem of initializing. I would have my pipe data in code, inside a module, but I'd have to remember to call an init_pipes sub at the start of every piece of code, in order to load the data into the arrays and make it available.
Plus I guess I'd have to do a dictionary for every single pipe parameter: pipes_diameter, pipes_unitweight, pipes_sectionarea, etc. Doesn't sound very fun to code.
So moving up, I guess I could make a pipes class. And then do something like:
Dim pipedata as pipes
pipe_diameter = pipes.innerdiam("HSS100x100x3")
And all the relevant data would be stored inside the class module. Load the module, create your object, and you've got the data available. I'd have to give some thought as to how to organize the class to make it practical to retrieve and maybe update the data, but it looks like the most promising option, so far.
Am I right, or is there an easier way that this might be done that doesn't involve custom classes in VBA?
The best way to share a library workbook is a built in function of Excel called "Personal Workbook". This is a workbook stored in your XLSTART folder.
There is a machine level XLSTART folder that will open for any user, this requires an admin to install the workbook. For regular users you can just share the workbook to the other users personal workbook area.
The pattern is like this:
C:\Users\$USERNAME\AppData\Local\Microsoft\Excel\XLStart
Now that you know how to share a personal workbook that will open automatically when Excel starts, you can design your solution.
I recommend putting the pipe data in to a table in a sheet in the workbook. Then you can pull the data in to whatever VBA functions you will expose to the user.
This way, once the code is stable, you can change the underlying data in the sheet without modifying the code. It's always advised to keep your data and code separate.
A dictionary is only good for Key/Value pairs. You want to make custom class objects for the pipe that has all the fields you need to fill from the spreadsheet. Then you could dynamically pull the data from the sheet in to a collection of pipe objects.