Advanced Macros in VBA

There are literally hundreds of VBA objects, properties and methods, and this can be confusing for the beginner. Chapter 14 focused cn the basic tools needed to create macros to automate chemical worksheet calculations how to transfer values from a sheet to a VBA module, how to perform calculations within a VBA module, how to perform logical branching and iterative looping, and how to send values back from a VBA module to a worksheet. In this chapter we ll use these tools to create some useful Sub procedures. 

As a first example, well create the ChemicalFormat macro to format text in a selected cell as a chemical formula. The macro will be a simple one. It will examine each character of a text string if the character is a number, the character will be subscripted. You ll find this macro useful if you label rows or columns in your worksheets with chemical formulas, such as CH3CH=CH2 or moles of H3PO4 the macro will be a real timesaver. 

We need to accomplish four steps in this procedure (i) obtain the contents of the active cell, (ii) set up a loop so that we can examine each character in turn, (iii) test to see if the character is a number and (iv) subscript the character. 

The code required for the first three steps was described in Chapter 14. We ll (i) use the ActiveCell keyword to obtain the string, (ii) use For... Next to create a loop and the Mid function to access each character within the 

If Asc(char) = 48 And Asc(char) = 57 Then ActiveCell. Characters(Start =x, Length =1).Font.Subscript = True Next X End Sub 

---