Passing a Formula as a Function Parameter 2

TomJeffries · Feb 17, 2004

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 · Feb 17, 2004

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

Code:

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 · Feb 18, 2004

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.

Code:

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(&quot;X^2 + 2 * X&quot;, 3, 12, 2) _
      355

    '? basSeriesSum(&quot;x&quot;, 1, 4, 1) _
     10

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

    '? basSeriesSum(&quot;X - X/4&quot;, 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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Passing a Formula as a Function Parameter 2

TomJeffries

Technical User

Zathras

Programmer

MichaelRed

Programmer

Similar threads

Part and Inventory Search

Sponsor