PDA

View Full Version : Efficient coding with Loop



Kerry H
04-08-2019, 10:55 AM
Hi all,
I have the following code in a Sub:

For L = 1 To 5
B = XArray(16, L) ' True or False for Transpose
Sh = XArray(1, L) ' name of sheet to be copied/pasted
r1 = XArray(2, L) ' first row entire matrix (col in PREORDER)
r1a = XArray(3, L) ' first row of additions (col in PREORDER)
rL = XArray(4, L) ' last row of active matrix (col in PREORDER)
c1 = XArray(5, L) ' first col of whole matrix (row in PREORDER)
cL = XArray(6, L) ' last col of matrix (row in PREORDER)
c2 = XArray(7, L) ' col of Supply Code (row in PREORDER)
c3 = XArray(8, L) ' col retailer names (row in PREORDER)
c4 = XArray(9, L) ' col of Face Values (row in PREORDER)
c5 = XArray(10, L) ' col supplier discount (NA in PREORDER)
c6 = XArray(11, L) ' col member % (NA in PREORDER)
c7 = XArray(12, L) ' col initial active inv (NA in PREORDER, ORDER FORM)
c8 = XArray(13, L) ' col initial stored inv (NA in PREORDER, ORDER FORM)
c9 = XArray(14, L) ' col Min Inv (NA in PREORDER, ORDER FORM)

The Sub then goes on to make use of the parameters that have been read in from the array.
Each L in the loop represents a different sheet to which values are being copied or pasted.
As you may see from the comments, several parameters are NOT needed for the sheets "PREORDER" and "ORDER FORM".

Is it more efficient to simply allow the routine to assign unnecessary parameter values for c5, c6, ..... c9?
(And then just ignore them)
Or, use a loop within the main loop to branch around the unnecessary parameters?
In the small example shown it doesn't really matter much. Just trying to develop good habits.

Thanks very much.
Kerry

Bob Phillips
04-08-2019, 11:47 AM
Personally, I would read the values in every time. Adding code to ignore some in certain cases just adds to the complexity of the code, and make it less efficient (although this would be a very minor impact).

Kerry H
04-08-2019, 12:45 PM
Thanks, xld.
Is there a thank you button on this site?