I am trying to develop a UDF that will calculate the mass of a formula. In order to do that I need to be able to extract the element and it's associated number (e.g. C and 20). If the string C20H22 is in cell A1 and Emass(A1) is entered into A2 the result of 240 is correctly shown in cell A2 (mass of C = 12, 20 * 12 = 240) when the top level Do While Loop is made inactive by making it a comment (as shown below). However if the top level While-Loop is uncommented the function fails. The top level loop is supposed to go through the entire formula and revise the mass as a new element and number is found. Any ideas why the top level Do While Loop is causing the UDF to fail?


Function EMass(ChemFormula As String) As Double

On Error GoTo FuncDied:

Dim Elem As String
Dim ElemNumber As Integer
Dim ElemMass As Double
Dim TempMass As Double
Dim i As Integer
Dim n As Integer

i = 1
n = 1

'Do While i <= Len(ChemFormula)

    Do While IsCap(Mid(ChemFormula, n, 1))

    Elem = Elem & Mid(ChemFormula, n, 1)

    n = n + 1

    Loop
    
ElemMass = Application.VLookup(Elem, ThisWorkbook.Worksheets("DataBase").Range("table"), 2, 0) ' Find element and get mass
    
    Do While IsNum(Mid(ChemFormula, n, 1))

    ElemNumber = ElemNumber & Mid(ChemFormula, n, 1)

    n = n + 1

    Loop

TempMass = ElemNumber * ElemMass + TempMass

i = n + 1

'Loop

EMass = TempMass

Exit Function

FuncDied:
  mySum4 = CVErr(xlErrValue)

End Function

Private Function IsNum(letter As String) As Boolean ' Determines if a character is a number
    '48 (0) to 57 (9)
    If Asc(letter) > 47 And Asc(letter) < 58 Then
        IsNum = True
    Else
        IsNum = False
    End If
End Function

Private Function IsCap(letter As String) As Boolean ' Determines if a character is a uppcase letter
    '65 (A) to 90 (Z)
    If Asc(letter) > 64 And Asc(letter) < 91 Then
        IsCap = True
    Else
        IsCap = False
    End If
End Function