15,843,937 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick

# Excel Molar Mass Calculator - Molecular Weight

Rate me:
8 Mar 2020CPOL1 min read 35.7K   1.1K   6   11
Excel, Pure VBA Macro: Molecular Weight/Molecular Mass 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. We go over how we store elements masses in a normal VBA keyed collection. The calculation is done via pure VB code without any additional reference or library with extremely simple code.

## Introduction

### 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 Molar Mass

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.

VBScript
```Private Sub SetAtomicMass()
On Error GoTo ErrorHandler
Rem and so on to
Exit Sub
ErrorHandler:
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.

VBScript
```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
Next
PartMass = PartCount * PartMass
Mass = Mass + PartMass
Next
MolecularWeight = Mass```

## Related Articles

Written By
Lebanon
ASP.Net Hosting on Linux server
---------------------------
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

 First Prev Next
 Doesn't work if parentheses not at the end Member 156989436-Jul-22 8:52 Member 15698943 6-Jul-22 8:52
 Excel sheet is password protected Member 1543909321-Nov-21 7:37 Member 15439093 21-Nov-21 7:37