Variable length like operator

Question

I realize this code isn't terribly messy and so it probably isn't necessary, but is it possible to condense the lines

Select Case True
    Case Info.Feed Like "F#" Or Info.Feed Like "F##" Or Info.Feed like "F###"
        'stuff; Info is a class variable
    Case Else
        'there are more cases but this is all I need to keep things clear
End Select

into something without an Or? To tell Excel that when I say #, that doesn't mean a one digit number necessarily?

Oh god, I'm a dumbass. How could I forget about that? Thank you. (I mean, I'm still curious if there is an answer to my original question, but...how did I forget that?) — kumquatwhat, Jun 29 '17 at 15:26
I had to discard my answer :D @Nathan_Sav. I would recommend that you put one — Siddharth Rout, Jun 29 '17 at 15:29
Could Feed contain for example.. `FX`? i.e. X is not a number? — CLR, Jun 29 '17 at 15:31

score 3 · Answer 1 · answered Jun 29 '17 at 15:44

3

Info.Feed like "F*" And isnumeric(mid(Info.Feed,2))

This would catch

F01
F100
F99999

But would ignore

F
FX
FX01
F01A
A01

If you want to limit it to F000 to F999, then add a LEN

answered Jun 29 '17 at 15:44

CLR

11,284
1
11
29

score 3 · Accepted Answer · answered Jun 29 '17 at 15:45

3

If you are using the Like operator and hitting its limits then you can use regular expressions in VBA quite concisely. In your case using F* will allow FXXX and F12X etc which I assume you don't want.

F#, F## and F### are all saying F followed by 1 to 3 digits which can be done in a single regex:

^F\d{1,3}$

Which is start of string, an F, 1-3 digits, end of string.

The code:

Option Explicit

Public Sub Test()

    Dim objRegex As Object
    Dim InfoFeed As String: InfoFeed = "F123x"

    Set objRegex = CreateObject("VBScript.RegExp")
    objRegex.Pattern = "^F\d{1,3}$"

    If objRegex.Test(InfoFeed) Then
        Debug.Print "Do stuff"
    Else
        Debug.Print "Do other stuff"
    End If

End Sub

answered Jun 29 '17 at 15:45

Robin Mackenzie

18,801
7
38
56

Er, not to sound like a complete fool here, but what is a regex? It seems really useful, and I'm always happy to learn a new tool to use, but I've never even heard of this before. – kumquatwhat Jun 29 '17 at 15:58
Regex is a kind of advance version of 'Like' - there's a good write up here: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Robin Mackenzie Jun 29 '17 at 16:01

score 1 · Answer 3 · answered Jun 29 '17 at 15:35

1

like "F*" maybe with AND len(x)<=4 perhaps, or simply left(info.feed,1)="F" if it is not length dependant.

answered Jun 29 '17 at 15:35

Nathan_Sav

8,466
2
13
20

Variable length like operator

3 Answers3