Reading the Excel cell dropdown values from macro

I have an excel file in which i have a cell which is a list and has values say开发者_如何学Python "USA", "India", "Europe", "London". I want to read all the dropdown values present in this cell from a macro... can anyone assist me please??

I am assuming your list in the excel sheet has been created using Data > Validation?

Assuming you have a list in cell A1 on your spreadsheet the following code will retrieve all values in the list.

Sub GetDropDownListValues()
    Dim sourceList As Range
    Set sourceList = Evaluate(Range("A1").Validation.Formula1)

    Dim cl As Range

    For Each cl In sourceList
        Debug.Print cl
    Next cl

End Sub

Hope that helps