Range or Row and Column Indices in VBA

Question

I would like to know what the pros and cons are for storing a range variable instead of four integers or long variables (representing the first and last row and column) to define that range. One could also use a single-cell range variable in combination with two integers to define the row and column to define a larger range.

Obviously, there are situations where you would need to use one method over the other. But there are also situations where you need both the indices and the range at some point, and there are situations where either method will work (for example if you need to reference each cell in the range one by one).

So I don't need trivial pros and cons like "When you need to change the format of the entire range, reference the range.". Also, I'm only asking about which variable you would use in situations where either ranges or indices will work, or where both are needed at some point.

EDIT: I'm aware that for each will beat nested for loops at code golf in this situation, no need to mention this one or other situational code golf answers because anyone who knows how to use either method should be able to figure that out on their own.

Answer 1

In simple VBA, it is really just a style choice since either will do the job and any performance difference will be negligible. I can only see this question mattering in more complex scenarios.

A few things to consider:

You may need more than four integers to specify a range. A full reference to a single cell would require a file name, a worksheet name, a row, and a column.

If you change the layout of a worksheet, it may be simpler to redefine the range once at the beginning of your function rather than to change integer values in several places.

A range can contain multiple areas. For example, you could have four cells over here and a four non-contiguous cells over there defined by one range object.

If you are doing several things to each cell (say retrieving a value, setting color, setting border, setting number format), it could be better to use a range variable (telling Excel "this spot, do this") rather than repeatedly telling Excel to "go find this sheet/row/column, do this".

If you were calling Excel from the outside using COM Automation, there may be an advantage to making your interface clearer by returning a range object (already defined in a type library) rather than an array or other user-defined data structure (that you have to define an interface for, publish a type library, or apply a custom marshaling attribute).

• James Snell: It's worth checking to see if there's a performance hit for just referring to cell(s) instead of using a range too.

Answer 2

This is an oldie but still relevant. The question is about VBA variables and not the worksheet addresses. But going there. Hopefully you'll see why. There is an option in File/More/Options/Formulas to use "R1C1" references. So both column- and row- headers are numbers (No "A", "B", "C" column-headers.) I knew that forever-ago and at some point thought I should have made R1C1 my convention, but didn't want to change. Now I think it doesn't make any difference. I just enabled R1C1 references and the headers indeed changed. Then in the Immediate Window I typed ?ActiveCell.Address. Expected it to return "1,1" or "R1C1". No, it was "$A1ドル". Also, no matter what the headers are, Range.FormulaR1C1 = "...R[_]C[_]..." gets "coerced" if-you-will, in the sheet, to the Alpha-Num format. Using row and column numbers in Cells(_,_) or Range(..).Item(_,_) are very handy. Range(..).Offset(RowOffset,ColumnOffset) is to me just plain essential. I think it would be nice for maintaining code you haven't seen in years if you always used indices, and the R1C1 option labeled the top left cell "0,0", AND there was an R1C1 format for cell formulas. Then the OP's approach would really pay off. Visually. Assuming there is no performance hit, I think Maintainability should be your guide. You can always use proper Tables and/or Named Ranges.