VBA: When is * usable?

Question

I'm trying to get a grasp on when I can and cannot use * to generalize while coding.

An example of what I mean is xfile.* if the extension does not matter. Another would be *.xls if I want to refer to any and all excel files.

I'm not just interested in files though. I want to use something like Washington* and Oregon* if I want all sheets in a workbook regardless of what comes after, be it This Month, This Year, etc.

Another level would be values in cells on a spreadsheet.

I ask for each of these specifically because * seems to be treated differently in each scenario.

Thanks for the help.

---

EDIT:

A good example of an issue I just ran into is in this code:

If ActiveSheet.Name <> "City*" Then
    code
End If

The options for worksheet names are City MTD, City YTD, Country MTD, and Country YTD (Month to Date and Year to Date, fyi)

The sheet i'm on is City MTD but my program still enters the If-Statement. This leads me to believe that * is not being treated as a wildcard, but rather as a literal asterisk in the string.

Answer 1

Here is how you would use * to access all sheets whose names fit a certain pattern:

Sub test()
    Dim ws As Worksheet
    Dim count As Long

    For Each ws In ActiveWorkbook.Sheets
        If LCase(ws.Name) Like "*data*" Then count = count + 1
    Next ws
    Debug.Print "There are " & count & " sheets with 'data' in their name"

End Sub

When I run this on a workbook that has 1 sheet named "Raw Data" and another "Processed Data" (and some other sheets that don't contain "data" anywhere in them) I get:

There are 2 sheets with 'data' in their name

* is useful for many purposes, but is somewhat limited. For more complicated problems it is advisable to use VBScript's regular expression object -- which can be also be used in VBA (if you add the right reference to the project).

Answer 2

List with methods found for * wildcard character

.

Range .Replace Method: UsedRange.Replace "test*", "NewValue"

Range .AutoFilter: Range("A:A").AutoFilter Field:=1, Criteria1:="test*"

Like Operator (compares strings): If Range("A1") Like "test*" Then

Files and Folders Methods:
    Copy
    CopyFile
    CopyFolder
    MoveFile
    MoveFolder
    DeleteFolder
    DeleteFile
    Dir Function (searches for files or folders)
    ChDir Statement (changes current folder)
    Kill Statement (deletes files from disk)

Application Methods
    .GetSaveAsFilename (used for file extension only)
    .GetOpenFilename (used for file extension only)
    .Match "test*", Range("A:A"), 0 '(If match_type is 0 and lookup_value is text)

WorksheetFunction Methods:
    .AverageIf and .AverageIfs
    .CountIf and .CountIfs
    .Find and .FindB (Range("A1").Find "*")
    .Match
    .Search and .SearchB (locate one text string within a 2nd string)
    .SumIf and .SumIfs
    .VLookup and .HLookup

FileDialog Object - .InitialFileName Property

VBScript.RegExp (or reference to "Microsoft VBScript Regular Expressions *")

Scripting.FileSystemObject CopyFile and DeleteFile Methods ("Microsoft Scripting Runtime")

.

It can be escaped with a tilde (~) character: Range("A1").Find "~*" finds * (.Find "~~" finds ~)

.