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

Question

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.

Not possible as specified - an Excel Defined Name is an alias for a Range, not for any general data structure. — Pieter Geerkens
– Pieter Geerkens, Commented Sep 14, 2014 at 17:26
Yes, you will have to copy the values to a sheet and then define a name for the range. — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Sep 14, 2014 at 17:29
@Pieter - An excel defined name is an alias for a formula, not a Range. — Charles Williams
– Charles Williams, Commented Sep 14, 2014 at 18:20
@Pieter, thats not correct, try defining a name with a refersto of =(1+2)*4 and you will see that its is not returning a Range, its returning the result of a formula which may or may not be a range. — Charles Williams
– Charles Williams, Commented Sep 14, 2014 at 18:39
@CharlesWilliams: It's a good day when learn something new and interesting; thank you.. — Pieter Geerkens
– Pieter Geerkens, Commented Sep 14, 2014 at 18:55

Gary's Student · Accepted Answer · 2014-09-14 17:43:14Z

1

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

answered Sep 14, 2014 at 17:43

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charles Williams · Accepted Answer · 2014-09-14 18:34:57Z

0

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.

answered Sep 14, 2014 at 18:34

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related