Drop Down List of values into a VBA variable #ThinkingCapOn

[WarPigl3t]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Drop Down List
[/TD]
[/TR]
</tbody>[/TABLE]
Imagine A1 has a Drop Down List(DDL) and there are 3 values in that list.
Values:

1. dog
2. cat
3. unicorn

I want a Subroutine to be activated on button click event.
I know how to attach a Subroutine to a button.
I want to know how to pass the values of the drop down list into a variable in VBA.
So variableDDL(0) should be "dog"
variableDDL(1) should be "cat"
variableDDL(2) should be "unicorn"
I also need to know how to count how many values there are in that array. In this array, there are 3 values(dog,cat,unicorn).
My goal here is to select one of those three values and display it in cell A1. I want it to choose one of those values at random.

*Backround: The above example is just to explain things easily. My actual excel file is an Employee Work Schedule Builder. I want the schedule to build itself at the push of a button. I'm just letting you know so you don't think that I'm doing something stupid with dogs, cats, and unicorns. haha

 

[RoryA]

How was the drop down list set up? If it's data validation, does it use a range, or a comma separated list?

 

[WarPigl3t]

I have a list of values in other cells. Lets say for this example that I have this table.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drop Down List[/TD]
[TD]dog[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]cat[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]unicorn[/TD]
[/TR]
</tbody>[/TABLE]
I used the Name Manager located in the Formulas tab to insert a formula.
='Schedule '!$R$55:INDEX('Schedule '!$R$55:$R$105,COUNTIF('Schedule '!$R$55:$R$105,"?*")+1)
That is my actual formula for the sheet I'm really working with. So let me try to convert it to what my example is using.
='Sheet1'!$B$3:INDEX('Sheet1'!$B$3:$B$10,COUNTIF('Sheet1'!$B$3:$B$10,"?*"))
Okay so this formula will look at cells B3:B10 and only display the values dog, cat, and unicorn because there is no other data in the other 7 rows. It will not leave blanks in the drop down list for where the other 7 rows would normally go if there were values in them.
In the Data Validation screen located in the Data tab, I turned cell A1 into a List. I then type the name of the Formula that I mentioned in the previous step. Lets say I called that formula dynamicList when I made it. So now in the Source area in the Data Validation screen, I typed =dynamicList.

If you would like to get a clear picture of what I'm working with, you can watch my instructional video of how to operate the file. Skip to 2:12 to see where the dynamic list comes into play.
http://youtu.be/kwpuDaCgZ3M

 

[RoryA]

In that case, something like:

Dim v
    v = Application.Range(Mid$(ActiveCell.Validation.Formula1, 2)).Value

should give you an array containing the values - it will be a 2D array, rather than 1D though. If you must have 1D for some reason you can simply transpose it.

 

[WarPigl3t]

Thank you for your suggestion. I will research those commands that I am unfamiliar with.