CODEWITHSUNDEEP
arraysexcelvbauser-defined-functions
ARINDAM MOITRA
ARINDAM MOITRA

Reputation: 11

How to use Wildcards in VBA Array Filter Function to find non-empty strings in array?

Overview I am trying to create a User Defined Function where I want to pass three strings (cell values) — Value1, Value2 and Value3, pass them to an array and then use the Join Function to concatenate them using a New-Line character and adding a custom text as prefix before each string.

Target

But before I join them, I want to ensure none of the parameters passed are empty strings. I am trying to use VBA Filter function to detect non-empty strings passed in the array and then join them.

Where am I getting stuck

However, if I pass "" or vbNullString as Match parameter and set the Include parameter to 'False', the VBA Filter function is not able to detect non-empty strings. I have also used the Trim function to ensure that none of the strings passed in the array contain whitespaces.

Can I use some wildcard characters to ensure that the strings contain at least one or more characters? I have tried passing "*?" characters in the Match argument and set Include parameter to 'True' but it does not work.

Question

Is there a possible way to use VBA Filter Function, possibly with wildcard characters?

Upvotes: 0

Views: 709

Answers (1)

T.M.
T.M.

Reputation: 9948

Via function GetInfotext() I demonstrate how to

  • use a ParamArray argument,
  • filter out empty strings by help procedure RemoveEmpty (see a),
  • concatenate custom prefixes with the found non-empty strings (see b).

Example call

Sub ExampleCall()
    Debug.Print GetInfotext("Intro", "", "bla bla")
End Sub

Function GetInfotext(ParamArray s()) As String
    Dim tmp: tmp = s
'a) get non-empty elements
    RemoveEmpty tmp
'b) add custion prefixes
    AddPrefixes tmp, s
'c) return entire text
    GetInfotext = Join(tmp, vbNewLine)
End Function

Result in VB Editor's immediate window:

X: Intro
Z: bla bla

Help procedures to GetInfoText

The main requirement in OP is to filter out empty string elements; as blanks would be found in any element and there are no special wild cards for them, it's necessary to identify empty elements by an unused unique character, e.g. $, so eventual negative filtering allows to remove these marked elements.

Note that the double replacement is necessary to allow to mark consecutive empty elements by the $ mark, as VBA would skip several replacing characters here.

Sub RemoveEmpty(arr)
    Dim tmp
    tmp = Replace(Replace(Join(arr, "|"), "||", "|$|"), "||", "|$|")
    arr = Filter(Split(tmp, "|"), "$", False)
End Sub

In order to check how to add the found elements to your custom prefixes, you might use the following help procedure:

Sub AddPrefixes(tmp, ParamArray s())
    'define custom prefixes -- change to your needs
    Dim prfx() As Variant
    prfx = Array("X: ", "Y: ", "Z: ")
    'concatenate them with passed non-empty elements
    Dim i As Long, ii As Long
    For i = LBound(prfx) To UBound(prfx)
        If Len(s(0)(i)) Then tmp(ii) = prfx(i) & tmp(ii): ii = ii + 1
    Next
End Sub

Upvotes: 0

Related Questions