Need to use a Range for the arguments of the Choose Function in VBA.

[PhiLipka]

Hello Excel community,

I've recently fell in love with Excel VBA about 4 months ago, and I have learned a bunch in this short period of time. Usually I find the answer to something I need relatively quickly, but I have stumped myself and hope you may get some enjoyment trying to help me figure this out.

Basically I would like to choose a random option based on a list that can grow to any size. The options to choose from will be in a particular range, but the range may be 2 words or 20 words. I have messed around with a few different ways of trying this and have gotten close, but no cigar. I'll paste where I left off with the code. Any help is appreciated for this nooby .

Thanks,
Phil

Function RandBetween(Low As Integer, High As Integer) As Integer
RandBetween = WorksheetFunction.RandBetween(Low, High)
End Function

Function choose(CellRange As Range) As String
Dim Cell As Range
Dim x As Integer
x = CellRange.Cells.Count
choose = WorksheetFunction.choose(RandBetween(1, x), concat(CellRange))
End Function

Function concat(CellRange As Range) As Collection
Set concat = New Collection
Dim Cell As Range
Dim x As Integer

x = CellRange.Cells.Count
For Each Cell In CellRange.Cells
concat.Add Cell.Value
Next Cell
End Function

 

[DanteAmor]

Apparently you cannot use an array as an argument in the parameters of the choose function

WorksheetFunction.choose(RandBetween(1, x), concat(CellRange))

But you can try the following. Choose the random number directly from the collection:

Sub test()
  MsgBox wChoose(Range("B2:B10"))
End Sub

Function wChoose(CellRange As Range) As String
  Dim xval As Collection
  Set xval = concat(CellRange)
  wChoose = xval(wRandBetween(1, CellRange.Cells.Count))
End Function

Function wRandBetween(Low As Integer, High As Integer) As Integer
  wRandBetween = WorksheetFunction.RandBetween(Low, High)
End Function

Function concat(CellRange As Range) As Collection
  Dim Cell As Range
  Set concat = New Collection
  For Each Cell In CellRange.Cells
    concat.Add Cell.Value
  Next Cell
End Function

 

[PhiLipka]

Thanks for the help, I tried that code and it did not quite get it there. The message box was always empty, but I started from scratch figured it out. Thanks for helping me think outside the box

Function RandomChoice(CellRange As Range) As Integer
Dim Cell As Range
Dim ItemCount As Integer
Dim x As Integer
ItemCount = CellRange.Cells.Count
RandomChoice = WorksheetFunction.RandBetween(1, ItemCount)
x = 0
    For Each Cell In CellRange.Cells
x = x + 1
If x = RandomChoice Then
RandomChoice = Cell.Column
Exit Function
End If

    Next Cell

End Function

Sub PickRandomStringFromRange()
Dim RandomString As String
Dim CellRange As Range
Set CellRange = Sheet2.Range("b4:d4")
RandomString = Cells(CellRange.Row, RandomChoice(CellRange)).Value
MsgBox RandomString
End Sub

 

[DanteAmor]

Thanks for the help, I tried that code and it did not quite get it there. The message box was always empty, but I started from scratch figured it out. Thanks for helping me think outside the box

The range B2:B10, must have data.
But I'm glad to know that you found a solution. Glad to help you. Thanks for the feedback.