Use JavaScript regex to capture all string values from VBA code

Question

I want to use JavaScript regex to list all string values used in VBA code.

Example:

If cmd = "History Report" Then
     Range("D2").Formula = "=TEXT(""" & createDate & """,""" & Replace(subtitleFormat, """", """""") & """)"
     comment = "This task is completed!"
End If

The result will be:

1> "History Report"
2> "D2"
3> "=TEXT("""
4> ""","""
5> """"
6> """"""
7> """)"
8> "This task is completed!"

I'm so glad to have some better ideas to solve this problem.

You can't use JavaScript regex in VBA. You can use the Microsoft Scripting Runtime library to use VBScript-flavor regex though. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 2, 2015 at 18:23

vks · Accepted Answer · 2015-06-02 19:05:14Z

2

""*[^"]*"*"

Try this.See demo.

https://regex101.com/r/pG1kU1/19

Edited:

"(?:"{2})*[^"]*(?:"{2})*"

Try this.See demo.

https://regex101.com/r/pG1kU1/23

edited Jun 2, 2015 at 19:05

answered Jun 2, 2015 at 18:15

vks

68.1k11 gold badges96 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Doug B Over a year ago

@ZuZu Please mark vks' solution as the answer if it worked for what you needed.

Zu Zu Over a year ago

I have fixed a mistake with example. In VBA, string value can contain double quote by double up on the quotes. I want to capture whole string that can contains double quote such as: "I need ""your help"" in this problem".

Wiktor Stribiżew Over a year ago

@ZuZu: I have a regex solution that will correctly deal with the VBA string literals.

Wiktor Stribiżew · Accepted Answer · 2015-06-02 18:57:11Z

1

You need to account for double quotation marks inside string literals:

"((?:""|[^"])*)"

This will match all them. Capturing group 1 will hold the strings themselves. If you want quotes to be part of your results, just use "(?:""|[^"])*".

See demo.

Another "unrolled" regex for this task:

"((?:[^"]*(?:""[^"]*)*))"

edited Jun 2, 2015 at 18:57

answered Jun 2, 2015 at 18:52

Wiktor Stribiżew

631k41 gold badges503 silver badges633 bronze badges

1 Comment

Wiktor Stribiżew Over a year ago

Unrolled version is 43% faster than the one with alternatives.

Collectives™ on Stack Overflow

Use JavaScript regex to capture all string values from VBA code

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related