Best Practice - Cell and Range references in VBA?

[DocBrown]

I have seen many methods to reference ranges and cells in VBA. Also, in a WS
various techniques are used to allow for insertion and deletions of cells and
columns, such as relative and absolute addressing.

When rows and columns are changed in a WS, As far as I can tell, those
adjustments are not made in the Macros.

My question is: What is the best practice for coding functions and macros so
that they don't break if the rows and columns are changed in the worksheet we
are referencing?

Thanks,
John

 

[Dave Peterson]

That's a pretty big question.

One technique is to name the range you need to use
(in xl2003 menus: Insert|Name)

Then if you insert or delete any rows/columns, then that name will refer to that
original cell--unless you delete the row/column that contained the cell.

 

[Jacob Skaria]

When you are not sure about the last row of particular column then
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'When you are not sure about the last used column in a particular row
lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

If you are not at all sure about the range..the below will help
Set rngTemp = Activesheet.Usedrange


If you are looking at referencing; when you work with column numbers and row
numbers try

Range(Cells(r1,c1), Cells(r2,c2))
where r1,c1,r2,c2 are numbers

When you work with row numbers try Range("A1","J10") OR Range("A1:J10")
Range("A" & r1 & ":J" & r2)
Range("A" & r1 , "J" & r2)
where r1 and r2 are row numbers and A and J are column names or you can
replace those with string variables.

OR

Range(Cells(r1,"A"),Cells(r2,"J")) where r1 and r2 are row numbers and A and
J are column names or you can replace those with string variables.


If this post helps click Yes

 

[Jacob Skaria]

For the deletion...

If you know the header atleast you can use Range.Find property to find the
column number.....OR always work with named ranges..

If this post helps click Yes

 

[DocBrown]

I'm getting the impression that this is the best way:

"OR always work with named ranges.."

I've used all the other techniques to handle when the user inserts rows
since the records and programming cycle through the rows.

This usually comes into play when I want to modify my WS and the code is,
say referencing column D. Then I insert a new row before D. Now the code is
referencing the wrong column, so I have to make sure I update the code.
Usually, the user never has to enter new columns. That up to me as the
designer of the ws.

What do you think of setting up global constants that set the values of the
columns I'm writing code against, then I just need to change the globals and
the code should work.

John

 

[Jacob Skaria]

If so; I would suggest to keep a separate worksheet where you can enter the
column numbers to be referenced. Write the code to pick the column numbers
from this worksheet; before executing the current code.

OR

As mentioned earlier; if you have a header in Row1. You can use find to get
the column number.

OR

Use the Input box so that the user can select the range.

Dim varRange As Range
Set varRange = Application.InputBox( _
Prompt:="Please select the range", Title:="Message", Type:=8)


If this post helps click Yes