Click here to Skip to main content
14,454,912 members

Excel Molar Mass Calculator - Molecular Weight

Rate this:
4.13 (5 votes)
Please Sign up or sign in to vote.
4.13 (5 votes)
10 Jun 2019CPOL
Excel, Pure VBA Macro: Molecular Weight/Molecular Mass Calculator


What is Molecular Mass

Molecular mass or molecular weight is the mass of a molecule. It is calculated as the sum of the atomic masses of each constituent element or group of elements multiplied by the number of it in the molecular formula.
Some formulas are quite simple CaCO3 but there are complex formulas such as Co3[Fe(CN)6]2.4NH3.6H2 and some organic compound is much complex.

Calculating Molecular Weight Calculator

In this article, we will explain how to write a pure Excel VBA macro to calculate Molecular Weight or Molecular Mass Calculator of a chemical formula.

The calculation is done via pure VB code without any additional reference or library with extremely simple code
Error handler and line numbering is added to simplify debugging the code.

Using the Code

Download and open Excel file and edit it as needed. The mass will be updated once the formula changes
to calculate a formula in a cell, just type something like this =MW("CaCO3").

Formula Molecular Weight
CuSO4 159.6
CaCO3 100.1
Co3[Fe(CN)6]2 . 4NH3 . 6H2O 776.9
2Na2CO3 . 3H2O2 314.0
Na2CO3 . 1.5H2O2 157.0
NaBO2 . H2O2 . 3H2O 153.9

How It Works

First of all, we store elements masses in a normal VBA keyed collection.

Private Sub SetAtomicMass()
        On Error GoTo ErrorHandler
1:      Call Masses.Add(1.00794!, "H")
2:      Call Masses.Add(4.002602!, "He")
3:      Call Masses.Add(6.941!, "Li")
4:      Call Masses.Add(9.012182!, "Be")
5:      Call Masses.Add(10.811!, "B")
Rem and so on to
118:    Call Masses.Add(294, "Og")
        Exit Sub
        Debug.Print ("Error in: MMW.SetAtomicMass." & Erl & vbNewLine & Err.Description)
        Debug.Assert (False)
        Call MsgBox("Error in: MMW.SetAtomicMass." & Erl & vbNewLine & Err.Description)
End Sub

To calculate mass of NaBO2 . H2O2 . 3H2O, we split it into part, NaBO2 , H2O2 , 3H2O.
We split each part like H2O into groups H2 and O.
Calculate the mass of each group and add them together to get the total mass.

Parts = Split(GetParts(Formula), ", ")
For Each Part In Parts
    PartCount = GetPartCount(Part)
    PartSymbol = GetPartSymbol(Part)
    PartMass = 0
    Groups = Split(GetGroups(PartSymbol), ", ")
    For Each Group In Groups
        GroupCount = GetGroupCount(Group)
        GroupSymbol = GetGroupSymbol(Group)
        SymbolMass = GetSymbolMass(GroupSymbol)
        If SymbolMass = 0 Then
            MolecularWeight = 0
            Exit Function
        End If
        GroupMass = GroupCount * SymbolMass
        PartMass = PartMass + GroupMass
    PartMass = PartCount * PartMass
    Mass = Mass + PartMass
MolecularWeight = Mass


This article was a part of my article Office programming helper article and has been moved here.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Lebanon Lebanon
There is a developer behind every piece of code!
DNA is too complex what about it!
No junk DNA; There is a functional role for noncoding DNA

Comments and Discussions

QuestionThis apparently ignores foreign number formats... Pin
Aendie Bauer10-Jan-19 6:44
MemberAendie Bauer10-Jan-19 6:44 
AnswerRe: This apparently ignores foreign number formats... Pin
NewPast15-Mar-19 11:00
MemberNewPast15-Mar-19 11:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 8 Jan 2019


4 bookmarked