<VBA> How to pass Array into VBA Script parameters

ProjectVBA.zip (281.2 KB)

I as per written VBA in Macro.txt I want to filter like that

Sub Macro2()

’ Macro2 Macro
Range(“A1:D1”).Select
Selection.AutoFilter
ActiveSheet.Range(“$A$1:$D$9”).AutoFilter Field:=2, Criteria1:=Array(“ABC”, _
“DEF”, “GHI”, “JKL”), Operator:=xlFilterValues
ActiveSheet.Range(“$A$1:$D$9”).AutoFilter Field:=3, Criteria1:=“=waiting”, _
Operator:=xlOr, Criteria2:=“=working”
ActiveSheet.Range(“$A$1:$D$9”).AutoFilter Field:=4, Criteria1:=“=P1”, _
Operator:=xlOr, Criteria2:=“=P2”
Cells.Select
Selection.Copy
Sheets(“Sheet2”).Select
Cells.Select
ActiveSheet.Paste
End Sub

How this Array can be made into a variable??

@anjali123 ,

There is no other datatype support for VBA script parameter except String.

You can pass comma separated string or add some distinguishing character in your string parameter value which you can use to split the string and prepare array.

There is no direct way to pass array or other datatypes.

Thanks,
Ashok :slight_smile:

Can you please elaborate more like using assign activity, so that I can use it and check it, if it works?

Suppose this is your array:-

“ABC”,“DEF”, “GHI”, “JKL”

Then store this in arrayVar. Convert the same in string using below syntax

stringVar=String.Join(“,”, arrayVar)

Then pass stringVar in parameter.

@anjali123 ,

Please find the updated code here. I have added below logics

ProjectVBA.zip (277.7 KB)

  1. Get Unique AG’s to filter into an array of string

  2. Convert and pass array to string and pass to VBA.
    image

  3. VBA Code

Thanks,
Ashok :slight_smile:

1 Like

Thank you sooo much. It worked :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.