|
04-14-2020, 07:25 PM
|
 |
Expert
|
|
Join Date: May 2013
Location: USA
Posts: 700
|
|
Yeah, sorry about the complications. Part of the problem is that I've been doing this for a while, so some little extras that I used to skip ("too much trouble for this one little task") I eventually got into the habit of doing just because so often I later wished I had from the beginning. Like loading the table of formulae into a collection before applying them: When I was starting out no doubt I would have figure "Oh, there are only two or three different formulae, I may as well hard-code them into the program". Now, with experience, I agree with you that it's better to be flexible right from the start and leave room to add more as the need comes up—and now I just load the table into a table without asking myself whether I should, just because so often I need to in the end anyway.
I also am in the habit of specifying things like the workbook and worksheet:
Code:
Set owb = ThisWorkbook
Set ows = owb.ActiveSheet
Even though Excel often assumes the parents of the objects I refer to, and usually its defaults are what I wanted, every so often they aren't and I have to spend some minutes trying to figure out why the program isn't doing what I meant it to. Eventually I just got in the habit of writing it all out every time. You don't have to...until you've been programming for years :-).
And after a while I wrote some subroutines to save routine coding. I have a FetchWorksheet routine, for example, a function that I hand a workbook and a sheet name and it returns the worksheet—but if the worksheet doesn't exist (like if I forgot to create it, or spelled it wrong) the program prints a message instead of just bombing. An Exists function that returns True or False for a particular item in a collection. And so on. I tried not to burden you with that kind of junk; you can figure out for yourself when you want to write it. But the habits make my programs look more complicated than perhaps they need to be for your purposes.
|