Returning Values By Reference in Ribbon Callbacks
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.
Ribbon Callbacks
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()
End SubIn 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.
The 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.
If the UserIsAdmin() function returns True, the Enabled argument is set to True, and the button is enabled. If the function returns False, the 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.
Referenced articles
Cover image created with Microsoft Designer