VBA Excel : Argument not optional

Question

vba 非常新，我在一段代码中遇到了一些问题。本质上，我正在尝试对用户表单中列表框中的用户选择的项目执行不同的工作表功能。

Private Sub cmdRunStat_Click()
Dim averageValue As Single
Dim sdValue As Single
Dim maxValue As Variant
Dim minValue As Single
Dim modeValue As Single
Dim UserRange As String, sheetName As String


Set UserRange = ListBox1.Selected = True


If optAverage.Value = True Then

  averageValue = WorksheetFunction.Average(UserRange)
  MsgBox "The average of the selected data is " & averageValue

ElseIf optSD.Value = True Then

   sdValue = WorksheetFunction.StDev(UserRange)
   MsgBox "The standard deviation of the selected data is " & sdValue

ElseIf optMax.Value = True Then

   maxValue = WorksheetFunction.Max(UserRange)
   MsgBox "The maximum of the slected data is " & maxValue

ElseIf optMin.Value = True Then

   minValue = WorksheetFunction.Min(UserRange)
   MsgBox "The minimum of the slected data is " & minValue

Else

    modeValue = WorksheetFunction.Mode(UserRange)
    MsgBox "The mode of the slected data is " & modeValue

End If

End Sub

Answer 1

Set UserRange = ListBox1.Selected = True

这是分配给 String 的错误方式。目的。

如果您试图从列表框中获取选定的值，那么我认为这就是您需要的？

Dim UserRange As String

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        UserRange = ListBox1.List(i)
        Exit For
    End If
Next i

如果是 Range对象，那么您需要将以上内容更改为

Dim UserRange As Range

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        Set UserRange = ListBox1.List(i)
        Exit For
    End If
Next i