Hi,
I’m one of those people who got quite good at VBA without any formal training in programming. I can accomplish just about any task (intelligent spiders to hunt down info, databases, regular expressions, and all sorts of clever things), even though occasionally I use other people’s code I don’t understand at all. People on this site have been hugely helpful and a large part of what I know is because of people going out of their way to answer a question in a better way than they had to or I expected them to. What’s always bugged me though is that I’ve learnt everything on a need to know basis without any overarching concepts guiding me. Obviously I’ve realised big concepts as I’ve gone along and written them into new code.
Here’s my question(s). I’ve been trying to write all my code in the most re-useable lego-like way that I can. Basically every procedure is a generic function. I forget bits and pieces of why a function works and like to have a name that summarises the logic as best as possible. In my head, it naturally follows that code shouldn’t be repeated. The thinking being that any mistake only has to be corrected in one place. The consequence is that every generic function makes calls to lots of others in a very dependent way. Big mistakes have big knock on effects if lots of other code was written after.
This is obviously hard to do but I like the idea of having a single (Personal) workbook that has anything I commonly need in it. If I needed to write code for someone else, I could see what functions I needed and have another function check what it calls and collect them all into a txt file. That’s the theory, but I find myself often getting confused. What tips do you guys have to overall principles? Some concepts I already use are:
• Enums are great for clarifying code. When you pass an argument, it’s easier to understand when one says *IsRed* than *True*
• Breaking Module names into a file like structure: String_File_GetExtension, String_File_GetNameFromFileSpec
• I search for VBA code using InfoRapid Search & Replace. Do you guys save text files for everything or what? How do you search for long forgotten code?
• When variables go nuts, rename them, run, rename back
• Mind mapping software
I know it’s a rant, but I’m curious and never knew how to ask such a broad question. Any advice or tips?
I’m one of those people who got quite good at VBA without any formal training in programming. I can accomplish just about any task (intelligent spiders to hunt down info, databases, regular expressions, and all sorts of clever things), even though occasionally I use other people’s code I don’t understand at all. People on this site have been hugely helpful and a large part of what I know is because of people going out of their way to answer a question in a better way than they had to or I expected them to. What’s always bugged me though is that I’ve learnt everything on a need to know basis without any overarching concepts guiding me. Obviously I’ve realised big concepts as I’ve gone along and written them into new code.
Here’s my question(s). I’ve been trying to write all my code in the most re-useable lego-like way that I can. Basically every procedure is a generic function. I forget bits and pieces of why a function works and like to have a name that summarises the logic as best as possible. In my head, it naturally follows that code shouldn’t be repeated. The thinking being that any mistake only has to be corrected in one place. The consequence is that every generic function makes calls to lots of others in a very dependent way. Big mistakes have big knock on effects if lots of other code was written after.
This is obviously hard to do but I like the idea of having a single (Personal) workbook that has anything I commonly need in it. If I needed to write code for someone else, I could see what functions I needed and have another function check what it calls and collect them all into a txt file. That’s the theory, but I find myself often getting confused. What tips do you guys have to overall principles? Some concepts I already use are:
• Enums are great for clarifying code. When you pass an argument, it’s easier to understand when one says *IsRed* than *True*
• Breaking Module names into a file like structure: String_File_GetExtension, String_File_GetNameFromFileSpec
• I search for VBA code using InfoRapid Search & Replace. Do you guys save text files for everything or what? How do you search for long forgotten code?
• When variables go nuts, rename them, run, rename back
• Mind mapping software
I know it’s a rant, but I’m curious and never knew how to ask such a broad question. Any advice or tips?
