CODEWITHSUNDEEP
functionvbaexcel
J Dee
J Dee

Reputation: 31

Function name defined by a variable in VBA

New VBA user here, engineer-not a programmer. Your patience is appreciated. Did spend hours in help and online trying to find this answer. Found some answers to this, but they were for php. Working with MS Office Home and Student 2007.

I have a list of 8 equations. I have the user input the number of the equation they want to evaluate, I call that number index. Is there a way to put the index into a function name when it's defined? such as:

Function PlotFun[index]= some f(x)

Or is it possible to redefine the function using an index within a subroutine? such as:

If index=1 then PlotFun=PlotFun[index] 'so PlotFun would equal PlotFun1

I think I read in the help that a function has to be defined in a function routine, so this one probably isn't feasible.

Or is it possible to read the function from a cell in excel? I tried using 

Function PlotFun=Worksheets(1).Range("BC6")

where BC6 contains "24 - 50 * x + 35 * x ^ 2 - 10 * x ^ 3 + x * 4" which is correct syntax for a function of x, as it was copied from my PlotFun1 routine. But I don't have the syntax down right. I get Compile Error: Expected: End of statement, right at the equals sign.

I've also been referred to the lamda function, but again, I get the Expected: End of Statement error.

Sub Bisection()
Dim index
Call FuncIndex  'sets the index number for which equation to use for analysis
        If index = 1 Then
            Dim PlotFun=Function(x) x ^ 3 - 6 * x ^ 2 + 11 * x - 6
        If index = 2 Then
            Dim PlotFun=Function(x) 24 - 50 * x + 35 * x ^ 2 - 10 * x ^ 3 + x * 4
Do 'my calculations using PlotFun
Loop 'for specified error or iterations
End Sub

I need to use PlotFun or PlotFun1 in a few places within many subroutines. I don't want to have to write/copy code for each f(x). Perhaps there is a better way to do this than what I've thought of? I cannot use an array, as the equations are not all polynomials.

Thank you!

Upvotes: 2

Views: 2285

Answers (3)

Boann
Boann

Reputation: 50010

VBA is the worst language to try to do this in. (Actually, it's the worst language to try to do most things in.)

VBA functions are unfortunately not values or objects, and they cannot be stored in variables whatsoever. Therefore, none of this is remotely legal:

Function PlotFun[index]= some f(x)

Function PlotFun=Worksheets(1).Range("BC6")

Dim PlotFun=Function(x) x ^ 3 - 6 * x ^ 2 + 11 * x - 6

Since you don't need too many functions, the easiest solution is this:

Function PlotFun(index, x)
    Select Case index
    Case 1
        PlotFun = x ^ 3 - 6 * x ^ 2 + 11 * x - 6
    Case 2
        PlotFun = 24 - 50 * x + 35 * x ^ 2 - 10 * x ^ 3 + x * 4
    Case 3
        ' ... and so on
    End Select
End Function

I.e., declare PlotFun as an ordinary function, and use your index variable to tell it what internal expression to evaluate. The disadvantage is that you will have to include the function index variable everywhere you want to call PlotFun.

Note that the PlotFun = ... syntax here does not assign the function itself, but rather is VBA's way of setting the return value from a particular invocation of the function. Within the function, PlotFun is just the name of a variable to assign to.

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

Is there a way to put the index into a function name when it's defined? such as:

Not in-line really. You can use a select case switch, or a series of if/else statements. I ordinarily prefer the select case method.

NOTE This code does not specify any value for x, so it will be interpreted as an Empty or zero-value, unless you change the code or provide a means for user to input the variable x value.

Sub WhichFunction()

Dim i as Long
Dim functionResult as Double
i = Application.InputBox "Please enter the function's index #"
Select Case i
    Case 1
        functionResult = x ^ 3 - 6 * x ^ 2 + 11 * x - 6
    Case 2
        functionResult = - 50 * x + 35 * x ^ 2 - 10 * x ^ 3 + x * 4
    Case 3
        'etc.
    Case 4

    Case 5

    Case 6

    Case 7

    Case 8

    Case else
        MsgBox "invalid function index!"
End Select
    MsgBox functionResult
End Sub

About your code:

I get the Expected: End of Statement error

Probably because your Dim statements don't end where they should. In VBA, you generally can't declare and simultaneously assign a variable. There are exceptions, and visual shortcuts, but that's not necessary to discuss here.

Or is it possible to read the function from a cell in excel? I tried using Function PlotFun=Worksheets(1).Range("BC6")

Yes, it is possible for a function to read from a specific cell, but not the way you're implementing it. YOu need to go back to basics and learn how to create a function, e.g,:

Function MyFunctionName(arg1 as integer, arg2 as integer)
    'Code the evaluates some methods on the arguments provided
    Dim myVal as Double
    myVal = arg1 ^ arg2
    MyFunction = myVal 'Return the calculated value
End Function

Likewise you could do:

Function GetValueFromSheet(byVal cl as Range)
'This function will ALWAYS return the value in B6
    GetValueFromSheet = Range("B6").Value
End Function

Upvotes: 1

Doug
Doug

Reputation: 5318

It sounds like you want to vary what algorithm is used, or in your case what equation is called based on a parameter that you would pass to a single function. This problem can be solved by the use a of a well know software pattern known as the "strategy pattern". This pattern allows you to vary an algorithm while keeping its implementation the same.

You can combine the strategy pattern with a factory pattern to achieve the desired architecture you are seeking.

Here is a fairly clear vba implementation of a strategy pattern.

Hope this helps.

Upvotes: 1

Related Questions