New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.

ExcelKey

Dynamic Range Object

Free Excel Macros
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

Dynamic Range Object

Postby Sisyphus » Fri Jan 17, 2014 11:16 pm

This code sets a range object in Excel,
Code: Select all
    Set Rng = Range("A1:B10")
Since this is the type of code that the macro recorder spits out many novice programmers create dynamic code on its pattern. For example,
Code: Select all
    Dim Column_start As String, Dim Row_start As Long
    Dim Column_end As String, Dim Row_end As Long

    Set Rng = Range(Column_start & Cstr(Row_start) & ":" & Column_end & Cstr(Row_end))
In fact, this is terribly difficult to read for both Excel and the programmer. Excel addresses rows and columns by numbers. To address a cell using code that VBA can translate faster use syntax like Cells(1, 1) which specifies Row 1 and Column 1. Note that the row is always specified first. Since a range, like A1:B10 is specified by determining its top-left and bottom-right cells the corresponding VB syntax follows logically:-
Code: Select all
    Set Rng = Range(Cells(1, 1), Cells(10, 2))
Note that Cells refer to the ActiveSheet. If the range were to be set for another worksheet the reference to that sheet must be introduced. For example,
Code: Select all
    With Worksheets("Sheet1")
        Set Rng = Range(.Cells(1, 1), .Cells(10, 2))
    End With

' or
    Set Ws = Worksheets("Sheet1")
    Set Rng = Range(Ws.Cells(1, 1), Ws.Cells(10, 2))

In either case making this formula dynamic is much easier than the example modelled on the spreadsheet interface syntax, and be it only because there is no more need to deal with strings. This is the same specification as above but re-written in the other syntax:-
Code: Select all
    Dim C_start As Long, Dim R_start As Long
    Dim C_end As Long, Dim R_end As Long

    Set Rng = Range(Cells(R_start, C_start), Cells(R_end, C_end))
As you see, it is not only shorter but also much easier to comprehend.
The object of creating a function to replace this code is to still shorten it - making it still easier to read - while at the same time adding durability and flexibility. The function here introduced requires the call below to achieve the same result as the above examples:-
Code: Select all
    Set Rng = SetRange(R_start, C_start , R_end, C_end)
The four parameters are just lined up, one after the other. The only way to be shorter would be not to need them at all. This is where flexibility and durability come in which only a function can provide.

  • Set Rng = SetRange returns A1. This is because there is no row 0 or column 0. Therefore the function replaces 0 with 1. So, if your calculation of R-start or C-start returns the wrong result (anything smaller than 1 would be wrong) the function still returns a range, rather than an error. This enhances the stability of your code under circumstances that might be beyond your control.
  • Set Rng = SetRange(4) returns A4. Since only R_start is specified in the call, C_start is presumed to be 1.
  • The same thing works for the column. Set Rng = GetRange(, 4) returns D1.
  • Set Rng = SetRange(2, 2, 20) returns B2:B20. This is because the function presumes C_end to be equal to C_start if it isn't supplied. The same works for R_end and R_start.
  • Set Rng = SetRange(2, 2, , 26) doesn't specify R_end. Therefore B2:Z2 will be returned where R_end equals R_start.
  • The function is also capable of returning a range from another worksheet. Just add the sheet object to the function call.
    Code: Select all
        Set Rng = SetRange(R_start, C_start, R_end, C_end, Sheets("Sheet3"))
    If no sheet is specified in the function call the ActiveSheet will be presumed.

Here is the function's fully commented version.
Code: Select all
Function SetRange(Optional ByVal Rstart As Long = 1, _
                  Optional ByVal Cstart As Long = 1, _
                  Optional ByVal Rend As Long, _
                  Optional ByVal Cend As Long, _
                  Optional Ws As Worksheet) As Range
    '' 0088 V 1.0
   
    ' ==================================================
    '   Parameters:
    '       Rstart          = First worksheet row included in the range
    '                         [Optional: = 1 if not specified]
    '       Cstart          = First worksheet column included in the range
    '                         [Optional: = 1 if not specified]
    '       Rend            = Last worksheet row included in the range
    '                         [Optional: = Rstart if not specified]
    '       Cend            = Last worksheet column included in the range
    '                         [Optional: = Cstart if not specified]
    '       Ws              = The worksheet on which the range is to be created
    '                         [Optional: = ActiveSheet, if not specified]
    ' ==================================================
   
    ' Substitute 1 for erroneous Rstart or Cstart values
    With Excel.WorksheetFunction
        Rstart = .Max(1, Rstart)
        Cstart = .Max(1, Cstart)
    End With
   
    ' Rend and Cend equal the start parameters if unspecified or negative
    If Rend < 1 Then Rend = Rstart
    If Cend < 1 Then Cend = Cstart
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
   
    Set SetRange = Range(Ws.Cells(Rstart, Cstart), Ws.Cells(Rend, Cend))
End Function

In my own projects I don't normally need the comments and prefer to use what I call a stripped version that doesn't need as much space.
Code: Select all
Function SetRange(Optional ByVal Rstart As Long = 1, Optional ByVal Cstart As Long = 1, Optional ByVal Rend As Long, Optional ByVal Cend As Long, Optional Ws As Worksheet) As Range
    ' 0088 V 1.0
    With Excel.WorksheetFunction
    Rstart = .Max(1, Rstart)
    Cstart = .Max(1, Cstart)
    End With
    If Rend < 1 Then Rend = Rstart
    If Cend < 1 Then Cend = Cstart
    If Ws Is Nothing Then Set Ws = ActiveSheet
    Set SetRange = Range(Ws.Cells(Rstart, Cstart), Ws.Cells(Rend, Cend))
End Function

By the way, here is a little trick to determine the number of a column:
Code: Select all
    C_end = Range("BF1").Column
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 2 guests