Something I’ve been meaning to do since Eusprig was code up this function.

It struck me that one of the things that can make worksheet formulas difficult to follow is that a cell reference can be repeated several times. Also IV2367 is a bit of a pain as far as identifiers goes. (A bit like all that Greek the maths department made us do at school.)

My preferred way to work with algebra is naming stuff a, b, c etc, so I thought I would code that up as a worksheet function:

Private Const T1 As String = "_a_"
Private Const T2 As String = "_b_"
Private Const T3 As String = "_c_"
Private Const T4 As String = "_d_"
Private Const T5 As String = "_e_"
Private Const T6 As String = "_f_"

Public Function Algebra(theFunction As String, _
      Optional Arg1 As Variant, Optional Arg2 As Variant, Optional Arg3 As Variant, _
      Optional Arg4 As Variant, Optional Arg5 As Variant, Optional Arg6 As Variant) As Variant

      Dim sTempFunc As String
      sTempFunc = theFunction
      Debug.Print sTempFunc
      If Not IsMissing(Arg1) Then sTempFunc = Replace(sTempFunc, T1, Arg1)
      If Not IsMissing(Arg2) Then sTempFunc = Replace(sTempFunc, T2, Arg2)
      If Not IsMissing(Arg3) Then sTempFunc = Replace(sTempFunc, T3, Arg3)
      If Not IsMissing(Arg4) Then sTempFunc = Replace(sTempFunc, T4, Arg4)
      If Not IsMissing(Arg5) Then sTempFunc = Replace(sTempFunc, T5, Arg5)
      If Not IsMissing(Arg6) Then sTempFunc = Replace(sTempFunc, T6, Arg6)
      Debug.Print sTempFunc
      Algebra = Application.Evaluate(sTempFunc)
End Function

I can then call it from a cell using:


Obviously it would get more useful if the formula was more complex and/or had repeating arguments. I also havent bothered to try and optimise it, or add error handling, or debugging…)

I think the _a_ stuff is a bit ugly, but if it used something too simple it might replace the wrong thing. Any better suggestions?

What do you think?

As a concept is it useful to pass in to a function a string formula, and a bunch of values to use as replacements?




7 Responses to “Algebra”

  1. Harlan Grove Says:

    Writing your own parser. Classic wheel reinvention.

    Anyway, unless you were thinking about using single char function names you should be able to use single letters without underscores as positional parameters. However, if you think it’s difficult to follow straight Excel formulas, why would you think it’d be any easier to follow algebra(..) calls involving more than 5 or 6 parameters?

  2. Simon Says:

    Which existing parser should I be using?

    I was wanting to do a simple replace, not picking through each token so i was thinking ‘a’ would pick up abs() for example hence the _a_. Are you thinking regex?

    You’ll notice its limited to 6 args, for the very reason you mention. I think the real benefit would be where a cell ref is repeated, as its often hard to spot those.

  3. dougaj4 Says:

    Simon – I did something similar not so long ago:

    I pass the parameters and their values as an array, so there is no limit to the number, and you don’t have to check if each one is present.

    Eval is case sensitive, so I start all my parameters with an upper case, and if I think there is a danger of duplication I terminate them with a _. Functions are all lower case. There is a trade off between readability and reliability, but since the main point of doing it this way is to make the spreadsheet more readable I tend to favour just using an initial upper case, and making sure that there is no ambiguity in the parameter names.

    I like this idea a lot (one of the many things Microsoft could be improving, rather than messing around with the interface), but to be honest I don’t use it an awful lot; probably because 1) I haven’t got in the habit of using it, and 2) I haven’t got round to loading it at start up.

    One application where I have used it (I think quite effectivley) can be found here:

  4. Harlan Grove Says:

    Yes, I’m thinking regex because all other alternatives are soooo ugly!

    Actually, it looks like you only need the equivalent of a lexical analyzer to convert the tokens. A parser would only be needed to return diagnostics if the expression were syntactically invalid, e.g., unbalanced parentheses.

  5. Simon Says:

    Hi Doug
    Yes that is very similar (even down to using _), and I note that JonP has done something similar too.

    So it is obviously useful then, at least as a concept, I see we have all taken a slightly different approach.

  6. Nick Hebb Says:

    Fun idea! You could try a more generic solution using ParamArray and initialize the argument symbols:

    Private Const MAX_ARG_SYMBOLS As Integer = 26
    Private m_ArgSymbols() As String

    Public Function Algebra(ByVal TheFunction As String, _
    ParamArray Args() As Variant) _
    As Variant

    Dim i As Integer

    If Not IsMissing(Args) Then
    ‘ Allow A..Z
    If UBound(Args) > MAX_ARG_SYMBOLS – 1 Then Exit Function
    InitializeArgSymbols UBound(Args)
    For i = LBound(Args) To UBound(Args)
    If i <= UBound(m_ArgSymbols) Then
    TheFunction = Replace(TheFunction, m_ArgSymbols(i), Args(i))
    End If
    End If

    Algebra = Application.Evaluate(TheFunction)

    End Function

    Public Sub InitializeArgSymbols(ByVal MaxIndex As Integer)

    Dim i As Integer
    ReDim m_ArgSymbols(MaxIndex)

    For i = 0 To MaxIndex
    m_ArgSymbols(i) = “_” & Chr(65 + i) & “_”

    End Sub

  7. Nick Hebb Says:

    (I wish blog comments retained indentation integrity.)

    Remove this:
    If i <= UBound(m_ArgSymbols) Then

    I made some changes to InitializeArgSymbols that made it unnecessary and forgot to delete it.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: