This article is one in a series on Subs vs. Functions in VBA.
One of the "additional facts" from my Subs vs. Functions article was this:
Sometimes you MUST use a
Sub, even if you do need its return value (e.g., ribbon callbacks).
This is something of a corollary to the fact that sometimes you MUST use a
Function, even if you don't need its return value.
An Office ribbon callback is a VBA subroutine that gets triggered in response to various ribbon events.
Some events occur automatically, such as when the ribbon initially loads. Other events occur in response to user actions, such as clicking on a button. Some callbacks can be forced to run by using the Invalidate method, which forces reevaluation of every callback in the ribbon, or the InvalidateControl method, which is restricted to a single control.
Some ribbon callbacks, such as those associated with the getVisible and getEnabled ribbon properties, must return information to the Ribbon UI. Others, such as those set in the onAction and onChange properties, are simply raised by the ribbon but do not return information to the ribbon.
"Returning" Values to a Ribbon Callback
For technical reasons having to do with COM, you cannot use a Function to return a value to the ribbon.
To return a value from a ribbon callback, you need to pass the value back to the calling procedure using an argument that has been passed by reference. When you pass an argument by reference, you pass a reference to the memory location of the variable, rather than a copy of the variable itself. This means that any changes made to the variable within the called Sub are reflected in the calling procedure.
For example, consider a ribbon button defined with the following XML:
<button id="MyCustomButton" label="My Custom Button" size="large"
onAction="MyButton_Click" getEnabled="MyButtonEnabled" />
When the ribbon first loads, the
MyButtonEnabled() Sub will be called. Here's what that sub might look like:
Sub MyButtonEnabled(Control As IRibbonControl, ByRef Enabled)
Enabled = UserIsAdmin()
In this example, the
MyButtonEnabled() Sub is the ribbon callback for the getEnabled property of a ribbon command button. The
Control argument represents the control that triggered the callback.
ByRef Enabled argument is used to pass the return value of the
UserIsAdmin() function back to the calling procedure. The
UserIsAdmin() function is a custom function that determines whether the current user is an administrator.
UserIsAdmin() function returns
Enabled argument is set to
True, and the button is enabled. If the function returns
Enabled argument is set to
False, and the button is disabled.
By using a ribbon callback for the getEnabled property and passing the return value of the
UserIsAdmin() function back to the calling procedure, we can ensure that the button is only shown to users who have administrative privileges.
Cover image created with Microsoft Designer