vba excel - creating objects within an array

[terriertrip]

I have a dynamic range containing string variables containing numeric values that I would like to remove all non-numeric characters. I've run a loop using a regexp function which takes a few minutes to execute, so I resorted to using an array. My problem is it takes about 30-40 seconds to create the array which is probably the consequence of the regexp function is my guess. My question is, is there a faster way to do this? Currently it takes about 30 seconds to process 50k lines. It's still faster than looping, so I'll take it if this is the fastest way to process a range. I was curious if any of the masters here had any better way to handle this. My code is below.

Please Login or Register  to view this content.

[nankw83]

Try changing the code like below where you use "Early Binding" for the RegEx where you have to enable it from the Visual Basic Editor via Tools --> References --> Microsoft VBScript Regular Expression 5.5

Please Login or Register  to view this content.

[6StringJazzer]

It may not make much speed difference but I would use a simpler pattern:

[^0-9]

[karedog]

... to process 50k lines ...

Arrgghhh .... You create and destroy a very same object 50.000 times

Please Login or Register  to view this content.

[6StringJazzer]

Try changing the code like below where you use "Early Binding" for the RegEx

The big benefit here is not early binding but rather that you are getting instantiation of regEx out of the loop.

[nankw83]

The big benefit here is not early binding but rather that you are getting instantiation of regEx out of the loop.

Totally agree as illustrated by karedog

[Kyle123]

This is one of the times I find Static declarations to be a neat solution:

Please Login or Register  to view this content.

[terriertrip]

Thank you all for your help and input. Been sick with the flu for several days and finally feeling good enough to stare at a screen again.

Wow. Thank you karedog for showing me the error in my ways! After retooling the routine, it pretty much processes it from 23 sec to less than 1 sec now. smh. Not sure if the 'early binding' contributes to the increase in speed beyond keeping it to one object, but I'll take it.

Thanks Jeff for pointing out the pattern. I had no idea you could use the caret like that. Very cool.

[karedog]

You are welcome, thanks for marking the thread as solved and rep.points given.

Regards

[terriertrip]

How would I deal with negative numbers?

[karedog]

Pattern of the regex need to be changed, it depends on your actual data, need to see some sample of them.