Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice - Cell and Range references in VBA?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice - Cell and Range references in VBA?
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. DocBrown wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice - Cell and Range references in VBA?
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 "DocBrown" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice - Cell and Range references in VBA?
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 --------------- Jacob Skaria "DocBrown" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice - Cell and Range references in VBA?
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" wrote: 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 --------------- Jacob Skaria "DocBrown" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice - Cell and Range references in VBA?
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 --------------- Jacob Skaria "DocBrown" wrote: 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" wrote: 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 --------------- Jacob Skaria "DocBrown" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range names to cell references | Excel Worksheet Functions | |||
Range Names convert to Cell References | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Changing cell references in a Range to Absolute | Excel Discussion (Misc queries) | |||
VBA References - when is Office Object Library Reference set? Best practice re. Early/Late binding ... | Excel Programming |