Function name defined by a variable in VBA

Question

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!

None of the code you provide is even remotely executable. I suggest going back to square one, and learning how to write (and call) a very basic function in VBA, like one that simply sums 2 values or concatenates 2 string arguments. As it is, it is difficult to tell what you're asking, and it is going to be difficult to try and help you, since you haven't really demonstrated a minimum comprehension of the task at hand. — David Zemens
– David Zemens, Commented Sep 23, 2013 at 2:28
A simpler solution rather than VBA would be to use a calcsheet to derives the values of all the equations, then pick the appropriate result from the 8 columns. If you were ambitious you can used "Defined Names" to evaluate different funtions, I'd stick with the simple formula route — brettdj
– brettdj, Commented Sep 23, 2013 at 4:26

David Zemens · Accepted Answer · 2013-09-23 02:29:33Z

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

Doug · Accepted Answer · 2013-09-23 02:26:07Z

1

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.

answered Sep 23, 2013 at 2:26

Doug

5,3681 gold badge26 silver badges31 bronze badges

Comments

Boann · Accepted Answer · 2013-09-23 02:50:04Z

1

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.

edited Sep 23, 2013 at 2:50

answered Sep 23, 2013 at 2:40

Boann

50.3k16 gold badges125 silver badges153 bronze badges

1 Comment

David Zemens Over a year ago

+1 for a better explanation and answer in half as many words as mine :) Also worth noting, by making this an ordinary function requiring index as an argument, this avoids the need to use the annoying InputBox input method, unless that's critical to the application.

Collectives™ on Stack Overflow

Function name defined by a variable in VBA

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related