VBA中调用带参数子过程出现Compile Error: Argument not optional问题求助

Hey there! Let's break down why you're hitting that compile error and how to fix it.

The Root Cause #

Your StoreQA subroutine is declared to require a Shape parameter (ShapeClicked As Shape), but when you call it in ThisIsToBeRun with just StoreQA, you aren't passing any argument to it. VBA throws the "Argument not optional" error because it expects you to provide that Shape object, but you didn't. Your attempt to write StoreQA(ShapeClicked As Shape) also doesn't work because you can't declare parameter types when calling a sub—those declarations only belong in the sub's definition.

Solutions Based on Your Use Case #

1. If you want StoreQA to run when a shape is clicked

This is probably what you intended, since the sub takes a ShapeClicked parameter. Here's how to set it up correctly:

• Right-click the shape in your Excel worksheet.
• Select Assign Macro from the menu.
• Choose StoreQA from the list of macros and click OK.

Now, when you click the shape, Excel will automatically pass the clicked Shape object to StoreQA, and your code will run without errors.

2. If you need to call StoreQA manually from ThisIsToBeRun

You need to explicitly pass a valid Shape object to the sub. Here's an example:

Sub ThisIsToBeRun()
    ' Define and set a target Shape (adjust the sheet and shape name to match yours)
    Dim targetShape As Shape
    Set targetShape = ThisWorkbook.Sheets("Sheet1").Shapes("MyButtonShape")
    
    ' Call StoreQA with the Shape object
    StoreQA targetShape
End Sub

Sub StoreQA(ShapeClicked As Shape)
    MsgBox "yeee"
    ' If you want to use the ShapeClicked parameter later, you can do things like:
    ' MsgBox "You clicked: " & ShapeClicked.Name
End Sub

If you just want to test the sub without a real Shape, you can pass Nothing (note: this is only for debugging, as Nothing means no actual Shape object exists):

Sub ThisIsToBeRun()
    StoreQA Nothing
End Sub

Quick Note on Calling Subs #

When calling a sub without the Call keyword, you don't need parentheses around the argument. If you do use Call, you must add parentheses:

' Valid without Call
StoreQA targetShape

' Valid with Call
Call StoreQA(targetShape)

内容的提问来源于stack exchange，提问作者Bhavesh Shaha