Sorting by Code (Excel 2K sr1a)

Topic

New Reply

I have a workbook with several sheets, two of which are similar. I have automated a Sort with the following function, called from the Before_save event of the workbook and it works. However, I set it up as a temporary measure until I worked out how to incorporate the range names into the code and this I can’t get to work. The range is dynamic so grows with input and could currently end on F250 for example. I have this worked properly it just doesn’t seem to transfer to code. I would have thought I could replace “A6:F1000” with a string expression for the Name but I can’t.
Any advice out there?

Function Reorder(ByVal strSheet As String)
Worksheets(strSheet).Range(“a6:f1000”).Sort _
key1:=Worksheets(strRange).Range(“A6”)
End Function

Thanks
Peter

Reply | Quote

Replies

Are you sure this works? I would be surprised if it does since you should not be able to sort from a Function. However, I just tried it and it does seem to work. Does anyone else out there know how this gets around the restriction of a function modifying a sheet directly?

There also does not seem to be anything to give strRange a value, unless it is a Global variable.

Are you talking about using a Named Range for the sort range? If so, I created a range on Sheet1 named SortRange, and the following code worked fine for me in a Sub procedure:

Dim strSheet As String, strRange As String
    strSheet = "Sheet1"
    strRange = "Sheet1"
    Worksheets(strSheet).Range("SortRange").Sort _
    key1:=Worksheets(strRange).Range("A6")

I also tried this, and it seemed to work just fine:

Function Reorder(ByVal strSheet As String, ByVal strRange As String) As Integer
    Worksheets(strSheet).Range("SortRange").Sort _
    key1:=Worksheets(strRange).Range("A6")
End Function


Public Sub Test2()
    A = Reorder("Sheet1", "Sheet1")
End Sub

Are you doing something different?

Reply | Quote

Legare
Yes it definitely works on the basis it is currently entered. I’ve included the sub that calls the function this time and I can assure you it works exactly as it’s supposed to.
All I would like to do is replace the Range ref A6:F1000 with the Names (“OrderBus” and “OrderPers”) so I don’t need to go in and alter the code if the range grows too big. It must be possible but I can’t make it work.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Reorder “Business”
Reorder “Personal”
lngResult = MsgBox(UCase(“is it ok to save now?”), 292, “Save”)
If lngResult = 7 Then Cancel = True
End Sub

Function Reorder(ByVal strRange As String)
Worksheets(strRange).Range(“a6:f1000”).Sort _
key1:=Worksheets(strRange).Range(“A6”)
End Function

Reply | Quote

Legare
I’d replied before reading your post properly. There was a misprint in the first post. I’m looking to include a string to cover the actual range for the 2 sheets as well as the name of the 2 sheets themselves (Business and Personal = sheets – OrderBus and OrderPers = the sort range on each sheet.) I just keep getting an error when I replace “A6:F1000” with a string to replace the range Names.
Peter

Reply | Quote

Legare

It works just fine with the Named Ranges as variables. The reason I was getting an error was that I had a typing error in the definition of one of the dynamic Named Ranges.

Check the simple things first before you play around with the complicated stuff and go asking for other people’s help, eh?

Thanks for your help, anyway.

Peter

Reply | Quote

After no testing I would say that the no-changes restriction only is valid for functions (udf’s) called from a worksheet cell.

Reply | Quote

After testing, it appears that you are correct! I had always thought that the restriction was absolute.

Reply | Quote

Jan Karel,
You are correct – you can do anything with a function that you can do with a sub as long as you don’t call it from a worksheet. Using a function rather than a sub simply allows you to return a value to the calling procedure – e.g. success or failure. Of course, if you want to run it from the macros dialog or a toolbar, you need a calling sub too.

Reply | Quote

Just a side remark.

I see you have only specified one sort key and have omitted the others. I’m not sure about the Sort method, but with the Find method Excel uses the options that are not set as they were used druing the last find done. You might get into nasty surprises that way. I would explicitly set all options the way you need them in your code to avoid possible erraric behaviour.

Reply | Quote

Jan Karel
You are right. Some of the options are carried forward but as ny method uses the defaults and I can’t think of a time I have changed this I think I’ll be OK. The worst that should happen is a descending sort instead of ascending.
Thanks.
Peter

Reply | Quote