Pass function in VBA?

[Mark S.]

Is it possible to pass a function in VBA?
I know it can be done in VB, but I don't remember the
exact syntax...

 

[Steve Jorgensen]

Yuo can't pass a function reference in VB or VBA that I'm aware of, but there
are possible alternatives.

1. Try to make your code more OOP, and use classes and interfaces. Each
class has its own implentation of a particular method, and you pass around
references to class instances.
2. Use Application.Run to execute a function by name. Be careful with this
because the compiler can't tell you if you have an error.
3. Use Eval to execute an expression that evaluates a global function. Same
caveat as #2.

 

[Jonathan West]

Is it possible to pass a function in VBA?
I know it can be done in VB, but I don't remember the
exact syntax...

Hi Mark,

You can use the Run method or the CallMyname method. What exactly are you
trying to do?

 

[Mark S.]

I use a 3 (possibly more) functions that create SQL
statements depending on certain conditions. The function
is being called from another Function.

Instead of having 3 (or more) separate copies of the main
calling Function, I'd like to pass the function that
creates the SQL statement to the main function.

I know I could pass a number, then use a select statement
to sort out the proper SQL statement to create, but
passing a function, I thought, would be a more elegant
solution since the statements are very complex.

Hope that makes sense. Also, I remember creating a
numerical integration function to which I passed an
arbitrary function, way back in the old days, using VB
3.0. Am I just dreaming or is my memory going?

 

[Jonathan West]

You can use CallByname for this, but I don't think that there is any great
advantage in terms of performance or elegance in using it as apposed to a
Select Case statement that calls one function or another.

 

[Tushar Mehta]

It would have been easier to explain the below if you had been more
specific with routine names...

If the routines creating the SQL statements are Subs, convert them to
Functions that return a string value. Then, in the calling routine,
use the appropriate function and pass the resulting string. Something
like:

function SQL1(...) as string
...
function SQL2(...) as string
...
function SQL3(...) as string
...

sub CalledSub (SQLStatement as string)
...
sub MainControl()
...
calledsub choose (i, SQL1(), SQL2(), SQL3())
...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions