Exclude keyword from array

[solidENM]

Hello,
Does anyone know how to exclude words from an array? I currently use a macro that uses an array to move items with a specified thickness to another tab. The problem is it also catches screws, since they can be 1/4 FHCS, 3/8 BHCS, etc. I need a way to omit "fhcs" and "bhcs" from the array.


Dim StrArray As Variant
StrArray = Array("1/8", "3/16", "1/4", "3/8", "1/2", "3/4")


i was not able to find anything via google, any help would be appreciated.

Thanks

 

[Leith Ross]

Re: how to exclude keyword from array

Hello SolidENM,

Since there are some details missing in your post, I am not sure if this is what you need. But, here is a User Defined Function (UDF) that will return only the fractional portions from a text string as a string array. If there are no fractions in the text then an array with a single empty string is returned. If you have any questions or need help with this code, let me know.

Function ExcludeWords(ByVal Text As String)


    Dim Matches     As Object
    Dim RegExp      As Object
    Dim sArray()    As String
    
        Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.Pattern = "(\d+\/\d+)"
        
        Set Matches = RegExp.Execute(Text)
        If Matches.Count Then
            ReDim sArray(Matches.Count - 1)
            For n = 0 To UBound(sArray)
                sArray(n) = Matches(n)
            Next n
            ExcludeWords = sArray
        Else
            ExcludeWords = Array("")
        End If
        
End Function

 

[solidENM]

Re: how to exclude keyword from array

Hi Leith,
Thanks for the help. I am new to dimming as objects. Can you tell me if i am understanding this correctly?

for the pattern part-- is that making a wildcard of any # that is in fractional format? I am not familiar with "(\d+\/\d+)"

the if/then else part is saying sarray or array depending on if it finds text matching sarray?

 

[Leith Ross]

Re: how to exclude keyword from array

Hello SolidENM,

This macro uses the Regular Expressions object found in the VBScript library. This object is commonly used by web applications to parse input data. That explais why it looks so different from anything you have seen before.

The Pattern property describes what to look for in the text string. The Global property when set to True looks for all possible pattern matches in the string. Matches are defined by the parentheses in the pattern and saved internally. The pattern \d+ means 1 or more digits. The \ before another character is used to "escape" it. So, the forward slash must be preceded by the backslash to prevent the forward slash from denoting the start or end of a literal within the pattern. The pattern only matches fractions within the text.

The macro is designed to always return an array of values. The If statement makes certain if only one match is found then that match is added to an array containing only one element. Otherwise it would return a string value for the single match.

 

[solidENM]

Re: how to exclude keyword from array

Thanks for the explanation Leith. I tend to reuse my macros often, but wasnt sure how this one operated since it was new. Have a good one!