使用excelVBA，為什么我的子程式似乎使用Application.run運行了兩次？

在我的電子表格單元格中，我有一個帶有公式的單元格

=RunMacro("sample_macro('first';'second')", "雙擊我")

在我的模塊中，我有

Option Explicit

Function RunMacro(macro_with_semicolons_and_apostrophes As String, display As String)
    
    RunMacro = display

End Function

Public Sub sample_macro(one As String, two As String)
    MsgBox one
    MsgBox two
End Sub

在我的作業表代碼中，我有

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Left(Target.Formula, 10) = "=RunMacro(" Then
     ' Prevent default double-click action
     Cancel = True
     ' call function
     Application.run Replace(Replace(Mid(Target.Formula, 12, InStr(11, Target.Formula, ",") - 13), ";", ","), "'", """")
 End If
    
End Sub

根據需要，單元格顯示“雙擊我”。

根據需要，當雙擊單元格時，會執行 sample_macro。

根據需要，單擊任何其他單元格只會進入編輯模式。

不需要或不理解：我收到四個訊息框。“第一”、“第二”、“第一”、“第二”。

任何人都可以看到原因嗎？

Rory 和 Absinthe 建議更正 Application.run 陳述句中的語法。Ike 指出我必須在動態時分別傳遞宏名稱和引數。謝謝你們！

這就是我的結果。我認為通過雙擊包含要呼叫的宏名稱和引數的單元格來動態呼叫 excel 宏是一種很好的方法。

在單元格中，使用帶有宏和引數的 UDF 作為第一個元素，以分號分隔，文本顯示為第二個元素。像這些

=RunMacro("sample_macro2;first;second", "run a macro with two parameters")

=RunMacro("sample_macro1;first", "run a macro with one parameter")

=RunMacro("sample_macro0", "run a macro with no parameters")

在作業表的代碼中，具有以下內容

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Left(Target.Formula, 10) = "=RunMacro(" Then
    Dim myparams As Variant
    Dim mymacro As String
    Dim i As Integer
    ' Prevent default double-click action
    Cancel = True
    ' preparemacro name and parameters to be provided seperately to run function
    myparams = Split(Mid(Target.Formula, 12, InStr(11, Target.Formula, ",") - 13), ";")
    mymacro = myparams(0)
    ' stip off first element now saved in string
    If UBound(myparams) > 1 Then
        For i = 1 To UBound(myparams)
            myparams(i - 1) = myparams(i)
        Next i
        ReDim Preserve myparams(UBound(myparams) - 1)
        Application.Run mymacro, myparams
    ElseIf UBound(myparams) = 1 Then
        Application.Run mymacro, myparams(1)
    Else
        Application.Run mymacro
    End If
    
 End If
    
End Sub

然后要呼叫的宏，如果它有兩個或更多引數，必須將它們作為陣列接收。這是模塊的內容。當然，UDF 是必需的。其他都是樣品。

Option Explicit

Function RunMacro(macro_with_semicolons_and_apostrophes As String, display As String)
    ' this is the UDF.  It permits us to hold the name of the macro and the parameteres (if any) in the cell
    ' but it merely displays the display text unless/until double-clicked
    RunMacro = display
End Function

Public Sub sample_macro2(arrParameter As Variant)
    ' macros with 2 or more parameters must receive them as an array
    Dim i As Long
    For i = LBound(arrParameter) To UBound(arrParameter)
        MsgBox arrParameter(i)
    Next
End Sub

Public Sub sample_macro1(myparam As Variant)
    MsgBox myparam
End Sub

Public Sub sample_macro0()
    MsgBox "you've reached two"
End Sub

uj5u.com熱心網友回復：

這有效 - 但你必須將引數作為陣列傳遞

Application.Run 由宏 - Arg1 - 語法呼叫。

作業表模塊

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Left(Target.Formula, 10) = "=RunMacro(" Then
     ' Prevent default double-click action
     Cancel = True
     ' call function
     'Application.Run Replace(Replace(Mid(Target.Formula, 12, InStr(11, Target.Formula, ",") - 13), ";", ";"), "'", """")
     'Application.Run "sample_macro(""first"";""second"")"
     Application.Run getMacro(Target.Formula), getParameter(Target.Formula)
 End If

End Sub


Private Function getMacro(value As String) As String
getMacro = Mid(value, InStr(value, "(")   2)
getMacro = Left(getMacro, InStr(getMacro, "(") - 1)
End Function

Private Function getParameter(value As String) As Variant
getParameter = Mid(value, InStr(value, "'"))
getParameter = Left(getParameter, InStr(getParameter, ")") - 1)
getParameter = Split(Replace(getParameter, "'", vbNullString), ";")
End Function

模塊

Function RunMacro(macro_with_semicolons_and_apostrophes As String, display As String)
    RunMacro = display
End Function

Public Sub sample_macro(arrParameter As Variant)
    Dim i As Long
    For i = LBound(arrParameter) To UBound(arrParameter)
        MsgBox arrParameter(i)
    Next
End Sub

標籤：擅长 vba

上一篇：嘗試使用find方法到達單元格的地址

下一篇：動態SUM函式和過濾