Hi, vinwin06!
Let me try:
1) I absolutely dislike to change the main part of any code only because the worksheet, range or cell to which it was applicable changed, that's to say the code should take a sort of parameters instead of being modified; it's safer and more clear to say (and Debraj Roy'll be going to kill me) that a name rngHistory is used rather than a Range("C2:M6"), the first gives another entity to the people who read it.
2) Besides I neither like to be changing the named ranges definition each time that the data changes, so if it's possible I always define dynamic named ranges instead of fixed reference named ranges. I know its easier to understand that a range named rngHistory address is "C2:M6" than =OFFSET(DataTable,,2,,COLUMNS(DataTable)-2) (and even more if DataTable is defined as =OFFSET(Hoja1!$A$2,,,COUNTA(Hoja1!$A:$A)-1,COUNTA(Hoja1!$1:$1))), but you can add product rows or dates columns without doing anything; so I choose this last option.
3) In the code itself I could have written range rngH definition as Range("DataTable") but I prefer to define all constants (except 0, 1, space, "", or sometimes 8 if talking of bits, 256 if talking of bytes, ...) at the top of the procedure rather than reading a code with constants, which if I need to change them I'd have to do it every time they're referenced as opposite to only one with my method; it increases a bit the code size, but disk space it's cheap nowadays (remember that the panic for the effect Y2K before year 2000 arrived was originated by guys who decided in the earlier 60's -as my grandpa told me-. and no one ever was forced to change, to store dates in format YYMMDD instead of YYYYMMDD just to save 2 bytes per date).
4) I like to divide each procedure in the following structures:
a) non-executable code
- option statements (usually only explicit, but compare database..., always declare everything)
- public constants (only if there are)
- public declarations (idem)
- constants
- declarations
- api calls (only if exist)
b) executable code
- start
- process
- end
In such way all what I over spend in the non-executable code I gain it many times in the executable code, making it simpler to maintain, and here I don't care if simpler to read: if anyone else or myself tomorrow have to get hands dirty on it, I prefer to have it clean and accessible even if long, before than scrambled and difficult to follow but short.
5) Naming conventions are fundamental for readability even if it's easier to write R1 than rngH. I adopted this long time ago, and for greater projects a three prefix instead of one for all:
Variables: i(nteger), l(ong), v(ariant), d(ate), s(tring), n(single), b(boolean), e(double)... iNoOfRows
Constants: same as variables plus the prefix k... kiNoOfRows
Parameters: same as variables plus the prefix p... piNoOfRows
Global var o consts: same as previous plus the prefix g... giNoOfRows, gkiNoOfRows
Public var o consts: same as previous pulus the prefix p... pgiNoOfRows, pgkiNoOfRows
Objects: usually three letters, app for application, rng for range, obj for object, ctl for control, cmd for command button, opt for option button, chk for checkbox, lst for list, cbo for combo, lbl for label, txt for text box, img for image, hsb for horizontal scroll bar, vsb for vertical...
Subroutine: normal name, descriptive, and those called within and not shared, with a similar prefix as the original name, InitializeStuff, InitializeStuffInternal, InitializeStuffExternal...
Function: idem subroutine but with the prefix rules of variables
I'll always like to read pgkiX rather than X, because X doesn't tell me anything, neither object, or variable, or type, or... but pgkiX it comes with the pedigree: public global constant of type integer, and that helps even if it's easier to read X = X + 12 than pgkiX = pgkiX + kiMonthsOfDelay...
6) Writing the main code.
Start section prepares the environment for the process, defines ranges, sets objects, dimensions arrays, retrieves parameters, assigns variables.
Process section, does the job using what start prepared.
End section cleans the mess.
This is my personal style, there're a lot of conventions, techniques, methodologies, and... and... and... but they're all really beyond the scope of this topic. You could google them with keywords as best practices coding, conventions, and you'll be crowded with lecture for the next millenium.
One humble advice: what's cheaper today, will be expensive today, and always proportionally, the more cheaper, the more expensive, so if you ask me, spend today everything you can, and tomorrow go for a Carlsberg.
Which is what I'm gonna do now.
Hope it helps and good luck in your path thru coding.
Regards!
