Creating Wildcards

I need to save the names of one or more files in the custom document properties of a workbook. I don’t want to create a separate property for every filename. I thought about saving a comma-delimited string, then parsing it out. It would look like:

file1name,file2name

Another idea I had was just save one string that could stand for both:

file?name

I need to convert a number of strings into one string with the appropriate wildcards. I came up with the code below, but it has some shortcomings. It replaces differing characters in the same position with a question mark, and converts strings of three question marks or more into an asterisk. That means for wildcards("consistent","inconsistent"), it returns *s* when I would prefer it return *consistent.

Any suggestions on making it better?

Function Wildcards(ParamArray vaText() As Variant) As String
   
    Dim i As Long, j As Long
    Dim sShort As String, sLong As String
    Dim sTemp As String
   
    Const sQUES As String = “?”
    Const sASTR As String = “*”
       
    ‘If only one string, then return it
   If LBound(vaText) = UBound(vaText) Then
        Wildcards = vaText(LBound(vaText))
    Else
        sShort = vaText(LBound(vaText))
       
        ‘Store the longest and shortest strings
       For i = LBound(vaText) To UBound(vaText)
            If Len(vaText(i)) < Len(sShort) Then
                sShort = vaText(i)
            End If
            If Len(vaText(i)) > Len(sLong) Then
                sLong = vaText(i)
            End If
        Next i
       
        sTemp = sShort
       
        ‘replace differing chars with ?
       For i = LBound(vaText) To UBound(vaText)
            If vaText(i) <> sShort Then
                For j = 1 To Len(sShort)
                    If Mid(vaText(i), j, 1) <> Mid(sTemp, j, 1) Then
                        sTemp = Left(sTemp, j – 1) & sQUES & Mid(sTemp, j + 1, Len(sTemp))
                    End If
                Next j
            End If
        Next i
       
        ‘pad ?s to the end of the longest string
       sTemp = sTemp & String(Len(sLong) – Len(sShort), sQUES)
       
        ‘replace three or more ?s with a *
       If Len(sLong) >= 3 Then
            For i = Len(sLong) To 3 Step -1
                sTemp = Replace(sTemp, String(i, sQUES), sASTR)
            Next i
        End If
               
        Wildcards = sTemp
    End If
   
End Function
Posted in Uncategorized

23 thoughts on “Creating Wildcards

  1. How do you propose to restore the names of the original files from the stored abbreviated tokens? For example, how will you know that file?name maps to file1name and file2name but must not include file3name or fileAname?

    Obviously, I don’t know why you need to store the results in a custom property but if it isn’t too out of the way…and it’s higly likely you have already thought of this but in the remote chance that you have not…consider a hidden worksheet.

  2. This is a bit off topic, but viewing the code on the last few posts and the fact that I am in the middle of a new project, made me curious to know:

    Has MS changed the infuriating process ( for Office 2007) of always returning to to a userform object after closing a routine in the VBE?

    You know, while debugging or stepping through code and have to interrupt runtime to correct or add additional coding; then BAM! Back to a userform and then have to go find your code (function, sub or ???). I am hoping MS has fixed this intensely aggravating pain in the a**?

  3. doco: Unfortunately, the VBE window is Excel 2007 is exactly the same as the VBE window in the previous version. All of the annoying quirks and PITA bugs are still present. I still get those inexplicable “Device I/O error” messages, and I still see closed workbooks in the Project windows.

  4. I’ve seen the “Closed Workbooks in Projects window” problem before.

    I cured it by removing a misbehaving Addin from the registry location:
    HKEY_CURRENT_USERSoftwareMicrosoftOfficeExcelAddins

    (might pay to check HKEY_LOCAL_MACHINE too)

    To anyone wanting to fool around with the registry, make a backup first.

  5. Tushar: The code produces a number of unsaved workbooks – between 2 and 100 (conceivably, but not really, more like 20). A number of those workbooks are applicable to all of the others because they contain statistical information. For instance, if 10 workbooks are created, maybe two of those workbooks contain “standards” against which the other eight workbooks would be compared for reasonableness. The workbooks are created but not saved. The user can close them without saving, or save some of them, or save them in any order. Basically, I don’t know the name of the “standards” workbook when WorkbookX is saved. WorkbookX has a hyperlink to open the standards workbook(s). The user can’t name the file whatever he wants, so I can guess the filename if it is eventually saved. The FollowHyperlink event uses FileSearch to find the files and open them.

    The file to be opened via the hyperlink contains its own CDP that uniquely ties it to the hyperlink-containing file, so I’ll never open fileAname unless it’s applicable, although fileAname may be in the corpus of files I check for that CDP. The folder I’m checking contains thousands of files, so narrowing down the search is important.

    I don’t know why I don’t use a hidden worksheet. I almost never use a hidden worksheet to store a list of something, but I can’t think of a reason why – other than the reason that I’m “wasting” a whole worksheet when in 98% of the cases I’m only storing one file name.

    This all started as a feature that checked every file in the folder for the magic CDP. That worked great when there was several dozen files. Now it takes nearly a minute – too long. Thanks for the comment.

  6. Dick,

    Though this can probably be optimized a little bit, it checks every substring of the shortest string rather than character-by-character.

    Function Wildcards(ParamArray vaText() As Variant) As String
       
        Dim i As Long, j As Long, k As Long
        Dim sShort As String, sLong As String, sLong2 As String, sLong3 As String
        Dim sTemp As String
       
        Const sQUES As String = “?”
        Const sASTR As String = “*”
           
        ‘If only one string, then return it
       If LBound(vaText) = UBound(vaText) Then
            Wildcards = vaText(LBound(vaText))
        Else
            sShort = vaText(LBound(vaText))
           
            ‘Store the longest and shortest strings
           For i = LBound(vaText) To UBound(vaText)
                If Len(vaText(i)) < Len(sShort) Then
                    sShort = vaText(i)
                End If
                If Len(vaText(i)) > Len(sLong) Then
                    sLong = vaText(i)
                End If
            Next i
            sLong2 = sLong
           
            ‘find longest string in common to all parameters
           For i = LBound(vaText) To UBound(vaText)
                If vaText(i) <> sShort Then
                    sLong3 = “”
                    For j = 1 To Len(sShort)
                        sTemp = “”
                        For k = 1 To Len(sShort) – j + 1
                            If UCase(vaText(i)) Like “*” & UCase(Mid(sShort, j, k)) & “*” Then
                                sTemp = Mid(sShort, j, k)
                                If Len(sLong3) < k Then
                                    sLong3 = sTemp
                                End If
                            End If
                        Next
                    Next j
                    If sTemp = “” Then
                        If Not UCase(vaText(i)) Like “*” & UCase(sLong3) & “*” Then
                            sLong2 = “”
                            Exit For
                        End If
                    End If
                    If Len(sLong3) < Len(sLong2) Then
                        sLong2 = sLong3
                    End If
                End If
            Next i
           
            ‘pad ?s where longest matching substring does not match longest string
           i = InStr(1, sLong, sLong2, vbTextCompare)
            If i = 0 Then
                sTemp = String(Len(sLong2), “?”)
            Else
                sTemp = String(i – 1, “?”) & sLong2 & String(Len(sLong) – _
                    Len(sLong2) – (i – 1), “?”)
            End If
           
            ‘replace three or more ?s with a *
           If Len(sLong) >= 3 Then
                For i = Len(sLong) To 3 Step -1
                    sTemp = Replace(sTemp, String(i, sQUES), sASTR)
                Next i
            End If
                   
            Wildcards = sTemp
        End If
       
    End Function

    I wasn’t sure what the code/plaintext tags were, feel free to format
    Matt

  7. It seems to have cut out a chunk in the middle of my posting.. sorry

    If Len(sLong3) = 3 Then ‘same as above

  8. Dick –

    “I don’t know why I don’t use a hidden worksheet. I almost never use a hidden worksheet to store a list of something, but I can’t think of a reason why – other than the reason that I’m “wasting” a whole worksheet when in 98% of the cases I’m only storing one file name.”

    Hidden worksheets don’t really take up much of your resources, so you’re not “wasting” anything. But if you have only one or two items to store, you could stick them into defined names.

  9. Rob et al:

    Off topic, but on the “Closed Workbooks in Projects window” issue. An attempt or two to resolve this (in March 2006) concluded that “Phantom Workbooks” were created by other than a misbehaving addin. For example, if I remember correctly, I could reliably create one by programmatically opening a workbook (Set Wkb = Workbooks.Open(Path)), executing a dummy routine in it (via Application.Run), closing the workbook (Call Wkb.Close) and releasing its object variable (Set Wkb = Nothing). The workbook then remained as a phantom.

    They’re mostly annoying. Associated objects like ThisWorkbook can’t be selected in the VBE. The “out of memory” messages are, at least, innocuous. What I most objected to was that, although you *could* exceute code in phantom modules, attempting to *remove* them crashed Excel

    I’m relatively convinced that Application.Run is creating a reference within Excel that is not cleared, and I couldn’t then and can’t now see how to clear it. There likely are other ways to do the same thing. But eventually, we gave up. Restarting Excel clears things up, and that had to be a good enough “solution.” (Googling “phantom workbook” in the MS excel programming newsgroup should lead anyone to this interchange.)

    Keith

  10. Keith: I tried to reproduce the problem using the steps you provided. It didn’t reproduce the problem for me.

    I’m positive that the COM add-in we use was causing the problem.

    The non registry way to solve is as follows:
    You need to get the “COM Add-Ins…” button onto your commandbar.
    – right click the commandbar, select Customize…
    – click the commands tab. From categories, choose Tools. From Commands, choose COM Add-Ins.
    – drag COM Add-Ins button to your commandbar. Close the Customize windows. Click the COM Add-Ins button.

    -or-

    To open COM Add-Ins window directly, execute this VBA:
    Application.CommandBars.FindControl(Id:=3754).Execute

    Then manage your Add-Ins by either unticking them, or removing them.

    Rob

  11. Matt –

    Having just researched this, the tags are:

    [ v b ] and [ / v b ]

    all closed together. See the “code in comments” thread.

    …Michael

  12. QC123*p*123 may look better to humans, but QC123??123 would be much more efficient for computers to process. See any text on regular expressions. So it depends on who or what would be the intended user of these patterns.

    Any soundex or approximate matching algorithm could be adapted for this. They could restrict pattern generation to the two strings with the poorest match, and they could be canibalized to locate common substrings.

    Note that generating a pattern for pairs is much easier than generating patterns for 3 or more strings. For example, {abc,bca} -> ?bc?, but {abc,bca,cab} -> *a*, *b*, *c* all equally applicable and inefficient. And if there are no substrings in common, e.g., {abc,defg,hijkl}, what should the return pattern be, just * or ???* ? The latter indicates at least 3 characters. Again a question of whether the generated patterns are intended for human readers or computer processes. If the former, * may be better, but ???* would be much more useful in the latter case.

  13. fzz: I’m talking about situations like {file123A2name,file9A4name}. Where “A” is an important and separates {file123A2name,file9A4name} from {file123B2name,file9B4name}.
    Of course QC123??123 will be more efficient for computer… but I think that that mask isn’t complete.

  14. Okk: In terms of inferring a pattern, there’s a big difference between {“QC123pg123?,”QC123lp123?} and {“file123A2name”,”file9A4name”}. The first requires QC123*p*123 while the latter could be rendered as file?*A?name, where * and ?* take on their COUNTIF/SUMIF meanings: * means ZERO or more characters while ?* means ONE or more characters. Much easier to program detection of one or more characters. That said, there comes a point where only humans could detect patterns.

  15. I cannot fix the phantom workbooks in VBE by clearing the addins in Registry. The problem I have is the books I am trying to close has a reference to the one which calls the closing routine and should shut itself down at the end. However it will not as there is still a reference to it in another workbook albeit a phantom one. Crashes Excel if I close. I have taken all the code out of the phantom books and it still happens also removed the reference. Seems to happed whatever. They are all based on a template so all have the same VB project name, changing this once opened makes no difference. Can anyone give me any more ideas. May be chuck Excel 2007!

Leave a Reply

Your email address will not be published. Required fields are marked *