Computing the Value of a Cell

The function shown in Listing 13.17 translates the equation that the user typed in into one that the scripting engine can evaluate. The basic problem this function has to handle is to translate cell references of the form <letter><number> into Eval(<row>, <column>). For example, a reference to A1 must be converted to Eval(1,1), and a reference to B7 must be converted to Eval(7,2).

Listing 13.17: Compute Function in Charter

Private Function Compute(e As String) As String
Dim i As Integer
Dim j As Integer
Dim s As String
Dim t As String
On Error Resume Next
t = UCase(e)
For i = 1 To MSFlexGrid1.Cols - 1
   For j = 1 To MSFlexGrid1.Rows - 1
      s = Chr(j + 64)
      s = s & FormatNumber(i, 0)
      t = Replace(t, s, “Eval(“ & FormatNumber(i, 0) & “,” & _
         FormatNumber(j, 0) & “)”)
   Next j
Next i
Compute = FormatNumber(ScriptControl1.Eval(t), 2)
End Function

There are basically two ways to handle this process. The first way is to parse the equation and determine which values are cell references and which values are not. This is the way that Microsoft Excel works. It’s also the way I probably would have done it, except that it would have taken a lot more code to accomplish, and it really wouldn’t make much of a difference given the size of the grids I’m using in this program.

So I chose to use the second way—a brute force approach. I search the equation for every possible cell reference and replace that cell reference with the corresponding reference to the Eval function. I compute the letter part of the cell reference by adding 64 to the column number and converting the sum to a character. (Note that a value of 65 corresponds to an A, 66 corresponds to a B, and so forth.) Then I append the row number to create the cell reference. Finally, I use the Replace function to replace every occurrence of the cell reference with the corresponding call to the Eval function.

After I’ve processed all possible call references, I use the scripting engine’s Eval method to compute a new value for the cell. Then I format the result and return it as the value of the function.

© 1998 SYBEX Inc. All rights reserved.