0

There are bunch of great questions asked and awesome answers given here about Delegates concept. Specifically this answer was more captivating. I have been working with VBA and early versions of Java and C#. I am trying to upgrade with useful and bit advanced concepts in later versions of C# as a matter of interest and work.

In VBA it's not necessary to build classes/interfaces to do something like bunch of calculations using load of parameters using a user defined function (UDF). We are able to use the UDF within another VBA function/sub or from Excel sheet.

e.g. following function in VBA takes few paramters and return a calculated value

Private Function DoSomeCalc(ByRef x as long, ByRef n as int, _ 
                            ByRef type as String) as long
//'--function implementation
End Function

In terms of understanding delegates in real world use, I had searched quite a bit and found this to be a good discussion. However it would be much helpful if I could relate what I already know.

Am I barking at the right tree here?

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91

2 Answers2

3

Sorry, but delegates don't work like Excel UDFs. You cannot pass a function or a subroutine as argument to another function in VBA.

Not sure if this rings a bell to you, but they really look like function pointers in C: a user data type that can hold the address of a function with a specific argument list and a definite return type; based on that you can store/call any function that matches the "signature"; it's what delegates allow, partially.

Now, on a side note, function pointers are around in C since approx. 1971... so delegates are like the bell-bottom jeans: back in fashion. :-)

  • I don't have a C background. And certainly we can't *pass a function* as **an argument** to a function or a sub in VBA -<<<<- this ring me the *holly bells*. We are able to call functions within functions (whether it's built in or user defined). It's time to get a hold of pointers for the background purpose. ;) – bonCodigo May 17 '14 at 02:55
2

Posting this here for any future searchers looking to implement delegates in vba.

I think the closest you could come in vba is to predefined which functions you're going to allow your "main" function to call with an enum, then use a case statement to determine which one to execute.

For example:

Enum eDelegateFunction
    eDelF_Foo
    eDelF_Bar
End Enum

Private Function Foo(n as double) as double
    Foo = n*2
End Function

Private Function Bar(n as double) as double
    Bar = n^2
End Function

Public Function Delegator(f as eDelegateFunction, n as double) as double
    Select case f
        Case eDelF_Foo
            Delegator = Foo(n)
        Case eDelF_Bar
            Delegator = Bar(n)
    End Select
End Function
RubberDuck
  • 11,933
  • 4
  • 50
  • 95