如果已經有人問過并回答過這個問題，但我找不到滿意的答案，我深表歉意。
我有一個包含數千個化學式的串列，其中可能包含任何元素的符號，我想確定每個化學式中任何元素的原子總數。例子包括：
- CH 3 NO 3
- CSe 2
- C 2 Cl 2
- C 2 Cl 2 O 2
- C 2 Cl 3 F
- C 2 H 2 BrF 3
- C 2 H 2 Br 2
- C 2 H 3 Cl 3 Si
我只想要單個公式中的原子總數，因此對于第一個示例（CH 3 NO 3），答案是 8（1 個碳 3 個氫 1 個氮 3 個氧）。
我發現了 PEH（
還可以識別帶有前綴的化學式，如 Ca(OH)2
如果您需要更精確的方法（檢查元素的存在）并識別括號，則需要再次使用 RegEx 進行。
因為 VBA 不支持開箱即用的正則運算式，所以我們需要先參考 Windows 庫。
在工具下添加對正則運算式的參考，然后在參考
并選擇Microsoft VBScript 正則運算式 5.5
將此功能添加到模塊
Public Function ChemRegexCountTotalElements(ByVal ChemFormula As String) As Long Dim RetVal As Long Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False End With 'first pattern matches every element once regEx.Pattern = "([A][cglmrstu]|[B][aehikr]?|[C][adeflmnorsu]?|[D][bsy]|[E][rsu]|[F][elmr]?|[G][ade]|[H][efgos]?|[I][nr]?|[K][r]?|[L][airuv]|[M][cdgnot]|[N][abdehiop]?|[O][gs]?|[P][abdmortu]?|[R][abefghnu]|[S][bcegimnr]?|[T][abcehilms]|[U]|[V]|[W]|[X][e]|[Y][b]?|[Z][nr])([0-9]*)" Dim Matches As MatchCollection Set Matches = regEx.Execute(ChemFormula) Dim m As Match For Each m In Matches RetVal = RetVal IIf(Not m.SubMatches(1) = vbNullString, m.SubMatches(1), 1) Next m 'second patternd finds parenthesis and multiplies elements within regEx.Pattern = "(\((. ?)\)([0-9] ) ) ?" Set Matches = regEx.Execute(ChemFormula) For Each m In Matches RetVal = RetVal ChemRegexCountTotalElements(m.SubMatches(1)) * (m.SubMatches(2) - 1) '-1 because all elements were already counted once in the first pattern Next m ChemRegexCountTotalElements = RetVal End Function
雖然此代碼也能識別括號，但請注意，它不能識別嵌套括號。
uj5u.com熱心網友回復：
這是我的兩分錢
公式C1：
=ChemRegex(A1)
哪里ChemRegex()呼叫：
Public Function ChemRegex(ChemFormula As String) As Long
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "[A-Z][a-z]*(\d*)"
If .Test(ChemFormula) Then
Set matches = .Execute(ChemFormula)
For Each Match In matches
ChemRegex = ChemRegex IIf(Match.Submatches(0) = "", 1, Match.Submatches(0))
Next
Else
ChemRegex = 0
End If
End With
End Function
或者在（較短的）兩步正則運算式解決方案中：
Public Function ChemRegex(ChemFormula As String) As Long
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "([A-Za-z])(?=[A-Z]|$)"
ChemFormula = .Replace(ChemFormula, "$1-1")
.Pattern = "\D "
ChemFormula = .Replace(ChemFormula, " ")
ChemRegex = Evaluate(ChemFormula)
End With
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/328280.html