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

Question

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

Rory · Accepted Answer · 2014-08-15 15:33:23Z

1

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)

edited Aug 15, 2014 at 15:33

answered Aug 15, 2014 at 14:58

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

5 Comments

Jamie Walker Over a year ago

I tried defining my name as FurnaceArea and changed my code to ActiveSheet.ListObjects("Database").Range.AutoFilter Field:=7, Criteria1:=[FurnaceArea], Operator:=xlFilterValues but it debugged.

Rory Over a year ago

What was the error, and did you define it exactly as shown above?

Jamie Walker Over a year ago

Run-time error '-2147417848 (80010108)': Automation error: The object invoked has disconnected from its clients. I just defined it from a list I have on another tab in my spreadsheet, this is where I update any information and where any changed will take place.

Rory Over a year ago

See the revised code - you need different syntax to use ranges.

Jamie Walker Over a year ago

I don't know if you're still getting updates for this but I posted another part to the question. It's pretty much the same principal just used in a slightly different way I figured you might have a quick fix for it. Thanks for all your help.

Gary's Student · Accepted Answer · 2014-08-15 15:14:57Z

1

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

answered Aug 15, 2014 at 15:14

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related