VBA - Passing a FUNCTION as an Argument

  • Thread starter Thread starter James B
  • Start date Start date
J

James B

Some expert help is required on this one. I want to
create a VBA function that calculates the derivative of
another function between two points.

The syntax would be something like the following:

Gradient = DerFun(Height(), x1, x2)

where

Function DerFun(Arg_Function as function, point1, point2)
DerFun=(Arg_Function(point2)-ArgFunction(point1))/ _
(point2-point1)
End Function

What is the proper syntax, instead of "Arg_Function as
Function"?
 
James,

You can't pass function pointers in VBA. The closest you can get
is to pass the name of the procedure as an string and use
Application.Run to run the procedure. E.g.,

Function DerFunction(F As String, Point1,Point2)
DerFunction = Application.Run(F)
End Function

DerFunction "TheFunctionName", 1, 2


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
James, A function is not a datatype, so you cannot say Arg_Function as
Function, you have to use the datatype that the function returns, or
variant.

Here is a simple working example

Sub test()
MsgBox Func1(Func2, 9)
End Sub

Function Func2() As Long
Func2 = 7
End Function

Function Func1(arg1, arg2 As Long) As Long
Func1 = arg1 * arg2
End Function



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top