Search code examples
regexvbauser-inputpowerpoint

Combining Regex and User Input in VBA

I'm trying to allow a user search an array of strings. I want my search pattern to be:

  • Any number of white spaces
  • Followed by the user input
  • Followed by any text after

The following expressions should match:

  1. "ap" to "apple"
  2. " hi" to "hi"

In PowerPoint VBA, I get a syntax error when combining user input with an asterix like such:

Sub RegEx_Tester()


Set objRegExp_1 = CreateObject("vbscript.regexp")

objRegExp_1.Global = True
objRegExp_1.IgnoreCase = True

Dim test As String
    test = InputBox("Give a Section or Presentation")
    strToSearch = test
    objRegExp_1.Pattern = \s*[test]*


    Set regExp_Matches = objRegExp_1.Execute(strToSearch)

    If regExp_Matches.Count = 1 Then
    'Add it to another array. Unimportant for this question.
    End If

End Sub

My regex pattern is highlighted red, and I am getting a syntax error. My question is:

Is it not possible to combine a variable and a Regex? Or have I simply messed up the syntax?

Solution

  • You need to put your string in quotes:

    "\s*[test]*"

    But this isn't quite right either. This will match the letters t, e, or s. You might look at TRIM( text ) which removes leading and trailing spaces. The link I included looks like it only works in Word and Excel, but Powerpoint might have something similar.