Creating Excel Add-ins: Molecular Weight Example (Update #1)

Discussions on everything related to the software, electronic, and mechanical components of information systems and instruments.

Creating Excel Add-ins: Molecular Weight Example (Update #1)

Postby Natural ChemE on March 7th, 2014, 2:33 pm 

This thread provides code for the Molecular Weight V4.1 Excel add-in. After installing, users can get the molecular weight of a string by calling the new Excel function mw(chemicalName), e.g. typing in a cell =mw("H2O") would yield 18.01528. This add-in can handle isotopes, charges, and complex formulas. This add-in also allows users to specify custom names for both chemicals (e.g. "water" for H2O) and specific functional groups (e.g. "Me" for CH3).

Updated 2014-08-28: Code for Version 4.1:
Excel Add-In - Molecular Weight V4.1.txt
(227.26 KiB) Downloaded 1126 times
Version 4.1 adds conventionForUseMostCommonIspotopesDefault which controls the convention for when monoisotopic masses are used. Prior versions didn't have an option to change the default convention.

Code for the molecular weight Excel add-in:
Excel Add-In - Molecular Weight V4.0.txt
(227.46 KiB) Downloaded 571 times


Notes on how to create and installs add-ins in VBA can be found in the prior thread: Creating Excel Add-ins: Molecular Weight Example. The code provided in this thread is recommended, though this older thread retains code for historical/educational purposes.

Short version for power users:
  1. Open Excel to a new workbook.
  2. Ensure that the "Developer" tab is showing in the ribbon. If it's not, enable it (see instructions in older thread or Google how to enable the Developer tab in Excel).
  3. Go to the Visual Basic button on the Developer tab.
  4. Right-click the new Excel workbook in the Project treeview, then make a new module.
  5. Optionally, rename the new module "MWAddInV4". (Doesn't really affect anything.)
  6. Download text file with code, then copy/paste it into the module.
  7. Save the workbook as an Excel Add-In with a name like "MWAddInV4.xlam".
  8. Close the workbook.
  9. Open a new Excel window (existing file or new one, doesn't matter.)
  10. Enable the add-in. (See old thread or Google how to enable an Excel add-in.)
  11. Now add-in will work for all Excel spreadsheets on your computer until you disable or/and delete the add-in.
  12. If you intend to share a spreadsheet using these functions, either have the recipient install this add-in or put the add-in code in a module in the file to be shared and save that file as a macro-enabled workbook (*.xlsm).

Notes:
  1. Basic use: In Excel, type =mw("H2O") for the molecular weight of water.
  2. Names go in quotes: =mw("H2") gets the molecular weight of molecular hydrogen, but =mw(H2) gets the molecular weight of whatever is in cell H2 on the spreadsheet.
  3. Syntax errors return -999999: If -999999 is returned, then the code detected an error. Try checking the input to ensure that there are no errors.
  4. New "MostCommonIsotope" function: =MostCommonIsotope("O") returns 16 since the most common isotope of Oxygen has a mass number of 16.
  5. Case sensitive: =mw("CO") gets for carbon monoxide (CO), but =mw("Co") gets for cobalt (Co).
  6. Complex formulas work: =mw("[P(C4H9)3C14H29]Cl") does correctly return the molecular weight for [P(C4H9)3C14H29]Cl. In general you can have as parentheses, occurrences of elements, isotopes, charges, etc. as you want. The code doesn't care if you use ()'s or []'s; it seems them as the same. While not recommended as it's poor form, you can even mix and match, e.g. =mw("(H]2") does work.
  7. Subscripts can be fractions or decimals: Subscripts can be fractions or decimals, e.g. =mw("H0.5") or =mw("H1/2") will correctly read as H0.5 or H1/2 and return half the molecular weight of Hydrogen.
  8. Charges affect mass: Hydroxide, OH-, is calculated as the molecular weight of Oxygen plus Hydrogen plus an electron. This is slightly more than the mass of OH alone. Positive charges slightly reduce mass.
  9. Charges are their own atomic elements:Charge signs (- and +) are treated as their own atomic elements. For a charge of negative two, type in "-2" instead of "2-".
  10. You can specify specific isotopes: Put an exclamation point followed by the atomic number of an isotope before the atomic symbol. Example: mw("!2H2O") is heavy water, D2O. mw("D2O") would also work.
  11. Prevalence-weighted average is used by default when no isotope is specified: mw("H2O") doesn't specify any isotopes, so both Hydrogen and Oxygen use prevalence-weighted averages. (If you don't know what this means, it's okay because it's probably want you want.)
  12. Changing default isotope behavior: Don't like prevalence weighting? Use mw("H2O", True) instead. The second argument can be True or False, and if not supplied is False by default. True uses the most common isotope whenever isotope isn't specified while False uses the prevalence-weighted average whenever isotope isn't specified. True or False doesn't matter when for symbols which already have an isotope specified.
    Put an exclamation point followed by the atomic number of an isotope before the atomic symbol. Example:
  13. Charges affect mass: Hydroxide, OH-, is calculated as the molecular weight of Oxygen plus Hydrogen plus an electron. This is slightly more than the mass of OH alone. Positive charges slightly reduce mass.

Advanced notes:
  1. You can add in custom names: See comments in VBA code in function MWICustom. Custom names for overall chemicals go into MWICustom. Custom names for charges, isotopes, functional groups, and other elements of chemical formulas go into AMUCustom.
  2. Updates: See this thread for future updates. VBA code contains commented-out C# code which can generate new VBA tables from NIST's data. C# code can be run in Microsoft's Visual Studio or, probably, Mono for Linux users.
  3. Bugs: If you find any bugs, please report them to this thread!
  4. Feature requests: If you would like any new features, please report them to this thread!

Historical notes:
  1. There used to be an "Isotope" version: The add-in defines two functions MW() and MWI() that both do the same thing. This is because there used to be two different add-ins; MW() was small and did only prevalence-weighted stuff while MWI() was larger but did isotopes too. Technically the new version is the "Isotope" version and the smaller version of the code has been discontinued.
  2. Versions before 4.0 often returned (slightly) incorrect results: I made a mistake interpreting NIST's notation. For example, I thought that the current atomic weight of Hydrogen, 1.00794(7), meant "1.007947 where the last '7' isn't significant". However this was incorrect; it actually meant "". Version 4.0 corrects this misinterpretation, returning correct values.
  3. Not all older versions included changes in mass due to charge: This add-in used to disregard charge, e.g. OH and OH- both had the same molecular weight. Versions 4.0 and later consistently consider charges as increasing molecular mass by , e.g. OH- outweighs OH by one electron mass. This also means that you can get negative results in extreme cases, e.g. =mw("+") will return the negative of an electron's mass.
  4. !2D and !3T no longer work: Hydrogen's second isotope can be explicitly referred to using either "!2H" or "D", but not "!2D". Hydrogen's third isotope can be explicitly referred to using either "!3H" or "T", but not "!3T". Some older versions accepted "!2D" and "!3T" because D and T were regarded as elements, but now they're regarded as custom isotope names instead; it doesn't make sense to specify the isotope of an isotope.
Natural ChemE
Forum Moderator
 
Posts: 2754
Joined: 28 Dec 2009
AJKind60 liked this post


Re: Creating Excel Add-ins: Molecular Weight Example (Update

Postby AJKind60 on August 28th, 2014, 1:23 pm 

Great Work, Thanks! I'm a mass spectrospist (well, I operate an MS anyway). How can I edit to make the default monoisotopic mass? Thanks...Al
AJKind60
Forum Neophyte
 
Posts: 3
Joined: 28 Aug 2014


Re: Creating Excel Add-ins: Molecular Weight Example (Update

Postby Natural ChemE on August 28th, 2014, 2:21 pm 

AJKind60,

Welcome to the forums! I'm glad that you like the add-in!

EDIT: I added code for Version 4.1 to the top post. At the very top of the new code, there's:
Code: Select all
'If the user doesn't specify whether or not they want to the monoisotopic convention or not,
'then use:
Const conventionForUseMostCommonIspotopesDefault As Boolean = False
You'll just want to change the default to True:
Code: Select all
Const conventionForUseMostCommonIspotopesDefault As Boolean = True
.

Old reply:
As you know, =mw("H2O", true) would use monoisotopic masses. To be able to omit the true argument, I'd suggest:
  1. Open the code in the VBA editor, Notepad, or word processor.
  2. Hit Ctrl+H to bring up the "Replace" window.
  3. Find useMostCommonIsotopes As Boolean and replace with useMostCommonIsotopes As Boolean = True. Then hit the Replace All button.
Since useMostCommonIsotopes is Optional but no default value is specified, VBA'll use false as the default. By putting in = True, you're telling VBA to see =mw("H2O") as =mw("H2O", true).
Natural ChemE
Forum Moderator
 
Posts: 2754
Joined: 28 Dec 2009
AJKind60 liked this post


Re: Creating Excel Add-ins: Molecular Weight Example (Update

Postby AJKind60 on August 28th, 2014, 2:43 pm 

THANKS!
AJKind60
Forum Neophyte
 
Posts: 3
Joined: 28 Aug 2014


Re: Creating Excel Add-ins: Molecular Weight Example (Update

Postby rolandp on November 21st, 2014, 6:59 am 

Hi, thanks for providing this great tool! Unfortunately I always get an error when using it.
When I type ´=mw´I get the full formula in the drop-down menu, so the add-in is active. Then I type ´=mw("K")´, for example, but the result never shows up - I always get the ´#name´ error. Do you know what could be wrong? I guess it could just be a simple excel setting.

Thank you!
Roland
rolandp
Forum Neophyte
 
Posts: 1
Joined: 21 Nov 2014


Re: Creating Excel Add-ins: Molecular Weight Example (Update

Postby AJKind60 on November 21st, 2014, 2:06 pm 

I usually get that error when the character isn't recognized...like when there is a space after the atom or formula in a cell..."CH4 " vs "CH4". Not sure if this helps or not?
AJKind60
Forum Neophyte
 
Posts: 3
Joined: 28 Aug 2014


Re: Creating Excel Add-ins: Molecular Weight Example (Update

Postby Natural ChemE on September 30th, 2015, 2:48 am 

rolandp » November 21st, 2014, 5:59 am wrote:Hi, thanks for providing this great tool! Unfortunately I always get an error when using it.
When I type ´=mw´I get the full formula in the drop-down menu, so the add-in is active. Then I type ´=mw("K")´, for example, but the result never shows up - I always get the ´#name´ error. Do you know what could be wrong? I guess it could just be a simple excel setting.

Ick, I guess that I'm pretty late in replying, but, yeah, there's some issue in enabling the add-in. The #NAME? error pops up whenever what you type into Excel is unrecognized,
    Example:
    • =sin(0) results in 1;
    • =sinn(0) results in #NAME?.
so for whatever reason, Excel isn't seeing the function MW().

Guesses:
  1. Your computer just needs a restart.
    • You may've forgotten to save the module that holds the VBA code after copy/pasting the code in.
        To test:
        1. Close all of your currently open Excel windows.
        2. Open a new Excel sheet.
        3. Go to the Developer tab, then to the Visual Basic button. This should open the VBA editor.
        4. The tree view on the left should contain a list that includes the current spreadsheet as well as all installed add-ins.
        5. Select this add-in on that tree view. This should show you the code that it contains. Ensure that the code you downloaded from this thread is in it.
    • Your computer has some sort of weird security setting on it that blocks unauthorized VBA code. This is really only likely if you're on a work computer with a highly active domain administrator.
    Natural ChemE
    Forum Moderator
     
    Posts: 2754
    Joined: 28 Dec 2009



    Return to Computers

    Who is online

    Users browsing this forum: No registered users and 5 guests