VBA Excel: Argument not optional

Question

Very new to vba and I'm having some issues with a piece of code. In essence, I'm trying to perform different worksheet functions on user selected items from a list box in a user form.

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

Are you sure the compiler error isn't "Object required" on the line Set UserRange = ListBox1.Selected = True? — Comintern
– Comintern, Commented Aug 4, 2016 at 16:22
you can't Set value to string . this is wrong Set UserRange = ListBox1.Selected = True — cyboashu
– cyboashu, Commented Aug 4, 2016 at 16:22
If you are using a ListBox to enter a string that represents a range, then you need to use Set UserRange = ListBox1.Value , just remember that string need to contain a format like Worksheets("Sheet1").Range("A1:D10") — Shai Rado
– Shai Rado, Commented Aug 4, 2016 at 16:50

Siddharth Rout · Accepted Answer · 2016-08-04 17:30:05Z

3

Set UserRange = ListBox1.Selected = True

That is an incorrect way to assign to a String Object.

If you are trying to get the selected value from the listbox then I think this is what you need?

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

And if it is a Range Object then you need to change the above as

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

answered Aug 4, 2016 at 17:30

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

S.Paul Over a year ago

Thanks for the input! I'll make those changes.

Collectives™ on Stack Overflow

VBA Excel: Argument not optional

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related