top of page
  • John

Extract literal values (Strings, numerics, dates) to a constant in VBA

Updated: Sep 12, 2023

VBE_Extras includes functions for 'Matches for literal value at cursor' and 'Extract to constant' which are closely related.


The former will show you, when the cursor is on a literal value (String, numeric or date), just like it sounds, all of the matching literal values in the VBA project you are working on. For example with this code:

If you put the cursor on any one of the three instances of "Hello World" then select the 'Matches for literal value at cursor' menu option, the result is this dialog:

You can restrict the scope (Project, Module, Procedure/Property) of the results and filter in/out literals that are in code comments and, if the literal is a String, whether results are case sensitive or not. You can also create a CSV of the results and you can select any one of the literal values and 'Goto' it.


While that's quite handy, if you put the cursor on any one of the three instances of "Hello World" then select the 'Extract to constant' menu option, the result is this dialog:

In which various options can be selected (clicking on 'Matches' shows the same dialog as clicking on 'Matches for literal value at cursor'), then clicking 'Extract' results in the above code being updated to:

Now, I know that the VBE has Find/Replace functionality which will pretty-much do the same ... but it won't:

  • Show you the list of matching literal values in advance ... you have to step through each result individually

  • Add the Const declaration

  • Ensure that the Const declaration is scoped appropriately for all of the literal values being 'extracted'

Just think of the time saving ... then get a free trial of VBE_Extras

0 comments

Comments


bottom of page