[Excel] Using ranges instead of hard coding

[briancarlson]

Hello all,

I've been on LinkedIn recently and some tech guys said they never hire coders who reference ranges by ("A1:A20") instead of by name ("Revenue"), etc. I know how to name a range by using the input box just to the left of the formula bar, but how do you reference the above example in VBA?

I can see the benefits of using named ranges instead of hard coding them for ranges which never change, but for ranges whose length is unpredictable, I can't see how this would work. For example:

Count

Sheets("Cost Allocation - Detail").Select
    
    Range("B13").Select
        Dim c As Integer
        Do Until ActiveCell = ""
            ActiveCell.Offset(1, 0).Select
            y = y + 1
        Loop

Example A:  
'Zero Users [Count Range(AD1:ADy)] y = random variable between 5 and 5000
Range("AD1").Select
ActiveCell.FormulaR1C1 = "=IFERROR(COUNT(R[1]C:R" & 1 + y & "C), """")"

Example B:
'Populate Formula [Auto fill formula for Range(AA2:ADy)]
Sheets("F(5)").Select
Range("AA2:AD2").Select
Selection.AutoFill Destination:=Range("AA2:AD" & 1 + y)

Example C:      
'Account  [Copy Range("D13:Dz")] z = random variable between 5 and 5000
Sheets("Cost Allocation - Detail").Select
Range("D13:D" & 12 + z).Select
Selection.Copy
    
Sheets("Account").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Further, I have about 2.5k lines of code for this one project that I just finished. In order to Dim each range specific to its purpose I'll need to Dim about 250 different ranges by name. Which may take up more time than it saves.

I greatly appreciate your thoughts and input.

brian

[westconn1]

you can name ranges within code

i would think you have more issues in your code, than whether to use named ranges

[briancarlson]

Self taught VBA in the last three months, this forum has been the source of 80% of what I know. I have a degree in accounting, and not in computer science. The quality of the code may be low in the view of a professional, but I've reduced the time it takes for business processes at my company by about 90% (given they had terrible processes to begin with).

Is there a good forum you could direct me to which could explain how to use named ranges effectively within code?

Thank you

[opus]

Have you ever tried the makro-recorder?

[westconn1]

just use the range name in place of cells

set myrange = range("mynamedrange")

i do not necessarily agree that named ranges are so much better
my dig at your code is that as far as possible you should avoid selecting anything or working with selection or active sheet or active anything else, much better to always fully qualify ranges, so you know that the values come from the correct sheet in the correct workbook, and not rely on the one you want to be the active one or default

Example B:
'Populate Formula [Auto fill formula for Range(AA2:ADy)]

    Sheets("F(5)").Range("AA2:AD2").AutoFill Destination:= Sheets("F(5)").Range("AA2:AD" & 1 + y)

often the best method, is to set sheet objects or range objects where required

set mysht = workbooks("book1").Sheets("F(5)")
 mysht.Range("AA2:AD2").AutoFill Destination:= mysht.Range("AA2:AD" & 1 + y)

as well as avoiding ambiguity, the code will run much quicker not moving the selection point
as opus points out the macro recorder is a great way to generate some sample code, but it will always work with the selection object, much better to use the sample code and apply it to your sheet and range objects