PDA

View Full Version : Random Choice From Array



sammclean23
01-01-2012, 10:22 AM
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
01-01-2012, 11:53 AM
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
01-01-2012, 12:02 PM
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
01-01-2012, 12:16 PM
Excellent sure i'll be able to work it out from there.

Ill post the finished result up when it works

Thankyou

sammclean23
01-01-2012, 12:31 PM
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
01-01-2012, 01:09 PM
If RndNum >= 1 And RndNum <= 5 Then
selectProduct = list(RndNum)
MsgBox selectProduct
End If

sammclean23
01-01-2012, 01:34 PM
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
01-01-2012, 02:11 PM
Be sure to add Radomize or else LED will be the result at each new open of Excel and run.

sammclean23
01-02-2012, 08:02 AM
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
01-02-2012, 08:34 AM
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
01-02-2012, 08:45 AM
From VBA Help

thanks mate, helped a lot!

I now have randomise within the code :)