vba 自定义功能区 onAction 语法问题

Custom Ribbon onAction syntax question

提问by jeremiahs

I followed the directions hereto create a custom ribbon for an Access application. But none of the buttons worked! I kept getting an error that stated Access couldn't find the function or macro, even though it was public and in a standard module.

我按照此处的说明为 Access 应用程序创建了自定义功能区。但是没有一个按钮起作用！我不断收到错误消息，指出 Access 找不到该函数或宏，即使它是公共的并且位于标准模块中。

Eventually I discovered that it would work if I used the following syntax:

最终我发现如果我使用以下语法它会起作用：

onAction="=fncMyFunction('string argument', 1234)"

onAction="=fncMyFunction('string argument', 1234)"

fncMyFunctionreceives the manually typed in arguments, but not the ribbon object.

fncMyFunction接收手动输入的参数，但不接收功能区对象。

In Word for another project, I created a custom Ribbon by opening the document up as a .ZIP file, adding the XML in the appropriate place, and adding a reference to it. Relevant directions somewhere in this novel here.

在另一个项目的 Word 中，我通过将文档作为 .ZIP 文件打开，在适当的位置添加 XML 并添加对它的引用来创建自定义功能区。 这篇小说中某处的相关说明在这里。

In Word, I was able to have everything work the way I expected it to with the following syntax:

在 Word 中，我能够使用以下语法让一切按照我预期的方式工作：

onAction="fncMyFunction"

onAction="fncMyFunction"

In Word, fncMyFunctionhas a ribbon object passed to it when the button is clicked.

在 Word 中，fncMyFunction单击按钮时会传递一个功能区对象。

What's the deal here? Why the different syntax? And is one way or the other "wrong?"

这里有什么交易？为什么不同的语法？是一种方式还是另一种方式“错误”？

回答by Renaud Bompuis

You should use the ribbon element's tagproperty to store some values you want to pass to your action.

您应该使用功能区元素的tag属性来存储要传递给操作的一些值。

For instance, say you have a simple ribbon containing a few buttons:

例如，假设您有一个包含几个按钮的简单功能区：

• the first button uses a generic action ribbonOpenFormthat opens a form FormDashBoardFinancewhen clicked.
• the second button uses a generic action ribbonDoActionthat execute the LogOff("bye")VBA function(not a Sub!) that, for instance, displays a message to the user and logs off.
• the last one duplicates the behaviour that you wanted for your fncMyFunction().

• 第一个按钮使用通用操作ribbonOpenForm，FormDashBoardFinance单击时会打开一个表单。
• 第二个按钮使用ribbonDoAction执行LogOff("bye")VBA函数（不是 Sub！）的通用操作，例如，向用户显示消息并注销。
• 最后一个复制了您想要的行为fncMyFunction()。

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
          onLoad="ribbonLoad" loadImage="ribbonLoadImage">
   <ribbon startFromScratch="false">
        <tabs>
         <tab id="Home" label="Home">
                   <group id="gpDash" label="Dashboards">
                        <button id="btHomeFinance"
                                label="Finance"
                                imageMso="BlogHomePage"
                                onAction="ribbonOpenForm" 
                                tag="FormDashBoardFinance"/>
                        <button id="btLogOff"
                                label="Log Off"
                                imageMso="DatabasePermissionsMenu"
                                onAction="ribbonDoAction" 
                                tag="LogOff('bye')"/>
                        <button id="btMyFunc"
                                label="My Function"
                                imageMso="AppointmentColorDialog"
                                onAction="fncMyFunction" 
                                tag="'a string argument', 1234"/>
                   </group>
             </tab>
        </tabs>
   </ribbon>
</customUI>

The VBA to manage the ribbon would be in a module:

管理功能区的 VBA 将在一个模块中：

Option Compare Database
Option Explicit

' We keep a reference to the loaded Ribbon
Private ribbon As IRibbonUI

'-----------------------------------------------------------------------------
' Save a reference to the Ribbon
' This is called from the ribbon's OnLoad event
'-----------------------------------------------------------------------------
Public Sub ribbonLoad(rb As IRibbonUI)
    Set ribbon = rb
End Sub

'-----------------------------------------------------------------------------
' Open the Form specified by the ribbon control's Tag.
'-----------------------------------------------------------------------------
Public Sub ribbonOpenForm(control As IRibbonControl)
    DoCmd.OpenForm control.tag, acNormal
End Sub

'-----------------------------------------------------------------------------
' Perform the action specified by the ribbon control's Tag
' Use single quotes to delimit strings, they will be expanded.
' The action to be performed must be defined as a public Function!
'-----------------------------------------------------------------------------
Public Sub ribbonDoAction(control As IRibbonControl)
    Dim action As String
    action = Replace(control.Tag,"'","""")
    Eval action
End Sub

'-----------------------------------------------------------------------------
' fncMyFunction example implementation
' Use single quotes to delimit strings, they will be expanded.
'-----------------------------------------------------------------------------
Public Sub fncMyFunction(control As IRibbonControl)
    ' Split the string to separate the paramaters in the Tag
    Dim params As Variant
    params = Split(control.Tag, ",")
    ' Now we can assign each parameter
    Dim myString As String
    Dim myInt As Integer
    myString = Replace(Trim(params(0)),"'","") ' remove single quotes
    myInt = CInt(Trim$(params(1)))             ' We're expecting an Integer
    ' ... do something with the params ...
    Debug.Print myString  ' Will print: a string argument
    Debug.Print myInt * 2 ' Will print: 2468
End Sub

An excellent resource for the Access Ribbon is Avenius Gunter's Access 2010 Ribbon site

Access Ribbon 的一个很好的资源是Avenius Gunter 的 Access 2010 Ribbon 站点