Hello

I would like to do the following, but I am not sure it is doable in
VBA. I saw it in Php, so I have a little hope it is.

I have a spreadsheet full of range names. They are all of the form
"S1.Name3.Subname2". Basically I would like to load in VBA all the
values from these rangenames in one macro.

For the moment I do it manually be doing

dim S1_Name3_Subname2
S1_Name3_Subname2=Thisworkbook.Names("S1.Name3.Subname2").RefersToRange.Value2

and I do the same for all the range names. I have found on a post on
this group a lovely program called NameManager that allows to extract
all range names from a workbook into a spreadsheet. Creating with an
excel formula the code above is the easy with a few excel formula and a
copy paste.

Is there a way to go through all the range names of a spreadsheet
(relatively easy) and to create VBA array names based on these names
(that's the piece I can't do)?

thanks
Charles

Hi

A code example - maybe it helps you a bit:

Public Sub Test()
Dim MyArray() As Variant
Dim rngTest As Range
Dim RowNum As Integer
Dim ColNum As Integer

Set rngTest = [TestRange]
RowNum = rngTest.Rows.Count
ColNum = rngTest.Columns.Count
ReDim MyArray(RowNum, ColNum)
MyArray = rngTest

End Sub

TestRange is any named range, defined in workbook.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Charles" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello
>
> I would like to do the following, but I am not sure it is doable in
> VBA. I saw it in Php, so I have a little hope it is.
>
> I have a spreadsheet full of range names. They are all of the form
> "S1.Name3.Subname2". Basically I would like to load in VBA all the
> values from these rangenames in one macro.
>
> For the moment I do it manually be doing
>
> dim S1_Name3_Subname2
> S1_Name3_Subname2=Thisworkbook.Names("S1.Name3.Subname2").RefersToRange.Value2
>
> and I do the same for all the range names. I have found on a post on
> this group a lovely program called NameManager that allows to extract
> all range names from a workbook into a spreadsheet. Creating with an
> excel formula the code above is the easy with a few excel formula and a
> copy paste.
>
> Is there a way to go through all the range names of a spreadsheet
> (relatively easy) and to create VBA array names based on these names
> (that's the piece I can't do)?
>
> thanks
> Charles
>

Charles,

What exactly are you trying to do? Do you want to create an array for each
name, and then to load that array with the contents of the name? If so, what
if the name is not a range, but say a formula, or a constant?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Charles" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello
>
> I would like to do the following, but I am not sure it is doable in
> VBA. I saw it in Php, so I have a little hope it is.
>
> I have a spreadsheet full of range names. They are all of the form
> "S1.Name3.Subname2". Basically I would like to load in VBA all the
> values from these rangenames in one macro.
>
> For the moment I do it manually be doing
>
> dim S1_Name3_Subname2
> S1_Name3_Subname2=Thisworkbook.Names("S1.Name3.Subname2").RefersToRange.Value2
>
> and I do the same for all the range names. I have found on a post on
> this group a lovely program called NameManager that allows to extract
> all range names from a workbook into a spreadsheet. Creating with an
> excel formula the code above is the easy with a few excel formula and a
> copy paste.
>
> Is there a way to go through all the range names of a spreadsheet
> (relatively easy) and to create VBA array names based on these names
> (that's the piece I can't do)?
>
> thanks
> Charles
>

the thing is that the user can add some range names in the spreadsheet
and I don't want to update manually the macro grabbing all the values
each time. And for convenience of use, I am trying to use the same kind
of names in VBA arrays and XL range names (S1_Name3_Subname2 and
S1.Name3.Subname2). So basically I am trying to find a way to name a
new VBA array/variable with a name which itself is a variable

In php if the arrayname is stored in the variable $name, you can just
do $$name=....

and if $name="MyArray1", then you can directly use $MyArray1 in the
code.

not easy to describe clearly...
Charles

Bob Phillips wrote:
> Charles,
>
> What exactly are you trying to do? Do you want to create an array for each
> name, and then to load that array with the contents of the name? If so, what
> if the name is not a range, but say a formula, or a constant?
>
> --
> ---
> HTH
>
> Bob