Microsoft Excel Functions & Formulas

Use a user-defined function to calculate the

cross sum of a cell

With this tip, you can calculate the cross sum of a cell. Create a

table like the one in Figure 13-12 and type any numeric data in cells

A1:A5.

4

To calculate the cross sum of a cell:

1. Press <Alt+F11> to open the Visual Basic Editor.

2. From the Insert menu, click Module.

3. Type the following function:

Function Qs(rng As Range)

Dim i As Integer

Fori=1ToLen(rng.Value)

Qs = Qs+Cint (Mid(rng.Value, i, 1))

Next i

End Function

4. Close the VBA Editor by pressing <Alt+Q>.

5. Select cells B1:B5 and type the formula =Qs(A1).

6. Press <Ctrl+Enter>.

User-defined Functions 337

13

Figure 13-13