regexp in VBA takes a long time to execute

[excelfox1983]

I am using a function I found online that uses regex in VBA to extract cell references from a string. Usually when I run the following code, it is very fast (~.1sec). Sometimes, however, it takes anywhere from 1.0 to 1.5 seconds to run. When it does this, I can run the code over and over again and it always takes this long. Then, randomly, at some point it starts running fast again. I'm not sure if this has something to do with the regex pattern, or with how the regex object is created or handled, or something completely different? I'm really at a loss on this one. Any ideas?? Thank you!

Please Login or Register  to view this content.

[mjr veverka]

Welcome to the forum,

1. Maybe during the execution of the function code you have an overload system (with other calculations/functions) ?
2. Apply maybe early binding for 'RegEx'
3. Rather give up concatenation of text strings and write the results to an array variable instead
And if you must do that, then reduce the number of actions on the text variable:

Insted:

Please Login or Register  to view this content.

try:

Please Login or Register  to view this content.

4. Try the code below - do you also observe the slow running of the code here ?

Please Login or Register  to view this content.

[Rick Rothstein]

[CODE]
c = "A2+B5-C6+V4-G3*H239"

How complex can the formula you are evaluating be? I ask because I think the RegExp pattern given will misidentify function names like HEX2DEC and will also misidentify simple sheet names (such as Sheet2) when referenced by a cell address within a formula.

[jindon]

Please Login or Register  to view this content.

Change it to

Please Login or Register  to view this content.

[hrlngrv]

First, you want the complete workbook-worksheet qualifier with the ! subject to a single ?: ('?(\[[^[\]]+\])?[0-9A-Za-z._ ]+'?!)?. The range portion should be given by \$?[A-Z]{1,3}\$?\d{1,7}(:\$?[A-Z]{1,3}\$?\d{1,7})*. Putting them together,

('?(\[[^[\]]+\])?[0-9A-Za-z._ ]+'?!)?\$?[A-Z]{1,3}\$?\d{1,7}(:\$?[A-Z]{1,3}\$?\d{1,7})*

That is, subject the possible but not necessary workbook name in square brackets to its own ?. Then a worksheet name. As for the range address portion, it handles exotica like C20:E5:K10 is a valid range expression evaluating to C5:K20, the smallest single-area range containing the 3 cells. It also reflects that there could be just 1 to 3 letters and 1 to 7 numerals in each token. Modest efficiency.

While this wouldn't consider DAYS360 to be an address, it would consider LOG10 to be an address. Which gets to the core problem with parsing Excel cell formulas: best to use several collections (returned results from RegExp.Execute), first collecting function calls, then using Mid$ statement calls to replace all of them with srtings of / (a character which CAN'T appear in Windows filenames, worksheet names, or cell addresses, AND isn't a regex special character) of the same length. Do the same for all array constants, double-quoted strings, and boolean and error constants (TRUE, FALSE, #N/A, #VALUE!, etc). Operators, right parentheses, and range addresses would be all that's left, which makes it easier to parse the addresses.

If regex matching is really taking a full second, how much free RAM do you have? Are you using 32-bit or 64-bit Excel? In this case, I suspect 32-bit Excel works better with the DLL which provides VBScript regular expressions.

If you want to handle links into closed workbooks, make the worksheet-workbook portion

('?(([A-Z]:|\\\\[^\\]+)(\\[^\\]+)*)?(\[[^[\]]+\])?[0-9A-Za-z._ ]+'?!)?

[excelfox1983]

Hi porucha vevrku,

I've made the changes you specified, and so far it is running fast. I will monitor and let you know if I notice the slowdown in speed again.

As for the overloading and early binding: I don't think its overloading. There are no other functions or sub routines running at the same time, and there aren't any other programs running on my computer that I am aware of when I run this code. The early binding is something I came across when trying to troubleshoot this issue before. I turned it on and it didn't help the situation.

Thanks for all the input. I really appreciate it!

[excelfox1983]

Rick,

They aren't that complex, so issues like LOG10 and HEX2DEC shouldn't come up.

[excelfox1983]

hrlngrv,

Thanks for the feedback on the regexpattern. I've incorporated your suggestion into my code. As for your other questions, I have 15.7GB of usable RAM installed on my computer, and am running 64-bit excel. As this is a work computer I doubt I have an option to go with 32-bit excel, if that is indeed what would make things better

[Rick Rothstein]

Rick,

They aren't that complex, so issues like LOG10 and HEX2DEC shouldn't come up.

More than likely this is not the case, but if your expressions are as simple as you show (simple algebraic expressions involving only cell address references), then you could simply do it this way...

Please Login or Register  to view this content.

[hrlngrv]

32-bit Excel is a guess. By version number, the Windows Script Host DLL which provides regular expressions hasn't changed for nearly 2 decades, and there wasn't 64-bit Windows much less Excel 2 decades ago.

If you're going to use regular expressions a lot, better to create a VBA project reference to the DLL, then you could use Dim re As New RegExp rather than Create.Object(...).

[hrlngrv]

. . . so issues like LOG10 and HEX2DEC shouldn't come up.

If the pattern is essentially [A-Z]+[0-9]+, that WON'T match HEX2DEC, but it DOES match LOG10 and DAYS360.

. . . if your expressions are as simple as you show (simple algebraic expressions involving only cell address references), then you could simply do it this way...
. . .

Please Login or Register  to view this content.

No provision for absolute references, so $?

As distinct from functions, range addresses must be immediately preceded by an operator (arithmetic or comparison), a comma or a left parenthesis, and be immediately succeeded by an operator, a comma, a right parenthesis, or the end of the formula. It'd be more efficient to use a state machine, searching for the necessary preceding character, then searching for the necessary succeeding character, then checking if what's between them could be a cell address.

That's if one wants to avoid using regular expressions. However, regular expressions would be simpler than mucking about trying to distinguish valid names from necessarily range addresses. Like it or not, ABCD1 is a valid name and not a valid cell address, so you'd need to COUNT letters AND numbers explicitly if you want to avoid regular expressions.

[jindon]

Sometimes, however, it takes anywhere from 1.0 to 1.5 seconds to run.

Please Login or Register  to view this content.

You can't measure accurate time. This is the reason.

[excelfox1983]

hrlngrv,

If you're going to use regular expressions a lot, better to create a VBA project reference to the DLL, then you could use Dim re As New RegExp rather than Create.Object(...).

I altered my code as you suggested to add these two lines of code:
Dim regex As New RegExp
Set regex = New VBScript_RegExp_55.RegExp
It sped the macro a ton by doing this. Seems like something about the Create.Object() was the issue...not sure what was going on there. Before I get too happy though I'm going to run with this code awhile and see if I experience it slowing down again, as like I mentioned in my initial post this is an intermittent issue so not 100% sure if this fixed the issue or if the program just decided to run fast again randomly. Hopefully its the former! Thanks for your advice.

[excelfox1983]

Rick,

Your code works great on the example string I gave, but as you alluded to, the actual strings that I'll be processing are more complicated. I tried running your code on those and it didn't work. Thanks for the suggestion though!