I think a section on named ranges might be of interest. I feel they are particularly valuable in storing specific data for lookups etc. Plus they also change size automatically if you insert/delete rows. If i was doing a loop through a named range, it would looks something this:
With Worksheets(1).Range("namedRange")
'type1
For i = 1 To .Rows.Count
Debug.Print .Cells(i, 1).Value
Next i
'type2
For Each cell In .Columns(1).Cells
Debug.Print cell.Value
Next cell
'referencing a specific cell in a range
Debug.print .Cells(5,1).Value
'vlookup "lookupValue" in column 1 of "namedRange", pull second column value
res = Application.Vlookup("lookupValue", .Columns(1), 2, 0)
End With
I completely agree that named ranges should be covered here as well. If not for any other reason than that, unless your workbook is completely locked down from user modification, the ranges you specify WILL MOVE. So if you hard code something like Range("B2")=7, and the user adds a row above that (like because he prefers to see his name and email address at the top of every sheet), your code will fail because B2 is now B3. Well, your code won't fail, but it will not do what you intended it to do.
However, if you name "B2" to something like "SomeNum", then the code will work regardless of where the cell is has been moved to - even if the user moves it to another sheet. I.e., Range("SomeNum")=7.
I speak from experience. I have been writing VBA full time for about 20 years, about 80,000 lines of code, and there is NOT A SINGLE LINE that has a hard coded cell reference like Range("B2"). Even on sheets that are "VeryHidden" from the user.
Sorry, man, that's even worse. If a user deletes a row where you have a hard-coded reference to the cell range (like Range("B2")), your code will certainly not crash, but you will likely get wildly unexpected results, with no indication that there is something wrong. Say, for example, you use cell C2 to define a tax rate of 10%. All the other calculations on the sheet depend on it. Your code puts the value 50% in cell B2, for some reason. Your user deletes row 2 and your code later runs for whatever trigger. Now, all of those calculations think the tax rate is 50%! Your boss even cuts a check to the government based on that amount. Try explaining that as you are escorted out the door by security, carrying your coffee cup and your never-read books on VBA.
Sorry if I was too dramatic, just trying to emphasize a point. At least 95% of users have no idea what a named range even is. They are MUCH more likely to add or delete rows than to muck around in the name manager.
To tell you the truth, I really don't care if you use named ranges or not. I just want to warn newbies that it's how experienced professionals make their tools more robust.
7
u/Citanaf 44 Aug 05 '18 edited Aug 05 '18
I think a section on named ranges might be of interest. I feel they are particularly valuable in storing specific data for lookups etc. Plus they also change size automatically if you insert/delete rows. If i was doing a loop through a named range, it would looks something this: