Part III : A Token for All Seasons

In the original implementation we needed to reference columns in a spreadsheet control, to provide values that would be used in the formulas (following me?). The way we did that was to reference the name of the column within the formula, something like this: '3*7+[ValueColumn]'. Before the expression was evaluated, any references to any columns were replaced by the value of the entry in that column. For example: if the ValueColumn column had the entry 5.46, the expression above would be resolved to '3*7+5.46' before the result was calculated.

After a while we discovered that it would be useful to be able to insert other values into the formulas as well as spreadsheet figures. There were a number of configurable system values that were also used in the formulas, so a case statement was built to handle these special cases.

All that is fine for a specific implementation. But to make the code useful as a portable library that still supported this sort of functionality, I decided to implement a token structure. For ultimate portability I decided to implement tokens as an entirely separate set of functions from the calculation functions. This meant that they could be used in other situations as well, without any modification. The implementation of the token library is entirely through function calls.

Functions are exposed to define/redefine, and remove tokens in the collection of tokens maintained internally by the library, as well as clear the whole collection. All the code in the token function library is quite simple, for example the code to define a token looks like this:

  Function DefineToken (ByVal sToken As String, 
                        ByVal sValue As String) As Long
    Dim lTokenIndex As Integer
    Dim lResult As Long

    ' If we have tokens we may be altering or adding.
    ' if we do not have any tokens, search will return 0.
    lTokenIndex = FindToken(sToken)
    If lTokenIndex <:> 0 Then
      ' We are altering an existing token
      ATokens(lTokenIndex).sValue = sValue
      lResult = mlTokenCount
    Else
      lResult = AddToken(sToken, sValue)
    End If
    DefineToken = lResult
  End Function

Very simple, but how about some of the functions that actually do the work? Here is the code for AddToken (a function private to the token library) - the function which actually adds a new token to the collection:

  Private Function AddToken (ByVal sToken As String, 
                             ByVal sValue As String) As Long
    Dim lResult As Long

    ' We are adding a token, adjust the count of tokens
    mlTokenCount = mlTokenCount + 1

    ' redimension the array and insert the values for the new token
    ReDim Preserve ATokens(1 To mlTokenCount)
    ATokens(mlTokenCount).sIdent = sToken
    ATokens(mlTokenCount).sValue = sValue
    lResult = mlTokenCount
    
    AddToken = lResult
  End Function

You will notice that I explicitly set the range of the array used to actually store the tokens (which uses a user defined type for each token). I do this because it eliminates confusion about whether the array is zero based or one based, making the rest of the code a little more straightforward.

I also implemented functions to write the tokens to an INI file and be able to read them from an INI file. I can only explain this by saying if you eat the wrong things before going to sleep, you may have odd dreams where code writes itself backwards and you can't touch your keyboard because it's on fire.

The most important function exposed, however, takes a string as a parameter and replaces all known tokens in the string with their defined values. All we do is pass the expression string we are about to calculate into that function, before we begin evaluation. Any tokens we have defined will have their token names replaced with their values. A simple example of how to use this in code might look like this:

  Dim lResult As Long
  Dim sTemp As String

  lResult = DefineToken("Pi", "3.14159")
  sTemp = InputBox$("Please enter the radius of the circle:", 
                    "Enter Radius", "7")
  lResult = DefineToken("R", sTemp)
  sTemp = ResolveTokens("2*Pi*R")
  MsgBox ResolveFormula(sTemp), 64, "Circumference"
  sTemp = ResolveTokens("Pi*(R*R)")
  Msgbox ResolveFormula(sTemp), 64, "Area"

To wrap this up neatly, the calculation library (built in Part I) includes a wrapper function called ResolveFormulaEx. This performs a ResolveTokens on the passed formula before it calls ResolveFormula to calculate the result of the formula. Essentially doing what the above code does in one call instead of two.

Remember, the token library doesn't depend on the calculation library, it can be used for anything you can come up with.

---