VBA代码编译错误：使用Formula1时找不到命名参数求助

错误原因 #

你使用Application.InputBox时设置了Type:=8，这个参数代表要获取单元格区域引用，而Formula1是工作表数据验证（Validation）的专属参数，根本不属于这种类型的InputBox，因此触发编译错误。

解决方案 #

要实现带可选列表的弹窗选择，推荐以下两种方法：

方法1：用UserForm创建下拉选择框 #

这是最稳定直观的方式，步骤如下：

1. 打开VBA编辑器，右键项目→插入→用户窗体，添加1个ComboBox控件、2个CommandButton（命名为btnOK和btnCancel）
2. 给UserForm添加代码：

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Array("X-Small", "Small", "Medium", "Large", "X-Large")
    Me.Caption = "Profile Size List"
End Sub

Private Sub btnOK_Click()
    Insert_Profile_Selected = Me.ComboBox1.Value
    Me.Hide
End Sub

Private Sub btnCancel_Click()
    Insert_Profile_Selected = ""
    Me.Hide
End Sub

3. 修改主过程代码：

' 模块级变量，传递选择结果
Dim Insert_Profile_Selected As String

Sub Insert_Profile()
    Dim ChosenMacro As String
    
    UserForm1.Show
    ChosenMacro = Insert_Profile_Selected
    
    If ChosenMacro = "" Then Exit Sub ' 用户取消选择
    
    Select Case ChosenMacro
        Case "X-Small"
            Workings_Insert_XS_Profile
        Case "Small"
            Workings_Insert_S_Profile
        Case "Medium"
            Workings_Insert_M_Profile
        Case "Large"
            Workings_Insert_L_Profile
        Case "X-Large"
            Workings_Insert_XL_Profile
    End Select
End Sub

方法2：临时使用工作表数据验证创建下拉 #

如果不想用UserForm，可以临时在单元格生成下拉列表，引导用户选择：

Sub Insert_Profile()
    Dim MacroNames As Variant
    Dim ChosenCell As Range
    Dim ChosenMacro As String
    
    MacroNames = Array("X-Small", "Small", "Medium", "Large", "X-Large")
    
    ' 在临时单元格创建数据验证下拉（选一个不影响的单元格，比如Sheet1的A1）
    With ThisWorkbook.Sheets("Sheet1").Range("A1")
        .Validation.Delete
        .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(MacroNames, ",")
        .Select
    End With
    
    ' 弹出输入框让用户选择单元格（即选择下拉项）
    Set ChosenCell = Application.InputBox(Prompt:="Please select Profile Size from the dropdown", Title:="Profile Size List", Type:=8)
    ChosenMacro = ChosenCell.Value
    
    ' 清理临时数据验证
    ChosenCell.Validation.Delete
    
    ' 执行对应宏
    Select Case ChosenMacro
        Case "X-Small"
            Workings_Insert_XS_Profile
        Case "Small"
            Workings_Insert_S_Profile
        Case "Medium"
            Workings_Insert_M_Profile
        Case "Large"
            Workings_Insert_L_Profile
        Case "X-Large"
            Workings_Insert_XL_Profile
    End Select
End Sub

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