Calling methods and properties by name in VBA with CallByName

Sometimes you don't know until run time which method or property you need to call. The name of the method or property might be in a worksheet cell, a file, a registry key or the Tag of a button, and you'd rather not write a thirty-line Select Case just to turn that String into a call.

VBA has a built-in function for exactly this: CallByName. It calls a method of an object, or reads or writes a Property of an object, from the member's name as a String. This post builds a small example (a Class plus a standard Module, output via Debug.Print) and then shows how it can earn its keep.

First, though, you might be wondering what's the difference between CallByName and Application.Run as they both can do similar things:

• Which host applications:

  • CallByName is a VBA Function ... it is a core part of the VBA language (part of the Interaction module) and is available in every application that hosts the VBA language

  • Run is a method of the Application object ... it is only available if that host application's Application object provides it ... for the Microsoft applications that host VBA, it is provided by Excel, Word, PowerPoint, Access and Project ... it is not provided by Outlook, Visio or Publisher

• What they call:

  • CallByName calls members / properties of objects ... this could be an instance of a Class module or any other object

  • Application.Run (amongst other things) runs methods in standard Modules (or Document Modules in some host applications ... and, in Excel for example, you must include the name of the Document Module)

Key things to note

• What it does: call a method, or get or set a Property, using the member's name as a String at run time. The signature is: CallByName(Object, ProcName, CallType, [Args() ...])

• The target must be an object (a Type or a bare value won't do), and ProcName is case-insensitive

• CallType is one of vbMethod, vbGet, vbLet or vbSet ... the VbCallType constants ... it tells VBA whether you're calling a method or using a Property (and how)

• It returns a Variant ... the Function's return value if a method or the Property's value if a Property Get (there's nothing useful to read back from a Sub, Property Let or a Property Set)

• It is genuinely late-bound: names resolve at run time, so a typo in the name will result in a run-time error 438 ('Object doesn't support this property or method') rather than an error at compile time

• There is no IntelliSense and it's slower than a direct call (so use this only when the name really isn't known until run time)

Demo

Add a Class Module, call it clsRobot, and paste in a Property and a couple of methods ... a Get, a Let, a Sub and a Function ...

Then add a standard Module and paste in a routine that calls each member by name ...

Then run DemoCallByName ... which will result in ...

A string-driven dispatcher

This is one way in which CallByName can really pay for itself. Suppose a command name arrives as a String ... from a cell, a registry read, a Ribbon control's Tag ... and you need to run the matching method. The usual answer is a Select Case that you extend every single time you add a command ...

CallByName can collapse the whole thing to a single line ...

If you add a new method to clsRobot then it's instantly callable by name with no Case to maintain. The catch is the one mentioned above ... no compiler-time safety net and no IntelliSense.

Also remember that CallByName doesn't only call members of your Class modules, it will call members of any object. For example (and coming back to Application.Run again ... for the applications that provide a Run method), if you have a Sub in a standard Module named MySub, you can call it using ...

... but, as CallByName calls members of objects and Application is an object, then you can also call it using ...

... where Application is the object, Run is the method / property, VbMethod because it is a method (Sub or Function) and "MySub" because that's its name! You'd never do this in code that you would deliver to a client, but it demonstrates the fact that CallByName can call members other than those you have defined in your own Class modules.