VBA Enum - how to pass Enum as parameter,in function.

[Mallesh23]

Hi Team,

How to use Enum in Function to reduce lenghty parameter in functions.
In my below code Can I use Enum for Optional parameter.?.. and how to use.

Option Explicit
Sub Sort_Data()

    Dim datarng As Range
   
    With Sheets("Data")
        Set datarng = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With
       
    'Sorting through Custom Function  '('how to create and pass in Enum for Optional Parameter in Function)
    Sort_Data_Asc datarng, datarng.Range("A1"), xlAscending,xlyes
       
End Sub



Function Sort_Data_Asc(rng As Range, k As Range, Optional Order1 As XlSortOrder = xlAscending, Optional Header As XlYesNoGuess = xlYes)

    rng.Sort k, Order1, Header:=Header

End Function

Below is a Table

Name	Scores
Rohit	7000​
Sachin	6000​
Virat	9000​
Yuvraj	3000​
zeb	8000​
Jadeja	2500​

Regards,
mg

 

[Fluff]

Yes, you can do that

Sort_Data_Asc datarng, datarng.Range("a1"), 2, 1

Function Sort_Data_Asc(rng As Range, k As Range, Optional Order1 As Long = 1, Optional Header As Long = 1)

 

[Mallesh23]

Hi Fluff,

Thanks for your reply,
that is one way and it worked, something I am looking for below its working ,but I am not sure whether this method is right.

Public Enum mySorting
    MysortAsc = 1
    MysortDesc = 2
End Enum

Sub Sort_Data()

    Dim datarng As Range
    
    With Sheets("Data")
        Set datarng = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With
        
    'Sorting through Custom Function
    Sort_Data_Asc datarng, datarng.Range("A1"), 1
End Sub

Function Sort_Data_Asc(rng As Range, k As Range, mySorting, Optional header As XlYesNoGuess = xlYes)
Select Case mySorting
    Case MysortAsc
    rng.Sort k, 1
    Case MysortDesc
     rng.Sort k, 2
    
End Select

End Function

 

[Fluff]

Why not just use the numbers as I showed?

 

[Mallesh23]

Hi Fluff,

thank you for your help, Your approach is right,
I was just creating Custom function which we can directely use in project. by importing Utilities.
and also trying to learn Enum. thanks fluff

Sort_Data_Asc datarng, datarng.Range("A1"), 1


Regards,
mg

 

[Fluff]

You're welcome & thanks for the feedback.