化学式から分子量を計算するVBA

参考：エクセル関数に詳しい方化学式から分子量を計算 - エクセル関... - Yahoo!知恵袋

0. 元素記号と原子量を記載したエクセルシートを準備する

1. 下記サンプルコードを該当Excelブックの標準モジュールに組み込み 

2. あるセルに「=MolecularMass(D2,$A$2:$B$119)」のように入力

MolecularMass関数(ユーザー定義関数)の引数は、第１引数が化学式、第２引数が原子量一覧(セル範囲)

■サンプルコード 

Public Function MolecularMass(ByVal argChemicalFormula As String, ByRef argRange As Range) As Double
Dim m_Return As Double, i As Integer
Dim m_Char As String, m_E As String, m_N As String, m_M As String

Call Application.Volatile
For i = 1 To Len(argChemicalFormula) + 1
m_Char = Mid(argChemicalFormula, i, 1)
Select Case m_Char
Case 0 To 9, ".": m_N = m_N & m_Char
If Len(m_E) = 0 Then
If Len(m_M) = 0 Then
m_M = m_Char
Else
m_M = m_M & m_Char
End If
End If
Case "A" To "Z"
If Len(m_E) > 0 Then
m_Return = m_Return + Calculate(m_E, m_N, m_M, argRange)
End If
m_E = m_Char: m_N = ""
Case "a" To "z": m_E = m_E & m_Char
Case Else
If Len(m_E) > 0 Then
m_Return = m_Return + Calculate(m_E, m_N, m_M, argRange)
End If
m_E = "": m_N = "": m_M = ""
End Select
Next
MolecularMass = m_Return
End Function
Private Function Calculate(ByVal argE As String, ByVal argN As String, ByVal argM As String, ByRef argRange As Range) As Double
Dim m_Return As Double, m_Range As Range

Set m_Range = argRange.Resize(, 1)
m_Return = WorksheetFunction.SumIf(m_Range, argE, m_Range.Offset(, 1))
If IsNumeric(argN) Then m_Return = m_Return * Val(argN)
If IsNumeric(argM) Then m_Return = m_Return * Val(argM)

Calculate = m_Return
End Function