Passing a Formula as a Function Parameter 2

[TomJeffries]

I want to be able to pass an arbitrary formula to a function as a parameter (as in C-Language). For example

=Summation("X^2 + 2 * x", First, Last, Increment)

Function Summation(formula As String, ...)As Long
x = First
Do
Total = total + formula
x = x + Increment
Until x > Last
Summation = total
End

Is something like this even possible?

 

[Zathras]

Here is one way. It's not pretty, but it works.
[blue]

Option Explicit

Sub test()
  MsgBox Summation("X^2 + 2 * x", 3, 12, 2)
End Sub

Function Summation(formula As String, _
         First As Long, Last As Long, _
         Increment As Long) As Long
Dim X As Range
Dim Y As Range
Dim total As Long
  ActiveWorkbook.Names.Add Name:="X", _
     RefersToR1C1:="=" + ActiveSheet.Name + "!R1C249"
  Set X = Range("X")
  Set Y = X.Offset(0, 1)
  X.Value = First
  Y.FormulaR1C1 = "=" + formula
  Do
    total = total + Y.Value
    X.Value = X.Value + Increment
    Loop Until X.Value > Last
  Summation = total
  Set X = Nothing
  Set Y = Nothing
End Function

[/color]

 

[MichaelRed]

The following could be used for the simplistic expression (and variations on the SIMPLISTIC theme), but is primarily intended as an example of the use of the EVAL function in the context of a loop. There are MANY limitations in this implementation including (but far from limited to):

[tab]It is limited to functions of a single variable.

[tab]The expression to be evaluated must conform to VB arithmetic operators (including their syntax)

[tab]The expression cannot use User defined functions

[tab]The Variable in the Function must be "X"

Of course, it is possible to write parseing functions to overcome these limitations to some extent, or to expand the procedure to include more sophisticated functionallity, however that is left to the discretion of others.

Function basSeriesSum(strFnct As String, _
                      dblStrtVal As Double, _
                      dblLastVal As Double, _
                      dblIncrVal As Double) As Double

    'Michael Red    2/17/04 Simple example of the use of Eval _
     Returns the (four function) calculation of an expression _
     of one variable

    'Example Usage: _
     ? basSeriesSum("X^2 + 2 * X", 3, 12, 2) _
      355

    '? basSeriesSum("x", 1, 4, 1) _
     10

    '? basSeriesSum("x/25 + x* 3", 3, 12, 2) _
     106.4

    '? basSeriesSum("X - X/4", 20, 10, -2) _
     67.5

    Dim dblValX As Double
    Dim dblTmp As Double
    Dim MyFct As String

    dblValX = dblStrtVal
    Select Case dblIncrVal

        Case Is < 0
            While dblValX >= dblLastVal
                MyFct = Replace(strFnct, &quot;X&quot;, dblValX)
                dblTmp = dblTmp + Eval(MyFct)
                dblValX = dblValX + dblIncrVal
            Wend

        Case Is = 0
            dblValX = 0

        Case Is > 0
            While dblValX <= dblLastVal
                MyFct = Replace(strFnct, &quot;X&quot;, dblValX)
                dblTmp = dblTmp + Eval(MyFct)
                dblValX = dblValX + dblIncrVal
            Wend
        End Select
  
    basSeriesSum = dblTmp

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places