CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

How do I convert a variant array into an Excel defined name?

I have a variant array:

vNames(1) = "Joe"
vNames(2) = "Sarah"
vNames(3) = "Lisa"
vNames(4) = "Erik"

How can I set this array to an Excel defined name? I want to save it as an Excel defined name in order to use it in data validation later.

Upvotes: 0

Views: 67

Answers (2)

Charles Williams
Charles Williams

Reputation: 23520

You can create a defined name that refersto an array of constants rather than a range


={"Joe","Sarah","Lisa","Erik"}

But I don't think data validation will accept such a name.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub qwerty()
    Dim vNames(1 To 4, 1 To 1) As Variant
    vNames(1, 1) = "Joe"
    vNames(2, 1) = "Sarah"
    vNames(3, 1) = "Lisa"
    vNames(4, 1) = "Erik"
    Range("A1:A4").Value = vNames
    Range("A1:A4").Name = "aRose"
End Sub

and then later:

.

enter image description here

Upvotes: 1

Related Questions