Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
589 views
in Technique[技术] by (71.8m points)

excel vba: Special Types - Functions as Arguments of Functions

There is no special type for functions in VBA. It is hard for me to see how to add functions as arguments to functions in Excel VBA.

What I am trying to accomplish is something like this:

function f(g as function, x as string) as string
        f = g(x)
end function

Currently, I have a group of little functions all repeating themselves but with one call to a specific function.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

From your code, function g takes a string parameter and returns a string. I suggest you create a class module called IStringFunction to act as the definition of an interface that all functions will support, thus:

Class Module: IStringFunction

Public Function Evaluate(ByVal s As String) As String
End Function

Then, create a couple of example functions implementing this interface:

Class Module: HelloStringFunction

Implements IStringFunction

Public Function IStringFunction_Evaluate(ByVal s As String) As String
    IStringFunction_Evaluate = "hello " & s
End Function

Class Module: GoodbyeStringFunction

Implements IStringFunction

Public Function IStringFunction_Evaluate(ByVal s As String) As String
    IStringFunction_Evaluate = "goodbye " & s
End Function

...and finally, some test code to exercise the functions:

(Standard) Module: Test

Sub Test()

    Dim oHello As New HelloStringFunction
    Dim oGoodbye As New GoodbyeStringFunction

    MsgBox Evaluate(oHello, "gary")
    MsgBox Evaluate(oGoodbye, "gary")

End Sub

Private Function Evaluate(ByVal f As IStringFunction, ByVal arg As String) As String
    Evaluate = f.Evaluate(arg)
End Function

Note that the class implementing the interface must have methods named <Interface>_<Method> as in the example above, not just <Method> as you'd expect.

Download the simple demo or intermediate demo here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

Just Browsing Browsing

[3] html - How to create even cell spacing within a

2.1m questions

2.1m answers

60 comments

57.0k users

...