VBA Regex issue

Question

Hey guys,

has VBA any good mechanism for checking, if the content of a given Excel Cell matches a specific regex?

In my case i want to know, if some cell has the format

m
m2
m1234

In fact, there's just one defined letter at the beginning, followed by a not specified amount of numbers.

How do I put this into an If-Else construct?

If Doc.Cells(1,1).Value ..... ???

greets, poeschlorn

Answer 1

I don't know VBA, but the regex [a-zA-Z][0-9]* might be able to match what you want.

Answer 2

You can get at the VBScript RegExp objects via Tools->References & adding "Microsoft VBScript Regular Expressions 5.5"

Alternatively a quick way to do it, if you don't need to check for a subsequent letter as in `m1234X1 is:

if Doc.Cells(1,1).Value like "[a-zA-Z]#*" then ...

(This doesn't require a reference to anything)