Search code examples
excelvbaoopdecorator

Decorator pattern in VBA

Is there anyway to achieve the "Decorator-pattern" such as below in Excel VBA ?

Sub runner(subroutine)
MsgBox ("Decoration Before")
Call subroutine
MsgBox ("Decoration After")
End Sub

Sub toBeDecorated()
MsgBox ("to be decorated")
End Sub

Sub fire()
Call runner(toBeDecorated)
End Sub

When I try to run the sub "fire", The compiler throws "Compile error: Expected Function or variable"

Solution

  • There are no function/subroutine pointers in VBA, so you can't pass a function or subroutine as parameter. As a consequence, Call subroutine will never be valid: The compiler looks for a routine named subroutine. This happens at compile time, not at execution time and will create the Compiler error.

    A workaround could be using Application.Run: The Run-method expects the name of a routine as String, so it will evaluate subroutine at runtime:

    Sub runner(subroutine As String)
    MsgBox "Decoration Before"
    Application.Run subroutine
    MsgBox "Decoration After"
End Sub

Sub toBeDecorated()
    MsgBox "to be decorated"
End Sub

Sub fire()
    Call runner("toBeDecorated")
End Sub