Issue getting the value from an array instead of index

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Code:
Sub GetOutPut()

Dim V1, V2, V3, V4, V5
Dim TheList 
Dim output


V1 = "This One"
V2 = "The Boy"
V3 = "That One"
V4 = "Car Race"
V5 = "Cash Money"

TheList = Array(V1, V2, V3, V4, V5)

Output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))

MsgBox TheList(output)

End Sub

My aim is to pull randomly from the list meanwhile its showing me the list index instead. How do I get the value instead. Say

"The Boy" instead of 1?
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Like this?

Code:
Sub GetOutPut()


Dim V1, V2, V3, V4, V5
Dim TheList
Dim output

V1 = "This One"
V2 = "The Boy"
V3 = "That One"
V4 = "Car Race"
V5 = "Cash Money"


TheList = Array(V1, V2, V3, V4, V5)


output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))


MsgBox TheList(output)


End Sub
 
Last edited:
Upvote 0
Like this?

Code:
Sub GetOutPut()


Dim V1, V2, V3, V4, V5
Dim TheList
Dim output

V1 = "This One"
V2 = "The Boy"
V3 = "That One"
V4 = "Car Race"
V5 = "Cash Money"


TheList = Array(V1, V2, V3, V4, V5)


output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))


MsgBox TheList(output)


End Sub


Yes. I just figured it out few seconds after the post. Have been on this for the past 1 hour. :LOL:
 
Upvote 0
Just a comment: vba has its own random number generation facility so there is really no need to revert to the worksheet one
Code:
<del>output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))</del>
output = LBound(TheList) + Int(Rnd() * (UBound(TheList) + 1))
 
Upvote 0
Just a comment: vba has its own random number generation facility so there is really no need to revert to the worksheet one
Code:
<del>output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))</del>
output = LBound(TheList) + Int(Rnd() * (UBound(TheList) + 1))


Okay. I will amend that ASAP
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top