Random Choice From Array

[sammclean23]

Hi All,

What I am trying to achieve is this:

"Write a function (selectProduct) that picks and displays a random product from an array of 5"

I did something similar with simple numbers:



Sub randomPrices()
Dim rndNumber As Integer
' Decalre as an integer
For rndNumber = 1 To 10
ActiveCell.value = Rnd() * 100
ActiveCell.Offset(1, 0).Activate
' Populates cells with a random number 0 to 100.
Next
'Next cell
End Sub


My guess is that I will have to declare the array, as below, then choose a number at random but not sure how:


list(0) = "xxx"
list(1) = "xxx"
list(2) = "xxx2
list(3) = "xxx"
list(4) = "xxx"



Would anybody be able to point me in the right direction?

I appreciate any help :)

Sam

[mdmackillop]

Sub ran()
Dim list(4)
list(0) = "xxx"
list(1) = "xxx"
list(2) = "xxx2 "
list(3) = "xxx"
list(4) = "xxx"
y = Application.WorksheetFunction.RandBetween(0, 4)
MsgBox y & " - " & list(y)
End Sub

[Kenneth Hobs]

You will need to enable the analysis toolpak if you use RandBetween.


Sub Test_RBetween()
Dim list(3) As Variant, i As Integer, j As Integer
For i = LBound(list) To UBound(list)
list(i) = "x" & i + 1
Next i
For j = 1 To 10
i = RBetween(LBound(list), UBound(list))
MsgBox "list(" & i & ") = " & list(i) & vbLf & "i = " & i
Next j
End Sub

Function RBetween(lowerbound As Long, upperbound As Long) As Long
Randomize
RBetween = WorksheetFunction.Floor((upperbound - lowerbound + 1) * Rnd + lowerbound, 1)
End Function

[sammclean23]

Excellent sure i'll be able to work it out from there.

Ill post the finished result up when it works

Thankyou

[sammclean23]

Option Explicit
Option Base 1

Public Function selectProduct() As String
Dim list(1 To 5) As String
Dim RndNum As Integer
list(1) = "3D"
list(2) = "Holographic"
list(3) = "LCD"
list(4) = "LED"
list(5) = "Plasma"
RndNum = Rnd() * 5 + 0.5
If RndNum = 1 Then
selectProduct = list(1)
ElseIf RndNum = 2 Then
selectProduct = list(2)
ElseIf RndNum = 3 Then
selectProduct = list(3)
ElseIf RndNum = 4 Then
selectProduct = list(4)
ElseIf RndNum = 5 Then
selectProduct = list(5)
End If
End Function


This works but is there anyway to simplify it?

I haven't used the above suggestions due to the fact that I am trying to avoid using too many built-in functions for the purpose of understanding.

Oh and thanks again for the help above :)

Edit: this doesn't work, I thought it did but it returns the value #name?

[mdmackillop]

If RndNum >= 1 And RndNum <= 5 Then
selectProduct = list(RndNum)
MsgBox selectProduct
End If

[sammclean23]

Thankyou!!!!

Appreciate the help, it now works.

The final code:



Option Explicit
Option Base 1

Public Function selectProduct() As String
Dim list(1 To 5) As String
Dim RndNum As Integer
list(1) = "3D"
list(2) = "Holographic"
list(3) = "LCD"
list(4) = "LED"
list(5) = "Plasma"
RndNum = Rnd() * 5 + 0.5
If RndNum >= 1 And RndNum <= 5 Then
selectProduct = list(RndNum)
MsgBox selectProduct
End If
End Function

[Kenneth Hobs]

Be sure to add Radomize or else LED will be the result at each new open of Excel and run.

[sammclean23]

Be sure to add Radomize or else LED will be the result at each new open of Excel and run.

Not familiar with that, can you explain please?

thankyou!

[mdmackillop]

From VBA Help

Randomize uses number to initialize the Rnd function's random-number generator, giving it a new seed value. If you omit number, the value returned by the system timer is used as the new seed value.

If Randomize is not used, the Rnd function (with no arguments) uses the same number as a seed the first time it is called, and thereafter uses the last generated number as a seed value.

[sammclean23]

From VBA Help

thanks mate, helped a lot!

I now have randomise within the code :)