How to speed up extracting numbers from chemical formula-CodePudding

I have been using some useful VBA code by PEH that uses regular expression to extract the number of instances of a specific element in a chemical formula, see:

Note: If you still need a VBA solution then remember you can enter the above formula in the entire range in one go and then convert it to values.

rng.Formula = "=TRIM(TEXTJOIN("""",TRUE,IFERROR((MID(A1,ROW(INDIRECT(""1:""&LEN(A1))),1)*1),"" "")))"
rng.Value = rng.Value

CodePudding user response：

The slowest part of your ChemRegex routine is creating the RegExp object.

If all your cells are passed to CountElements as a pair of large areas move the code that creates the RegExp object and applies a few properties from ChemRegex to CountElements, and pass the RegExp reference from CountElements to ChemRegex.

Or, if you are calling CountElements as say a UDF in multiple cells, declare RegExp at module level

Private RegEx as RegExp

In CountElements...

If RegEx is Nothing Then
    Set RegEx = New RegExp
    ' apply the properties
End If
' code
' and pass RegEx to ChemRegex
Call ChemRegex(ChemFormula, Element, RetVal, RegEx)