Chemistry Formatter Add-ins for Microsoft Word, Excel, and PowerPoint

 

Christopher King

Chemistry Department, Troy University, Troy, AL 36082

E-mail:  cking@troy.edu

Home page:  http://christopherking.name

 

Here are free, open-source add-ins for Microsoft Word, Excel, and PowerPoint that format chemical equations and exponents.  The source code is released under the GPL v. 3 license.  To illustrate their use, imagine typing the following line in Word:

Cu2+(aq) + SO42-(aq) Description: uglyarrow.JPG CuSO4*5H2O       Heat for 2 hours at 120°C.   ΔH = 1E4 J/mol

(I typed -->, which Word automatically converted to Description: uglyarrow.JPG).  Leave the cursor in this paragraph (or select just the text to format).  Click the Description: image001 button, which runs the add-in.  The line is converted to this:

Cu2+(aq) + SO42-(aq) Description: prettyarrow.JPG CuSO4·5H2O     Heat for 2 hours at 120°C.   ΔH = 1 × 104 J/mol

Superscripts and subscripts were added; the arrow was replaced with a prettier arrow; and the asterisk was converted to a centered dot to indicate waters of hydration (or radicals).  A plus (or minus) sign with space before it is not superscripted.  Numbers in the text after the equation were unchanged, except that 1E4 was converted to 1 × 104.

In Excel, you could type the following into two cells:

*CH3 + *OH --> CH3OH

2H2(g) + O2(g) --> 2H2O(g)   yield: 3.2E8 kg

Select both cells, click the Description: image001 button, and the lines are converted to these:

·CH3 + ·OH Description: prettyarrow.JPG CH3OH

2H2(g) Description: prettyarrow.JPG 2H2O(g)   yield: 3.2 × 108 kg

(The centered dots are a little small in Arial, which is Excel’s default font on the PC.)

Here are some other examples.  Square brackets may be present.

[PtCl2(NH3)4]2+

becomes

[PtCl2(NH3)4]2+

Radicals can be entered.

*CH3 + *OH Description: uglyarrow.JPG CH3OH

becomes

·CH3 + ·OH Description: prettyarrow.JPG CH3OH

A plus or minus sign before a letter is not treated as a charge.

4-CH3C6H4OH

becomes

4-CH3C6H4OH

If more than one digit precedes a charge, the last digit goes with the charge.

PO43- and O22-

become

PO43- and O22-

A single digit preceding a charge goes with the charge, except for O3.

O2-, P3-, NO3-, and CH3-

become

O2-, P3-, NO3-, and CH3-

The program cannot read minds, so check the results.  In the last example, CH3- was probably meant to be CH3-, rather than CH3-

These add-ins take a couple of seconds to load the first time they are run; after that, they are quite fast.

Word Add-in

To download the Word add-in, click on the appropriate link below with your secondary mouse button (i.e., right-click) and select Save Target As … (Internet Explorer) or Save Link As … (Firfox) from the menu.

Word 2010 Chemistry Formatter (also works on Word 2013)

Last updated February 14, 2012

Word 2007 Chemistry Formatter

Last updated March 31, 2007

Internet Explorer 7 changes the file extension to .zip in the Save As dialog.  Change the ending back to .dotm and it should work fine.

The Word 2007 add-in seems to work at least somewhat on the Mac with Office 2011.

Word 2003 & XP Chemistry Formatter

Last updated March 1, 2003

Save the add-in in the Word startup directory. 

For international users:

The add-in goes in the …\Startup directory, which must be a "trusted location". To see the "trusted locations", on the ribbon go to File/Options/Trust Center/Trust Center Settings.../Trusted Locations.  One of the default trusted locations is the directory …\Startup, which, at least in Office 2010, is shown in English, even on non-English computers, where that directory doesn't even exist! For example, in Dutch the …\Startup directory is …\Opstarten, which isn't a trusted location by default. So, to fix the problem, add, e.g., …\Opstarten, to the list of trusted locations. Now the add-in should work without security warnings.

For Word 2007 or 2010 or 2013, the default path to this folder follows.  These directories are not visible by default, so the procedure to the left must be followed to make them visible.

For Windows 2000 or Windows XP,

C:\Documents and Settings\USERNAME\Application Data\Microsoft\Word\STARTUP

For Vista and Windows 7, the location is:

C:\Users\USERNAME\AppData\Roaming\Microsoft\Word\STARTUP

This directory may need to be created, if it doesn't exist.

For Word 2003 and XP, the default path to this folder is, for Windows XP,

C:\Documents and Settings\USERNAME\Application Data\Microsoft\Word\STARTUP

or, if user profiles are used on your computer,

C:\Windows\Profiles\USERNAME\Application Data\Microsoft\Word\Startup

or, for older Windows operating systems,

C:\Windows\Application Data\Microsoft\Word\Startup

Can’t find the proper folder?

The startup directory may be hidden on Windows XP and Windows 7 computers.  To make it visible, open any folder, go to the Tools menu (hold the Alt key down if you don't see that menu), then to Folder Options….  From the View tab, select the entry “Show hidden files and folders”.

Description: image003

Unless you have Office 2007 or later, do the following.  Otherwise, follow the directions under PowerPoint, below.

Once the file is in the correct directory, it will automatically load every time Word is started.  To start the formatter without restarting Word, go to the Templates and Add-Ins… dialog box (on the Tools menu).

Description: image004

 

Description: image005

If the list of add-ins does not include “Chemistry Formatter”, use the Add… button to locate it.  Put a check mark next to the entry “ Chemical Formatter for Word.dot” (which should now be present in the add-ins list).  Click the OK button to complete the installation.  You can tell that installation succeeded by the appearance of the Description: image001 button on Word’s standard toolbar.

Some Details

The Chemistry Formatter for Word will process text in shapes, a “normal” selection of text, a “block” selection (made by holding down the alt key before selecting text), and cells in a table.  The shapes and normal selection are usually processed in less than a second.  Processing a block selection takes about 7 times as long, though this is still only about a second for small selections.  A block of cells in a table is also somewhat slower to process if it extends over more than one row, and doesn’t include all the cells in the row; however, it is not as slow as a block selection.  If the AutoCorrect “Replace text as you type” option is turned off, the formatter will convert -->, but not Description: uglyarrow.JPG, to Description: prettyarrow.JPG.  If it is on, the formatter will convert Description: uglyarrow.JPG, but not -->, to Description: prettyarrow.JPG.

Word XP and later versions allows the user to select several regions of text at the same time (select the first region, hold down the control key, and select another region with the mouse).  The Chemistry Formatter will only format the last region selected.  The formatter does not process text in diagrams.  Also, the formatter will produce an error message if hidden text at the very end of a document is selected (by using “Select All” from Word’s Edit menu).

Complete “Undo” capabilities are provided.  (Word provides this automatically; I can’t take credit for it.)

The program was created using Visual Basic for Applications.  It is not password protected.  To view the code, unload the add-in, open it from the file / open menu, then go to the Visual Basic Editor.

Typing Isotopes, pKa, Δ, etc. in Word

Chemists use all kinds of symbols.  Word includes “AutoCorrect”, which makes it easy to use combinations of symbols.  If you type, say, “-->",it is automatically changed to Description: uglyarrow.JPG.  (Control-z will undo the change.)  You can add other entries to the AutoCorrect list.  Here are some entries I’ve added: 

Co-60 becomes 60Co
DHo becomes Δ
pKa becomes pKa

Here is a Word document that makes it easy to add entries to the AutoCorrect list.  Just select the entries that you want to add from a table and click the button.  17 entries are included; you can add others.  The table makes a convenient storage area for the entries you’ve added so that you can find them when you need them.

ChemSpell

Nghê Quốc Khải has created a nice Word add-in called ChemSpell. You can get it at http://www.esnips.com/web/ChemSpellDEMO.

Excel Add-in

To download the Excel add-in, click on the appropriate link below with your secondary mouse button (i.e., right-click) (Mac users:  hold down the control key, Description: cmnd, and click on the link) and select Save Target As … (Internet Explorer) or Save Link As … (Netscape) from the menu.

Excel 2010 Chemical Formatter (also runs on Excel 2013)

Last updated June 10, 2013

Excel 2007 Chemical Formatter

Last updated March 23, 2007

Internet Explorer 7 changes the file extension to .zip in the Save As dialog.  Change the ending back to .xlam and it should work fine.

Excel 2003 & XP Chemical Formatter

Last updated August 1, 2003

Save the add-in in the existing Excel AddIns directory.  (If the directory is hidden, make it visible by following the procedure given above for the Word Add-in.)

Note for non-English users:

On computers using a different language, the directory names may be different than shown below. See the notes under Word, above.

For Excel 2007 and 2010 for the PC, the default path to this folder follows.  These directories are not visible by default, so the procedure above must be followed to make them visible. The Excel 2007 add-in seems to work at least somewhat on the Mac with Office 2011.

For Windows XP:

C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

For Windows 2007, and Vista:

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns

For Excel 2003, XP and 2000 for the PC, the default path to this folder is

for Windows 2000 or Windows XP,

C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

or, if user profiles are used on your computer,

C:\Windows\Profiles\UserName\Application Data\Microsoft\AddIns

or, for older Windows operating systems,

C:\Windows\Application Data\Microsoft\AddIns

Unless you have Office 2007 or later, do the following. Otherwise, follow the directions under PowerPoint, below.

Once the file is in the correct directory, go to the Add-Ins… dialog box (on the Tools menu).

  Description: image006

 

  Description: image007

Put a check mark next to the entry “Chemistry Formatter” and you are done.  You can tell that the installation succeeded by the appearance of the Description: image001 button on Excel’s formatting toolbar.

Some Details

The Chemistry Formatter for Excel will process text in cells, in chart titles and axis labels, in text boxes, and in “AutoShapes”.  The entire cell, label, textbox, or shape must be selected; if only part of the text is selected, the formatter will not run.  To format a comment, shape, or textbox, select the border of the object, not the text.  A shortcut can be used in a chart:  if you select something in the chart besides the title or an axis label, then all titles and axis labels will be formatted. 

Cells containing numbers or formulas are ignored.  Hidden and protected cells are unchanged, as are linked textboxes.  Blank cells are ignored, so you can run the formatter with an entire column or row (or the entire sheet) selected.  The formatter is unable to format textboxes and other “AutoShapes” if they are on charts, so these will have to be formatted manually.

“Undo” is provided for cells.  It is not available for other objects, such as comments and text labels.  A drawback to adding undo capabilities is that it erases the contents of the clipboard.

The arrow, Description: prettyarrow.JPG and multiplication sign, ×, are operating system dependent in Excel (Word doesn’t have this problem).  If a Mac is used to create a spreadsheet containing these characters, the symbols will not make sense to someone viewing the spreadsheet on a PC, and vice versa.  Although both operating systems have some fonts in common, the character sets used by these fonts are different.  If you send a spreadsheet to someone using a different operating system, you may want to first use Edit / Replace… to convert Description: prettyarrow.JPG to --> and x to ×.

The Excel chemistry formatter was created using Visual Basic for Applications.  The code originated from a macro provided by Dr. E. J. Billo, of the chemistry department at Boston College in Chestnut Hill, MA.  His macro is also in his book, Excel for Chemists, 2nd ed, which came out in March, 2001.

Comments Applying to Both Add-ins

The XP versions of the add-ins will convert 1e6 or 1E6 to exponential notation; the other versions only convert a capital E (searching for a lower case e, the most-used letter in English, significantly slowed down the formatter on older machines).

Why three versions?  The add-ins were developed for Office XP.  Some changes were needed when the add-ins were run on Office 2000, which didn’t support the drawing canvas, or the new worksheet protection model used in Excel.  More changes were needed then they were run on Office 97, which uses an older version of Visual Basic for Applications.

If you do some programming using Visual Basic for Excel or Word, you may have changed “Error Trapping” (Tools/Options… in the Visual Basic editor) to “Break on All Errors”.  This setting may occasionally cause the add-ins to stop running.  The problem will go away if one of the other error trapping settings is used.

PowerPoint Add-in

To download the PowerPoint add-in, click on the appropriate link below with your secondary mouse button (i.e., right-click) and select Save Target As … (Internet Explorer) or Save Link As … (Netscape) from the menu.

PowerPoint 2010 Chemical Formatter

Last updated February 8, 2010

PowerPoint 2007 SP1 Chemical Formatter

Last updated December 15, 2007

This requires service pack 1 (which you may already have, if you’ve got Microsoft Update turned on).

Internet Explorer 7 changes the file extension to .zip in the Save As dialog. Change the ending back to .ppam and it should work fine.

PowerPoint 2007 Chemical Formatter

Last updated March 31, 2007

Internet Explorer 7 changes the file extension to .zip in the Save As dialog. Change the ending back to .ppam and it should work fine.

PowerPoint 2003 Chemical Formatter

Last updated January 29, 2005

Save the add-in in the existing AddIns directory.  The default path to this folder follows.  These directories are not visible by default, so the prodecure above must be followed to make them visible.

In Windows 2000 or Windows XP, the default path to this folder (in English) is

C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

In Windows 7 or Vista, the default path to this folder (in English) is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns

If your computer uses a language besides English, the directory name will probably not be exactly as indicated above. See the notes under Word, above.

To install in PowerPoint 2003: Start PowerPoint 2003.  Change the security level from high to medium (on the Tools menu, select Macro, then Security….  On the “Security Level” tab, select Medium.  Go to the Add-Ins… dialog box (on the Tools menu).  Use the Browse button to find the file.  Put a check mark next to the entry “Chemistry Formatter for PowerPoint 2003” and you are done.  You can tell that the installation succeeded by the appearance of a button with “H2O” on it on the formatting toolbar.  If you like, you can reset the security level to high.

To install in PowerPoint 2007 or 2010: Start PowerPoint.  Click the Office Button, then select PowerPoint Options (located at the bottom, not side, of the Office Button box). The PowerPoint Options box appears. On the menus on the left frame, select Add-Ins. At the bottom of the right frame is Manage, followed by a drop-down list. From the list, select PowerPoint Add-ins. Click the Go button. Up pops a dialog box. Use the Add New… button to find the file.  Finally, put a check mark next to the entry "Chemistry Formatter for PowerPoint 2007 (or 2010)” and you are done.  You can tell that the installation succeeded by the appearance of a Description: image001 button on the Home tab of the ribbon, after the Font group:

Some Details, 2003

The programming interface in PowerPoint is not as complete as it is in Word or Excel, so this add-in isn’t quite as sophisticated.  The button that runs the formatter isn’t as fancy:  it is just the label H2O.  Here’s what it looks like on the formatting toolbar: Description: image008  If you move it, the new position won’t be remembered when PowerPoint is restarted. 

If “->” is typed, the formatter will convert it into a prettier arrow.  This is different from Word and Excel, where “-->” is what gets changed to an arrow.  In Word, if no text is selected, the paragraph containing the cursor is formatted.  In PowerPoint, the paragraph containing the cursor cannot always be determined.  So, if no text is selected in a title box, the entire contents of the box will be formatted.  In a table or organization chart or note pane (in “Normal” view), if no text is selected, nothing will be formatted, though if the entire table or chart is selected, then the entire table or chart will be formatted.  For other shapes, the current paragraph will be formatted, if no text is selected.  That includes notes in “Notes” view.  If multiple shapes or slides are selected, they will all be formatted.  Comments cannot be formatted.  No part of a chart can be formatted. 

“Undo” is available; Cntl +z, or the undo button or menu will restore the changes made by the add-in.

Other Details, 2007

This version will replace either “->” or “Description: uglyarrow.JPG” with a prettier arrow. SmartArt shapes, charts, and comments cannot be formatted. Most selections of part of a paragraph can now be formatted. The PowerPoint 2010 code, for curious VBA programmers, may be downloaded.

Programming Help Needed

The next version of Office for the Mac, “Microsoft Office 2008 for Mac”, will not include Visual Basic. Microsoft recommends writing macros in AppleScript. Also, OpenOffice now supports macros. Both of these could use a chemistry formatter. If you’d like to get the chemistry formatters to work on other platforms, I’d be happy to add a link to your site, or make your product available here. If you’d like, we could work together on the project. Getting the formatters to format text is the easy part. Getting them to handle any situation the user throws at them is the time consuming part. That requires learning the software’s object model. To produce a quality product requires testing all the possibilities, which takes time.

 

Let me know if you have problems or suggestions, or just like the formatters.  I’m at cking@troy.edu

--Chris