I’ve been doing some programming in Excel’s VBA recently. I have a series of blocks of code, where the only thing different in the blocks is that each block calls a different function. Pseudo-code resembles
blah1 blah2 Call Function1 blah3 blah4 ... blah1 blah2 Call Function2 blah3 blah4 ... blah1 blah2 Call Function3 blah3 blah4 ...
The obvious change here would be to create a subroutine with one of the blocks of code, and to call the subroutine passing the unique function as a parameter to the subroutine. Excel 2003 VBA does not permit this directly, so my next thought was to use Eval, and pass the function name as a string. Excel 2003 VBA does not include Eval.
But… Excel VBA does permit one to pass an object as an argument. So the solution becomes:
- Define 3 different objects, and give each object a single function, each function named the same.
- Pass the object as an argument, instead of the function.
- Have the subroutine call the function via the object.