Adding this VBA to a Function

[Mahwill]

Is there a way to add this VBA code into a function and only call the function instead of writing out this code every time?

Here is the code -
opt.Activate
If ActiveSheet.AutoFilterMode = True Then
opt.AutoFilter.Sort.SortFields.Clear
opt.AutoFilter.Sort.SortFields.Add Key _
:=Range("BQ6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
Else:
Range("A6:CK6").AutoFilter
opt.AutoFilter.Sort.SortFields.Add Key _
:=Range("BQ6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
End If
With opt.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The range that I sort on changes so I was wondering if I could declare that as a variable and just fill in the range I need when I call the function.

Any help would be greatly appreciated.

 

[Dartagnan]

You could put the code in a Module and then simply call your code from anywhere in workbook/code.

In the Module create a Sub like Sub mycode(). Copy your code between the sub and end sub.

Once there you should be able to simply reference your code from other places in the code by simply typing Call mycode.

 

[Dartagnan]

As far as passing the range to your code you will likely need to create a parameter.

For example, Sub mycode(rng as Range)

When you call your code the syntax will be Call mycode(rng).

At some point before you call mycode you will need to declare and set the rng variable.

 

[Mahwill]

Okay, here's what I have,
Sub sort(rng As Range)
Dim opt As Worksheet
Set opt = Sheets("OptimizerInfo")
If opt.FilterMode Then
opt.ShowAllData
End If
opt.Activate
If ActiveSheet.AutoFilterMode = True Then
opt.AutoFilter.sort.SortFields.Clear
opt.AutoFilter.sort.SortFields.Add Key _
:=Range(rng), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
Else:
Range("A6:CK6").AutoFilter
opt.AutoFilter.AutoFilter.sort.SortFields.Add Key _
:=Range(rng), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
End If
With opt.AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

and I'm calling it using

Sub test()
Dim rng As Range
Set rng = Range("I6")
Call sort(rng)
End Sub

This isn't corrent though because it gives me the value within the cell not the range itself. How do I fix this?

 

[AlphaFrog]

Change this...

opt.AutoFilter.sort.SortFields.Add Key _
:=[COLOR="Red"]Range(rng)[/COLOR], SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal

To this...

opt.AutoFilter.sort.SortFields.Add Key _
:=[COLOR="Red"]rng[/COLOR], SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal

Also, you should specify the sheet when you define rng in Sub test()

Sub test()
Dim rng As Range
Set rng = [COLOR="Red"]Sheets("OptimizerInfo").[/COLOR]Range("I6")
Call sort(rng)
End Sub

 

[Dartagnan]

Nice catch.

 

[Mahwill]

That works! Thank you so much for your help.

 

[Norie]

Just an aside - don't call the sub sort.

Sort is a VBA method.