The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Did this approach make sense? (VBA, but general programming question)
schattenjaeger
Registered User regular
Registered User regular
I'm the company's resident VBA hacker, in that I can make Office applications bend to my will through sheer force of will (as opposed to clean, easily understandable self-documenting code). I'm not a programmer by trade or education, but I've managed to use it to help me in countless endeavors, mostly simpler scripting stuff in Excel. I assume the actual code I've written would boil the blood of a person who actually does this for a living. However, I've been independently studying (well..study is a strong word, you know what I mean) Java and C++ for my own fun and amusement and learning how much I really do suck. Well, an opportunity came up to try and usefully apply some of my new learnings.
This is VBA in Project, I know there's probably far less of a userbase than VBA with Excel, but it's the same idea, if not simpler. Instead of a workbook/worksheet object, you have a project object. Instead of a range object, you have task objects. Each task has properties like task.startdate, task.finishdate, etc. Pretty simple. The most interesting part is that each task "depends" on predecessors and successors, so it's really a logic network (I guess an...acyclic directed graph?) Anyways, I have a Project file and we had added some custom fields with numbers in them, and I needed to basically step backwards through the schedule (which in theory could be 10,000 tasks with each task having dozens of predecessors) and trace my way task by task through the logic based on those values. Then when I reach the beginning, I had to retrace my steps up the path I just came down, until I reached a task with a new predecessor, then turn back around and go back down this new path, based on the custom values, until I can go no more turn back around, retrace my new steps, etc. I have to mark all these distinct paths. Anyways, don't worry too much about the algorithm; I'm withholding information and it wasn't really even as simple as I just made it out to be.
I was able to get it mostly working, but it was pretty brittle, and the code was a hilarious mess that probably no human could follow. Including me, who now had to make it less brittle. I basically dumped it and started from scratch.
One thing that was making my code sorta messy was that there were several "properties" of the tasks I needed to keep track of beyond what Project offers in the Task object. My original attempt at this had been with a multidimensional array, which was a mess as I had to manually keep track of what "column" in the array corresponded with what property. Also, the properties are mixed data types, so...I don't even remember how I dealt with that. In addition, in the initialization step I had to keep re-dimensioning the array since I didn't know how many tasks there are a priori. Not that I'm good enough to worry about this kind of thing, but that's pretty slow, right? Especially in the 10,000 task hypothetical case.
Now, I've since learned about enum types (which VBA has) which probably could work to help the readability. Well, what I ended up doing instead was making a Class (which VBA does, but there's no inheritance) for basically a custom "Task" which contained my desired properties. However, in addition I wanted to use some of the built-in Task methods that are non-trivial that I didn't want to reproduce, so I made one of the class fields a task object, and added a method that returned the associated task object. That way I could do either (these aren't the names I used): MyTask.MyProperty, or MyTask.getProjTask.ProjTaskMethod. I'm hoping this is faster than the array since in the initialization step I'm basically adding a bunch of properties to memory that I will need there no matter how I do it, and then just basically pointing to the associated task object...hopefully that's what happens. I definitely prefer the way it reads.
I feel like, in a sick way, I kind of faked making an extension of the Task class? Is that....ok? That alone significantly helped me read what I was doing and got rid of lots of clutter. Also, I could add the MyTask objects to a Collection that was a single object holding all of the MyTasks. That was extremely helpful because I had broken the algorithm into more functions to help with readability (you don't want to see the original cut), so instead of passing several arguments to several functions, I could pass the single collection (by reference).
In addition, I had 2 or 3 variables that were independent of the individual tasks. Kinda top-level "state" variables (for example, I have to keep track of how many paths overall I've gone through). A lot of the functions needed to know and/or alter most of these variables, and the data types were mixed, so I made another class with a single instance called CurrentState, with those items as properties, initialized them at the start, and then passed that object (by reference) around.
In the end, all my functions are now only taking 1 or 2 arguments, no single function is impossible to follow, and it is crystal clear what variable I'm modifying when I do. I was able to get it to work on a rather large schedule (fast, but I'm not sure speed was ever going to be a problem) so I'm feeling pretty good that I did something better, I'm just curious what many things could improve it? I think I helped myself with the classes, but I hope I wasn't driven to use them just because I had learned about them.
This is VBA in Project, I know there's probably far less of a userbase than VBA with Excel, but it's the same idea, if not simpler. Instead of a workbook/worksheet object, you have a project object. Instead of a range object, you have task objects. Each task has properties like task.startdate, task.finishdate, etc. Pretty simple. The most interesting part is that each task "depends" on predecessors and successors, so it's really a logic network (I guess an...acyclic directed graph?) Anyways, I have a Project file and we had added some custom fields with numbers in them, and I needed to basically step backwards through the schedule (which in theory could be 10,000 tasks with each task having dozens of predecessors) and trace my way task by task through the logic based on those values. Then when I reach the beginning, I had to retrace my steps up the path I just came down, until I reached a task with a new predecessor, then turn back around and go back down this new path, based on the custom values, until I can go no more turn back around, retrace my new steps, etc. I have to mark all these distinct paths. Anyways, don't worry too much about the algorithm; I'm withholding information and it wasn't really even as simple as I just made it out to be.
I was able to get it mostly working, but it was pretty brittle, and the code was a hilarious mess that probably no human could follow. Including me, who now had to make it less brittle. I basically dumped it and started from scratch.
One thing that was making my code sorta messy was that there were several "properties" of the tasks I needed to keep track of beyond what Project offers in the Task object. My original attempt at this had been with a multidimensional array, which was a mess as I had to manually keep track of what "column" in the array corresponded with what property. Also, the properties are mixed data types, so...I don't even remember how I dealt with that. In addition, in the initialization step I had to keep re-dimensioning the array since I didn't know how many tasks there are a priori. Not that I'm good enough to worry about this kind of thing, but that's pretty slow, right? Especially in the 10,000 task hypothetical case.
Now, I've since learned about enum types (which VBA has) which probably could work to help the readability. Well, what I ended up doing instead was making a Class (which VBA does, but there's no inheritance) for basically a custom "Task" which contained my desired properties. However, in addition I wanted to use some of the built-in Task methods that are non-trivial that I didn't want to reproduce, so I made one of the class fields a task object, and added a method that returned the associated task object. That way I could do either (these aren't the names I used): MyTask.MyProperty, or MyTask.getProjTask.ProjTaskMethod. I'm hoping this is faster than the array since in the initialization step I'm basically adding a bunch of properties to memory that I will need there no matter how I do it, and then just basically pointing to the associated task object...hopefully that's what happens. I definitely prefer the way it reads.
I feel like, in a sick way, I kind of faked making an extension of the Task class? Is that....ok? That alone significantly helped me read what I was doing and got rid of lots of clutter. Also, I could add the MyTask objects to a Collection that was a single object holding all of the MyTasks. That was extremely helpful because I had broken the algorithm into more functions to help with readability (you don't want to see the original cut), so instead of passing several arguments to several functions, I could pass the single collection (by reference).
In addition, I had 2 or 3 variables that were independent of the individual tasks. Kinda top-level "state" variables (for example, I have to keep track of how many paths overall I've gone through). A lot of the functions needed to know and/or alter most of these variables, and the data types were mixed, so I made another class with a single instance called CurrentState, with those items as properties, initialized them at the start, and then passed that object (by reference) around.
In the end, all my functions are now only taking 1 or 2 arguments, no single function is impossible to follow, and it is crystal clear what variable I'm modifying when I do. I was able to get it to work on a rather large schedule (fast, but I'm not sure speed was ever going to be a problem) so I'm feeling pretty good that I did something better, I'm just curious what many things could improve it? I think I helped myself with the classes, but I hope I wasn't driven to use them just because I had learned about them.
0
Posts
Like you, I am the resident VBA guru at my job. I was at my last job, too. I basically devise small scale, ad hoc solutions to problems as they come up. I've worked on broader scale projects too. I mostly work with Excel and Access with some PowerPoint and Word and Outlook. I've used Project but never coded for it. Still, it's basically the same concept, just a different library of objects and properties.
The way I've reconciled whether or not the way I've done something is "ok" is by answering these questions in order of importance:
1) Is it functional? Does it solve whatever problem it is supposed to solve?
2) Does it run efficiently or does it take an hour to perform a task that can take 75%+ less time with a different approach.
3) Does it sufficiently handle a majority of potential errors, runtime or otherwise (such as iterating over janky data or a dumb end user)?
4) Is the code readable?
Now, I have OCD and I'm a syntax stickler and I dabble in creative writing so I personally try to make my code readable and easy to trace through, but in a pinch FUCK THAT and if your company is relying on you to write robust and universally readable VBA solutions fuck them too.
From what you've said here, unless your code runs unnecessarily slow, I think your code is more than "ok."
I mean, it's good to be readable but if they are asking you to resolve some problem or produce some metric in a short period of time through VBA then their and your last concern should be readability.
In my opinion.
And there's nothing wrong with creating a more robust version of a class MSFT provided.
hah, amen to that. I should stress the extra mile here is really for my own enrichment. This is sort of a proof of concept thing, so even my ugly mess of a solution was probably fine, but this is something I really do want to test on a large schedule, which requires more confidence that everything was really doing what I thought it was doing.