Creating Excel Add-ins: Molecular Weight Example

[Natural ChemE]

Code in this thread is obsolete.
Please see Creating Excel Add-ins: Molecular Weight Example (Update #1) for updates, new information, and leaving new comments.

We often use the same functions over and over again, such as sine and cosine, so they’re programmed into everything from calculators to computer programming languages to spreadsheets. In Excel, you can write your own functions. In this thread, I’ll give a quick "How To" for Excel 2007/2010 and provide an example add-in which calculates molecular mass.

EDIT 1: If anything would benefit from a clarification or edit, please let me know by commenting below!

EDIT 2: Added support for isotopes at the end of this post. The code in this first post is the most up-to-date and using the code in the discussion following this first post is not recommended.

What’s a function?
In Excel, a function can be called to perform a calculation or task. For example, the sine function sin(x) returns the sine of its single argument x.

The sine function used in Excel. Note that Excel calculates 1.2+2^2=5.2 before passing 5.2 to the sine function.

Enabling Excel’s ‘Developer’ Tab
The Developer tab, which is hidden by default, is necessary to program stuff into Excel. If yours is already showing, you can skip ahead.

For Excel 2007: http://www.asap-utilities.com/blog/inde ... oper-mode/
For Excel 2010: http://www.excel-2010.com/the-developer ... xcel-2010/
For Excel 2011: http://www.gilsmethod.com/how-to-enable ... excel-2011

Writing your First Function
Once the Developer tab is enabled, select it and then select the "Visual Basic" button. The Visual Basic editor should pop up.

Next, create a new module. In the "Project - VBAProject" panel, right-click "Microsoft Excel Objects", and select Insert -> Module.

Then insert the code:

Code: Select all

Function MyAverage(firstNumber As Double, secondNumber As Double)
    MyAverage = (firstNumber + secondNumber) / 2
End Function

Now that it’s written into a module, it’ll work for the current spreadsheet that you have open. If you save this spreadsheet, it’ll work for anyone that opens it and enables macros. (Note that it’ll have to be saved as an *.xlsm file instead of as an *.xlsx file if you want the code to work.)

In any cell, you can now use your new function as MyAverage(x₁, x₂).

Code: Select all

=MyAverage(1,2)

A More Complex Function
Chemists often have to find the molecular mass (or molecular weight) of a chemical species. This is done by adding up all of the weights of the component atoms of the species. For example, water (H₂O) has two hydrogen atoms and one oxygen atom, so you can pick up a periodic table, find the mass for hydrogen, double it, and add the mass of oxygen, and that’s your molecular mass. This can become tedious, particularly for more complex chemicals, i.e., [P(C₄H₉)₃C₁₄H₂₉]Cl.

A function mw(x) can be created to compute the molecular mass (aka molecular weight) automatically. This is a more complex function, and its code is attached below.

MW Code.txt

(21.12 KiB) Downloaded 1765 times

Obsolete: Please see Creating Excel Add-ins: Molecular Weight Example (Update #1) for newer code with a bug fix, newer values, and new features.
To use this code, directly copy/paste this code into a module. The function mw() should now work for that spreadsheet. Note that mw(H2) gives the molecular mass of whatever the cell H2 has in it, not the molecular mass of H₂. To directly enter a molecule’s formula, put quotes around it - i.e., mw("H2").

As for syntax, it’s

Code: Select all

=mw("[P(C4H9)3C14H29]Cl")

for the molecular mass of [P(C₄H₉)₃C₁₄H₂₉]Cl.

Note that the function considers ( and [ to be the same thing, and likewise for ) and ]. Also, there's no practical limit to the number of ()'s you can use.

Making the Code into an Add-in
The functions that you’ve just written work for only the spreadsheet that you’ve put them into. If you save it (as an *.xlsm file) and send it to someone, they’ll be able to use those functions in that spreadsheet too.

However, if you want the functions to be automatically available to you in all of your spreadsheets, you can save them as add-ins. Functions in your add-ins will work for all spreadsheets that you open up on your computer until you disable or delete the add-ins.

To save the current code as an add-in, simply go to Save As and, under file type, select "Excel Add-In (*.xlam)". The add-in should be saved in specific folder which Excel will go to by default.

Once the add-in is saved, it still needs to be enabled. Enabling the add-in:
Excel 2007: http://peltiertech.com/WordPress/instal ... xcel-2007/
Excel 2010: http://www.dq.winsila.com/tips-tricks/m ... -2010.html

Once enabled, the functions will work on all of your spreadsheets!

Note
For those who just wanted to get an introduction to programming Excel add-ins, you can stop here. The following section relates to adding in functionality for isotopes. No additional programming skills will be gained; it's just adding code like before and explaining how to use it.

Isotope Variation
The code just installed will give you the prevalence-weighted molecular weight, just like what you find on a periodic table. However some users will want the values of specific isotopes. For this, just go to the Visual Basic button in Excel’s Developer tab again, make a new module (as you did before), and copy paste in the code in this file:

MW Code - Isotope mod v3.0.txt

(203.79 KiB) Downloaded 405 times

Obsolete: Please see Creating Excel Add-ins: Molecular Weight Example (Update #1) for newer code with a bug fix, newer values, and new features.
Now you have a new function, mwi(chemicalSpecies, useMostCommonIsotopes). This function has more in it (as you can tell from the significantly larger file size), providing isotope data. Use it just like mw(chemicalSpecies) as we did before, EXCEPT:

1. You can use an exclamation point and then a number before an element symbol to specify a specific isotope.
Example: =mwi("!1H") gives the molecular weight for ¹H.

2. You can use a "true" argument in mwi() to have all element symbols which haven’t had their isotope specified default to their most common isotope. (Note: Putting "FALSE" in is purely optional since it’ll default to FALSE if you don’t put anything.)
EX: mwi("H", TRUE) gives the molecular weight for ¹H, since ¹H is the most common isotope of hydrogen.
EX: mwi("H") and mwi("H", FALSE) give the prevalence-weighted molecular weight of hydrogen, just like mw("H").

Other examples:
EX: mwi("!2H2O", TRUE) and mwi("!2H2!16O") would both give the molecular weight for ²H₂¹⁶O.
EX: mwi("!2H2O") and mwi("!2H2O", FALSE) would both give the molecular weight for regular heavy water, ²H₂^avO.
EX: mwi("!1H!2HO") would give the molecular weight for ¹H ²H ^avO.

The examples use very simple formulas for the sake of simple demonstration, but the code can easily handle far more complex chemicals. In general, specify the isotopes you want, then "TRUE" if you want the rest to be the most common isotopes for their species or "FALSE" if you want the rest to be prevalence-weighted.

Licensing
As the original author, I hereby release code attached in this post to the public domain. I would ask, though it is optional, that a link back to this thread be provided. No further attribution is required.

Acknowledgements
NIST for providing the isotopic weights used.
Microsoft for providing the Office platform.
Providers for the linked resources in the guide above.
The SPCF for hosting this thread.
Members commenting below for their contributions, comments, and feedback.

Bugs and Errata
2014 03 05 - chebob512 has noted that all versions of the add-in through the current date incorrectly add the digits for uncertainty to the end of the digits for atomic weights. In most cases this results in a small-ish error, e.g. for Hydrogen the atomic weight 1.007947 was used instead of the actual 1.00794, but it will be corrected in an upcoming version.
2013 05 13 - It was noted that the second and third isotopes of hydrogen, ²H and ³H, were not recognized when the user referred to them as "!2H" or "!3H". This is because they were referred to as "!2D" or "!3T", since those isotopes are usually named. Versions 3.0 and later will now accept "!2H" and "!3H" as valid names for these isotopes. "!2D" and "!3T" still work too.

[Natural ChemE]

The introduction in the thread above describes how to create an add-in and provides the code for the molecular weight add-in. If anyone enjoys programming and wants a challenge, they can try modifying the add-in.

Challenges:
Add in common functional groups
Chemical formulas can include shorthand for common functional groups. For example, "Me" is a common shorthand for CH₃. How can this be added in?
Difficulty: Easy

Take ionization into account
As written, the code automatically disregards charges. For example, the sodium ion Na⁺ is considered as weighing the same as a sodium atom Na. In truth, an ion should have added to its weight, where is the charge of the ion and is the mass of an electron. How can you modify the code to take this into account? (Hint: The Sanitize function would need to be modified to stop destroying the +'s and -'s.)
Difficulty: Moderate

Optimize the code
This code was typed quickly at 1am last night. It is not optimized. How can it be optimized?
Difficulty: Moderate

Be creative!
Can you modify or extend the code to do something useful or interesting?
Difficultly: Variable

[BchmRebel]

Just for those of us that are computer illiterate (sometimes), I would point out that the program is case sensitive. Super easy to get set up, and then the nerd in me just started typing in random chemical formulas. Needless to say, I realized after a very complex formula that it's case sensitive. Not all of us are oh so pro! :) By the way, my very complex formula was water. Did you know the density of water is 55.56 M? So pro, right? :P

[Natural ChemE]

NOTE: The code below is retained for historical reasons. The code at the top of this thread, in the first post, is the most up-to-date.

It's been requested that this add-in be modified to output molecular weights using specific isotopes. The modified code is here:

MW Code - Isotope mod.txt

(21.41 KiB) Downloaded 467 times

Obsolete: Please see Creating Excel Add-ins: Molecular Weight Example (Update #1) for newer code with a bug fix, newer values, and new features.
Now if the user inputs a string such as "CH3_13CH2CH3", the center carbon will enter the AMU function as "13C" and can have its own return value. "12C" can have its own return value as well. The question is then whether "C" should also refer to 12C or still be the prevalence-weighted atomic mass. The AMU function isn't populated with the new isotope values yet, so that'll still have to be done.

Also, it should be noted that the new function is called "MWI" instead of "MW" so that my computer didn't confuse the two. If you use both, the new code should be placed in a new module (see third picture in the original post) instead of being in the same module as the previous "MW" code.

[Natural ChemE]

NOTE: The code below is retained for historical reasons. The code at the top of this thread, in the first post, is the most up-to-date.

Update: The isotopic version of this add-in, MWI, has been extended with all of the isotopic values from NIST.

MW Code - Isotope mod - Isotopes filled in.txt

(196.66 KiB) Downloaded 468 times

Obsolete: Please see Creating Excel Add-ins: Molecular Weight Example (Update #1) for newer code with a bug fix, newer values, and new features.

This was a fun one. NIST had enough isotope data that I had to write a program to datamine it out. Then when trying to run the program that the first program wrote, Excel tossed an error at me that the AMU function was too long (note that the file size is nearly ten times larger!). So, I rewrote the first one to rewrite the second one as a series of functions. Now AMU is a big switch function with 17 sub-functions AMUPi, where i ranges from 0 to 16. =P

Anywho, geekiness aside, it's working and should have the best values currently available for the isotopes.

[piejem]

Great! Grea! job ChemE.

Many many thanks!
I'll use it and see wether there maight be some additions!
Maybe look for a wider forum to publish it?

may many thanks,

PieJeM

[xcthulhu]

NaturalChemE:

What license are you releasing this under? Creative Commons?

You might consider posting this on github.com - that way if someone wanted to make modifications they could easily fork and hack your code...

~XCT

[kidjan]

Yeah, good point. If you don't attach a license to it, then it actually makes it difficult for people to use because the default license can be legally restrictive. So you might want to go creative commons or public domain. Or, if you care who uses it an how they use it, some of the more copy-left licenses are a good way to go.

[Natural ChemE]

PieJeM,

Sorry for the late response; been a bit slammed lately. If you'd like to distribute the add-in, please, feel free! All I'd ask is that, when possible, a link back to this thread is provided. We're always trying to get clever folks to join us here at SCF, and hopefully you'll consider sticking around too!

[Natural ChemE]

xct, kidjan,

Thanks for the advice guys. I'll go ahead and just release the code to the public domain as-is. I make no assertions as to the quality, accuracy, etc. of this code. Anyone who intends to use this code does so at their own risk, and it's generally advisable to go over the code to ensure its accuracy before using it.

This said, I'll make the good faith claim that the code has consistently returned correct values for me during a variety of trials and I have no reason to believe that there's any fault with it (although it could probably be stand to be optimized).

[Tripmitz]

Great effort here.

Just a quick note that the calculator as is calculates the average molecular weight of a species by using the average molecular weight of an element. Great for use when calculating moles etc.

However, calculation of the monoisotopic weight is much more valuable to a mass spectrometrist.

In this case it is fairly easy to modify the text file/add-in by substituting the mass of the elements with the most common isotope. This works reasonably well for low molecular weight species and those only containing the most common elements with few naturally occuring isotopes.

It would be great to see this further developed into a monoisotopic weight calculator or a isotopic distribution calculator - as a non-programmer I don't know how feasible this is though!

Cheers

[Natural ChemE]

Tripmitz,

Welcome to the forums!

You know, piejem had made similar comments. I've added an improved isotope-handling version of this code to the original post as well as instructions for its installation and use. Please let me know if this is what you had in mind!

[yannickv]

Thank you for the script, it is really useful for me. I'm doing a Ph. D. on Mass Spectrometry, and often need to calculate the molecular mass of ions in Excel.

As I need the charge of the ion to be taken into account in my project, I will give it a try to implement it in your script. I will post the customized code once I got it working (I have some programming skills, but VBA is totally new for me...)

[Natural ChemE]

yannickv,

Welcome to the forums!

I’m glad to hear that you’ll be trying to improve the code! Please let me know if there’s anything that I can help you with in it.

I like VBA in some ways. The debugging on it’s not quite as good as that in a good IDE, but it’s useful since you can directly interact with things like Excel spreadsheets, whether it’s making a function like this one for the molecular mass or making an interactive program with the ActiveX controls.

Just as first thoughts on this, the Sanitize function will need to be modified to stop destroying +’s and -’s (which is as simple as deleting the cases for them in the switch statement). And you’d probably want to come up with a syntax convention before you start coding, i.e., what’s the general rule for typing all possible types of ions that the function should handle?

[Natural ChemE]

Updated the isotope code from version 2.0 to version 3.0.

I realized that I forgot to include the isotopes ²H and ³H in the isotope version of the code. If you tried to explicitly specify one of these isotopes using "!2H" or "!3H", the function would've reported an error.

They were actually still there as ²D ("!2D") (since ²H is deuterium) and ³T ("!3T") (since ³H is tritium), but now "!2H" and "!3H" also work.

[yannickv]

Hi

I made some slight modifications so the function takes into account the charge. You can type a + or - symbol in the formula, and the sign will be interpreted like an atom with a certain mass.
Note that this only works for mono-charged ions, which is always the case for the type of mass spectrometry I'm doing (static secondary ion mass spectrometry).

MW Code Charge.txt

(203.38 KiB) Downloaded 278 times

Obsolete: Please see Creating Excel Add-ins: Molecular Weight Example (Update #1) for newer code with a bug fix, newer values, and new features.

[Natural ChemE]

yannickv,

Awesome, thanks!

I ended up stopping a bit short of completing the add-in. The solution that I had in mind for implementing charges was…

1. Remove these lines from the SanitizeChemicalFormula function:

Code: Select all

Case "+"  'remove
    If i = 1 Then
        tempForm = Right(tempForm, Len(tempForm) - 1)
    ElseIf i = Len(formula) Then
        tempForm = Left(tempForm, Len(tempForm) - 1)
    Else
        tempForm = Left(tempForm, i - 1) & Right(tempForm, Len(tempForm) - i)
    End If
Case "-"  'remove
    If i = 1 Then
        tempForm = Right(tempForm, Len(tempForm) - 1)
    ElseIf i = Len(formula) Then
        tempForm = Left(tempForm, Len(tempForm) - 1)
    Else
        tempForm = Left(tempForm, i - 1) & Right(tempForm, Len(tempForm) - i)
    End If

Reason: The SanitizeChemicalFormula function sanitizes user input, putting it into a form that the rest of the function can more readily read. Part of this was removing the plus and minus signs from the formula. But since the improved function requires these, we have to stop removing them.

2. Change the IsCap function to this:

Code: Select all

Private Function IsCap(letter As String) As Boolean
    '65 (A) to 90 (Z), 43 (+), 45 (-)
    ASCNumber = Asc(letter)
    If (ASCNumber > 64 And ASCNumber < 91) Or ASCNumber = 43 Or ASCNumber = 45 Then
        IsCap = True
    Else
        IsCap = False
    End If
End Function

Reason: Reading molecular species requires telling if a letter if capital or not to know when a new element starts. For example, “CO” is carbon monoxide, but “Co” is cobalt. The code used IsCap to determine if a new element was starting. By making it return true for + (43) and - (45) (ASCII table), the function now recognizes “+” and “-” as capital letters, so they now start element strings.

3. Add elements “+” and “-” to an AMU lookup function. Since the basic AMU function is present in both the basic and isotopic versions of this code, it can be added there to update either:

Code: Select all

Case "-"
    AMU = 5.485799094622E-04
Case "+"
    AMU = -5.485799094622E-04

Reason: Now when the function tries to look up “+” and “-”, it’ll find their effective masses. Note that a “+” charge denotes the absence of an electron, so its effective mass is the negative of the mass of an electron.
Note: Wikipedia lists the electron mass as . As with the other atomic masses, I’m keeping the extra digits (in this case, the trailing 22).

In summary, the first change allows the function to see charges, the second change allows the function to recognize a charge as starting its own element name, and the third change defines the charge as an element. This allows the function to recognize charged species.

’s molecular weight is now MW("OH-"). is now either MW("CO3-2") or MW("CO3--") – they both work.

[Natural ChemE]

RE: Alternative solution to implementing charge –

A lazier solution would be to add a new function to the code:

Code: Select all

Function MWCharge(chemicalSpecies As String, Optional charge As Integer)
    mass = MW(chemicalSpecies)
    If (mass = ErrorValue()) Then
        MWCharge = ErrorValue()
        Exit Function
    End If
    MWCharge = mass - charge * 5.485799094622E-04
End Function

This would allow the user to enter as MWCharge("OH", -1).

Or, slightly less lazily, you could go back and rename the old MW function as MWWithoutCharge, then just call this new one MW. Then users could still do MW("OH", -1), etc., for charged species, or just the same ol’ MW("H2O") for uncharged species, since the Optional charge as Integer will default to a charge of if the user doesn’t explicitly provide it.

Still, I like the formula-based syntax of MW("OH-") in the prior solution more than MW("OH", -1).

[Natural ChemE]

I really need to make a new thread to include newer code and notes, plus links for Excel 2013.

Just since I forgot to mention it anywhere else, this codes does accept both decimals and fractions for input on all elements (including on charges, if they're implemented as shown above). i.e., MW("H7/2O1/2"), MW("H3.5O1/2"), and MW("H3.5O0.5") all work and return the same thing.

Most folks don't need it, but apparently I was being a bit OCD when I wrote this thing.

[yannickv]

Hi Natural ChemE,

Thank you for sharing your approach on implementing the charge. Indeed it's better to store the masses for + and - in the AMU function, rather than in the AMUCI and AMUP16 functions as I did. And also implementing the + and - in the isCap function is a cleaner approach than mine.

There is just one thing I missed in your suggestion: the function MWI checks if the value returned by the AMU function is greater then zero which is not true if it's looking for the mass of the electron. So the line

Code: Select all

If tempAMU < -0 Then

should be changed to

Code: Select all

If tempAMU < -0.001 Then

or something similar.

[Natural ChemE]

yannickv,

Thank you for pointing that out! That line should probably be:

Code: Select all

If tempAMU <> ErrorValue() Then

, since it’s really just meant to check if tempAMU had been set to the error value or not.

I’ll try to make a new thread with the updated code soon. I’ll post the link to it here once it’s done.

Do you think that there’s anything else that might improve the function?

[yannickv]

Hi

At least for me, the function now covers everything I would need.

[chebob512]

NOTE: The original mw code.txt incorrectly added the NIST uncertainties listed in parenthesis to the values
resulting in calculated weights being higher than the NIST values.

The uncertainties have been trimmed from the attached version.

I have not gone through the isotope version to strip all those uncertainties from the end of the values.

[Natural ChemE]

chebob512,

Wow, that's a big mistake! Thanks for correcting it!

For some reason I learned the notation as meaning that the calculated value was , but that the at the end wasn't statistically significant. Apparently this is an incorrect interpretation of NIST's notation.

I had a program download the data from NIST's database and write the code for me, so I'll correct it and rerun it later to fix the code.

Thanks!

[Natural ChemE]

New code at Creating Excel Add-ins: Molecular Weight Example (Update #1).

The new code:

1. Corrects the atomic masses.
2. Uses the newest atomic/isotopic mass values from NIST as of a few minutes ago.
3. Changes in molecular mass due to charge (adding the mass of an electron for each negative charge, subtracting it for each positive charge).
4. Adds in customization functions.
5. Has coded-out C# code for automatic updating from NIST's database.
6. Adds in a function for the most common isotope, "MostCommonIsotope".