Excel VBA Macro Programming

Overview

Sometimes in a spreadsheet a cell will contain both numeric characters and letter characters. For example, a cell that contains RJS123 will hold the characters as text. You may wish to separate out the numeric part of this text so that the cell only contains 123, but it is not easy to do this. The function Value will convert a label holding numeric data into a real number, but this will not work if there are alpha characters involved as well.

You can write your own function to do this, however. For example, if the cell has the label ART3478BC, you may wish to extract the number, which is 3478. You could use a Mid function to do this, but that assumes that the structure of the letters will always be the same. If you do not always have three letters followed by four numbers followed by two letters , then you will have to keep changing your criteria. The following code provides a reliable means to extract numbers from labels:

Function EVAL(cell_ref As Object) Application.Volatile t = "" For Each cell In cell_ref temp = cell.Value For n = 1 To Len(temp) If IsNumeric(Mid(temp, n, 1)) Then t = t & Mid(temp, n, 1) End If Next n Next cell EVAL = Val(t) End Function

This is a function as opposed to a subroutine, and it behaves differently than a subroutine. A range is passed into it in the form of an object called cell_ref .

Application.volatile ensures that the function must be recalculated when any cells on the worksheet are recalculated. The user can then select a cell or range of cells by dragging the cursor in much the same way as you would with the SUM function. If only one cell is selected, it is very apparent where the numeric has come from; for example, ABC1234 will be shown as 1234. However, this can have odd results if multiple cells are used because all the numerics will be concatenated together.

A variable called t is set to empty, which builds up the numeric string for return back to the cell. Each cell within the cell_ref object is then cycled through. A variable called temp is loaded with the cell value.

The code then uses a For..Next loop to work through the string of the cell value one character at a time. It tests each character to see if it is numeric using the IsNumeric function. If it is numeric, the code concatenates it onto the variable t .

When all cells in the selected range are completed, the code takes the value of t using the Val function and returns it using the variable EVAL . To try this out, do not run the code, just enter a formula (for example, =EVAL(A1) ) as you normally would in the spreadsheet itself.

If you click the Formula Paste icon on the Formula toolbar, this formula will be under the User Defined Formula section, and you can use it as you would for any other formula. If you put in no parameters, you will get the standard Excel errors.

Once you have entered the code into a module, type ABC1234RS into cell A1 and type =EVAL(A1) into another cell. The cell with the formula will give the result 1234 as a number. You formula should look like Figure 26-1.

Figure 26-1: Example of EVAL function

You may want to use this function if you have imported or pasted data in from another source such as a database. If there is a reference code imported that consists of letters and numbers, you may wish to separate out the numbers.