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
|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")
Debug.Print ("Error in: MMW.SetAtomicMass." & Erl & vbNewLine & Err.Description)
Call MsgBox("Error in: MMW.SetAtomicMass." & Erl & vbNewLine & Err.Description)
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
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.