Writing Chemical Equations in Excel: The Easy Way (no ions)

I find it very tedious to write a chemical formulas and equations because of the subscripts, so I decided to create a VBA code in Excel which handles just that. It worked fine, until I realized that it won’t have any benefit since I’ve done it in Excel, and lesson plans and the likes are almost always done in Word.

Well, recently, I showed this to my colleague and he suggested pasting the transformed equation from Excel to Word with the hope that the right format will be preserved. Guess what, it worked. That’s why I’m sharing it now.

My code however, won’t work with equations that involve ions, and fractional coefficients but I’m already working on it. Hopefully, I can post another set of code in the future which could be used for both ionic and non-ionic chemical formula.

Here is an example of a chemical equation that can be formatted automatically. Just take note that the spaces between the compounds, + and -> are required to be formatted properly.

CH4 + 2O2 -> CO2 + 2H2O

Here are the steps (MS Excel 2003).

1. Open a blank worksheet.
2. If the Visual Basic Toolbar is not visible do the following: View ->Toolbars->Visual Basic
3. On the Visual Basic Toolbar, click on Visual Basic Editor
4. On the Menubar of Visual Basic, click Insert ->Module
5. Copy the code below and paste inside the module.

Sub write_chem()

For Each rng In Selection
mystring = rng.Value
Length = Len(mystring)
For i = 1 To Length
char = Mid(mystring, i, 1)
If i <> 1 Then
charfound = found(Mid(mystring, i – 1, 1))
Else
charfound = True
End If

On Error Resume Next
x = Int(char)
If Err <> 13 And charfound = False Then
rng.Characters(i, 1).Font.Subscript = True
Else
rng.Font.Subscript = False
End If
Next i
Next rng

End Sub

Function found(char As String) As Boolean
Dim mylist As Variant

mylist = Array(" + " , " . " , " – ", Space(1))

For i = 0 To 4
If mylist(i) = char Then
found = True
Exit For
Else
found = False
End If
Next i
End Function

6. Close the Visual Basic Editor
7. On the Visual Basic Toolbar, click Control Toolbox -> Command button
8. Draw a command button on the worksheet
9. Double-click the command button to open again the Visual Basic Editor.
10. Within the Private Sub and End Sub, type write_chem. Your code should look something like the one below

Private Sub CommandButton1_Click()
write_chem
End Sub

11. Close the Visual Basic Editor
12. On the Visual Basic Toolbar, click Exit Design Mode
13. Type  Chemical Equations or formula in Excel.
14. Select the cell/s and click the command button to format it correctly. Try H2SO4 and 2CCl4 as examples
15. For the 2CCl4, notice that the number 4 became a subscript while the number 2 was preserved.
16. You can now paste the formatted equations or formula in MS Word.
17. When you are using this code, write all the chemical formulas/equations first in Excel then format everything before you paste them to Word.

I’ll be working on the code for the superscripts next time. For now, please enjoy.

By the way and as always, please feel free to comment. And if you need the worksheet please give me your email address so I can send it to you. Cheers!!!

~ by experibiz on September 15, 2008.