CODEWITHSUNDEEP
excelvba
Jamie Walker
Jamie Walker

Reputation: 213

How to use a defined name in an array in my code?

I have a sheet with multiple macros in it and the macros have to filter a list by certain values and this is a common theme throughout my code and is repeated multiple times. What I want to do is to define the name of each list of values that need to be filtered and use that defined name in my filter array so every time I change the values for that defined name I won't have to go in and update each array. For example, below is just one part of my code that is repeated over multiple system sizes. I would like to define the list of values I have as the array and use that defined name as the array to make it a lot easier.

  ActiveSheet.ListObjects("Database").Range.AutoFilter Field:=7, Criteria1:=Array("NAPS", "ES", "FM", "PW", "C2H2", "CA", "Fluids", "Fluids Eng"), Operator:=xlFilterValues

So I would like to define the "NAPS", "ES", etc. as a name and use that name here instead of all the values. Any help is much appreciated.

I also have a custom order list to sort my database that I have on one of my tabs. Below is the code I have for the custom order list and I have a list named Sorting_List I would like to use instead of each line item but am not sure how to do it. I tried to use the same code I used for the filtering but it didn't work.

   ActiveWorkbook.Worksheets("Database").ListObjects("Database").Sort.SortFields. _
    Add Key:=Range("Database[Category]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, CustomOrder:= _
    "FL,MF,HF,Furnace Eng,Launder,CM,CM Eng,ML,PP,Lab SOT,Lab AT,SSP,SII,NAPS,ES,FM,PW,C2H2,CA,Fluids,Fluids Eng,Elec,PMDA,Elec Eng,CH SW,CH SII,CH Std,CH High,CH Elec,CH Eng,CH FS,FS SW,FS SII,FS Elec,Eng,Train,Lab,Lab SW" _
    , DataOption:=xlSortNormal

Upvotes: 0

Views: 160

Answers (2)

Rory
Rory

Reputation: 34055

Define a name as say, foo, using:

={"NAPS", "ES", "FM", "PW", "C2H2", "CA", "Fluids", "Fluids Eng"}

and then the code is simply:

ActiveSheet.ListObjects("Database").Range.AutoFilter Field:=7, Criteria1:=[foo], Operator:=xlFilterValues

If you want to use a range, then the syntax is different. For a one column range:

ActiveSheet.ListObjects("Database").Range.AutoFilter Field:=7, Criteria1:=Application.Transpose([foo]), Operator:=xlFilterValues

If the range is a row, you need to transpose twice (to make it a 1-dimensional array)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

One way is to create and then use a Global array:

Dim GlobalArray() As String

Sub MAIN()
    ReDim GlobalArray(0 To 7)
    GlobalArray(0) = "NAPS"
    GlobalArray(1) = "ES"
    GlobalArray(2) = "FM"
    GlobalArray(3) = "PW"
    GlobalArray(4) = "C2H2"
    GlobalArray(5) = "CA"
    GlobalArray(6) = "Fluids"
    GlobalArray(7) = "Fluids Eng"
End Sub

Once MAIN() has been run, the array is available to any sub in the module:

ActiveSheet.ListObjects("Database").Range.AutoFilter Field:=7, Criteria1:=GlobalArray

Upvotes: 1

Related Questions