Return a range from a cell's address in an VBA function?

Question

I have the following function to find a cell's range:

Function find_last_column()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("Data_History")
    Set rng1 = ws.rows(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
    find_last_column = rng1.Address
End Function

I'd like to use rng1.Address as a range in the following sub:

Sub Start_of_range()
    Dim starting_cell_string As Range
    starting_cell_string = find_last_column()
End Sub

I'd like to use Offset from `starting_cell_range' to then unload an array.

score 0 · Accepted Answer · answered Mar 14 '15 at 23:41

0

Use this

Set starting_cell_string = Range(find_last_column())

instead of starting_cell_string = find_last_column()

answered Mar 14 '15 at 23:41

ZygD

22,092
39
79
102

score 0 · Answer 2 · 2015-03-15T05:18:57.017

You seem to be mashing up a string address and a range object.

Function find_last_column()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("Data_History")
    Set rng1 = ws.rows(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
    'might as well include the worksheet reference for added functionality (external:=true)
    find_last_column = rng1.Address(1, 1, xlA1, external:=true)
    set rng1 = nothing
    set ws = nothing
End Function

Sub Start_of_range()
    Dim starting_cell_string As STRING, starting_cell_range As RANGE
    starting_cell_string = find_last_column()
    set starting_cell_range = range(starting_cell_string)

    ' do stuff here

    set starting_cell_range = nothing
End Sub

I've change your var declaration for starting_cell_string in the Start_of_range sub to String which is what your function is returning. I've added a new Range object type variable (starting_cell_range) to Set to the cell(s) defined by the address string.

this is great! Question for you: why set the variables to `nothing` at the end of the function? — DBWeinstein, Mar 15 '15 at 00:24
@dwstein - Simply put, I do not trust VBA for object destruction. It takes very little to type and is a habit for me by now. see [When should an Excel VBA variable be killed or set to Nothing?](http://stackoverflow.com/questions/19038350/when-should-an-excel-vba-variable-be-killed-or-set-to-nothing) for more detail. — , Mar 15 '15 at 00:35

Return a range from a cell's address in an VBA function?

2 Answers2