How to pass range variables between subroutines in Excel VBA

Question

I'm trying to pass a range to a subroutine, but its throwing up a "Method 'Range' of object '_Global' failed" error.

In the main I declare and define the range variable I want to use:

Sub maintest()

Dim ScheduledSort As Range
Set ScheduledSort = Range("F4:F321")

Call test(ScheduledSort)
End Sub

Then in the subroutine test I want it to sort using the range I passed it from the routine above:

Sub test(RangeForSort)

Sheets("SheetTest").Select

' Sort in descending order
ActiveWorkbook.Worksheets("SheetTest").AutoFilter.Sort.SortFields.Add _
    Key:=Range("RangeForSort"), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SheetTest").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

I think its going wrong at the Key:=Range("RangeForSort") but I can't work out why and how to fix it.
What is it I'm doing wrong with the Range and how do I fix it such that I can pass it any Range to sort on?
And if you have a better suggestion for what I'm trying to do, feel free to add! :-)

Key:=RangeForSort or ... Key:=Range(RangeForSort.address) ... RangeForSort is a range object, not a string. You might also have incorrect worksheet as indicated by Jeeped, but the second version should take care of that. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 3, 2015 at 13:13
You don't need the maintest() sub. You could directly pass the range to your test() sub. — Moritz Schmitz v. Hülst
– Moritz Schmitz v. Hülst, Commented May 3, 2015 at 14:44
maintest() is a snippet from a larger piece that calls other routines... In the sub rountine that testsub is cut from, I sort on some criteria passed to it from the sub above and then I filter on specific cells. I need to filter on different criteria and then sort on different columns. Having a generalised subroutine that did the filter and sort seemed the best way to do that. — E_L
– E_L, Commented May 3, 2015 at 16:50
In any case, one of the answers should work. If it does, please select one as an accepted answer. — Moritz Schmitz v. Hülst
– Moritz Schmitz v. Hülst, Commented May 4, 2015 at 16:57

Moritz Schmitz v. Hülst · Accepted Answer · 2015-05-03 16:58:03Z

1

Shorter version would look like this:

Sub test(rng As Range)

    ' Sort in descending order
    Worksheets(rng.Parent.Name).AutoFilter.Sort.SortFields.Add _
    Key:=rng, SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortTextAsNumbers

    With Worksheets(rng.Parent.Name).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
     End With

End Sub

To run:

Call test(Worksheets("YOUR WORKSHEET NAME").Range("YOUR RANGE")).

edited May 3, 2015 at 16:58

answered May 3, 2015 at 14:41

Moritz Schmitz v. Hülst

3,7316 gold badges44 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

E_L Over a year ago

I'm currently running this macro from the PERSONAL.XLSB file in excel and run it from there on the appropriate file. If I start using ThisWorkbookwon't this stop working?

Moritz Schmitz v. Hülst Over a year ago

Yes, it will. You could delete ThisWorkbook completely if you are sure that the name of your range's worksheet is unique in all open Excel workbooks.

Gary's Student · Accepted Answer · 2015-05-03 12:26:50Z

1

If you pass a Range object to the sub, you are passing an object that is already associated with some worksheet. The sub selects a potentially different worksheet and then has trouble handling the passed range.

If you want to pass a specific block of cells to a sub that needs to change worksheets, then pass a String variable instead.

UNTESTED

Sub maintest()
    Dim ScheduledSort As String
    ScheduledSort = "F4:F321"
    Call test(ScheduledSort)
End Sub

Sub test(RangeForSort As String)
    Sheets("SheetTest").Select

    ActiveWorkbook.Worksheets("SheetTest").AutoFilter.Sort.SortFields.Add _
        Key:=Range(RangeForSort), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortTextAsNumbers

    With ActiveWorkbook.Worksheets("SheetTest").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

answered May 3, 2015 at 12:26

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

E_L Over a year ago

I think I understand what you mean when you say "ub selects a potentially different worksheet and then has trouble handling the passed range" and this is why in all my subs I code that selects the sheets I want to work on, for example Sheets("SheetTest").Select here. Is this not a good way to work?

FreeSoftwareServers · Accepted Answer · 2019-03-17 07:14:28Z

1

I set this up and now I can pass the "ActiveCell/Range" whatever and call the function throughout my project if needed.

Public colLetter As Variant

Sub Test()
Dim rng As Range
Set rng = ActiveWorkbook.ActiveSheet.Range("A1:A1")
Call GetColLet(rng)
End Sub

Public Sub GetColLet(var As Range)
colLetter = Split(var.Address, "$")(1)
MsgBox colLetter
End Sub

answered Mar 17, 2019 at 7:14

FreeSoftwareServers

2,8906 gold badges43 silver badges79 bronze badges

Collectives™ on Stack Overflow

How to pass range variables between subroutines in Excel VBA

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related