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
Bookmarks