FILTER |
FILTER(sourcearray, match [,include] [,compare]) |
Returns an array containing a subset of values that contain a substring (Variant). |
sourcearray | The array of strings to be searched (1-dimensional) (Variant / Array) |
match | The text string to search for (String). |
include | (Optional) Whether to return substrings that include or exclude match: True = subset does include the match (default) False = subset does not include the match |
compare | (Optional) A vbCompareMethod constant specifying the type of string comparison to use (Integer): -1 = vbUseCompareOption (uses the "Option Compare" setting) 0 = vbBinaryCompare (case sensitive) (default) 1 = vbTextCompare (not case sensitive) 2 = vbDatabaseCompare (uses an Access database) |
REMARKS |
* This function performs a Partial Match. There is no way to specify matching the entire element or just part of it. * This function is case sensitive (by default). * This function does not support wildcards (? and *). * This function will always return a 0 based array. * This function always returns a 1 dimensional array. * This function ignores any Option Base 1 statements and always creates an array that starts at 0. * This function searches an array for all the elements that match a given text string and returns an array of just the elements that match. * If no matches of "match" are found within "sourcearray", then an empty array is returned. * If "sourcearray" is Null, then a "type mismatch" run-time error occurs. * If "include" = True, then the subset is all the values that does include the substring "match". * If "include" = False, then the subset is all the values that do not include the substring "match". * If "include" is left blank, then True is used. * If "compare" is left blank, then -1 is used. If there is no Option Compare statement provided then vbBinaryCompare (0) is used. * If "compare" is Null, then an error occurs. * The array that is returned only contains enough elements to contain the number of matched items. * This function can be used to determine if a particular element exists in a 1-dimensional array. * This function can return an empty array. When this happens the lowerbound is 0 and the upperbound is -1. * You can use the ARRAY function to return an array containing specific values. * You can use the JOIN function to return a text string containing all the elements in an array. * You can use the SPLIT function to return an array containing a specified number of substrings. * The equivalent .NET function is Microsoft.VisualBasic.Strings.Filter * For the Microsoft documentation refer to learn.microsoft.com |
Dim aValues(2) As String
aValues(0) = "This"
aValues(1) = "is"
aValues(2) = "it"
Dim aSubStrings() As String
aSubStrings = Filter(aValues, "is")
Debug.Print aSubStrings(0) = "This"
Debug.Print aSubStrings(1) = "is"
Debug.Print VBA.Join(aSubStrings, ",") '= This,is
aSubStrings = Filter(aValues, "i")
Debug.Print VBA.Join(aSubStrings, ",") '= This,is,it
'Return the values that do not contain "is"
aSubStrings = Filter(aValues, "is", False)
Debug.Print VBA.Join(aSubStrings, ",") '= it
aSubStrings = Filter(aValues, "is", , VBA.VbCompareMethod.vbTextCompare)
Debug.Print VBA.Join(aSubStrings, ",") '= This,is
aSubStrings = Filter(aValues, "is", True, VBA.VbCompareMethod.vbTextCompare)
Debug.Print VBA.Join(aSubStrings, ",") '= This,is
Return the values that contain "IS" not case sensitive
aSubStrings = Filter(aValues, "IS", True, vbTextCompare)
Debug.Print VBA.Join(aSubStrings, ",") '= This,is
aSubStrings = Filter(aValues, "IS")
Debug.Print "'" & VBA.Join(aSubStrings, ",") & "'" '= blank
aSubStrings = Filter(aValues, "i", False)
Debug.Print "'" & VBA.Join(aSubStrings, ",") & "'" '= blank
aSubStrings = Filter(aValues, "?s")
Debug.Print "'" & VBA.Join(aSubStrings, ",") & "'" '= blank
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top