Creating dynamically arrays, is that possible in VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charles - 13 Dec 2006 10:57 GMT

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

Reply to this Message

Arvi Laanemets - 13 Dec 2006 11:11 GMT

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.

Signature

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

> Hello
>
[quoted text clipped - 22 lines]
> thanks
> Charles

Reply to this Message

Bob Phillips - 13 Dec 2006 11:19 GMT

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?

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hello
>
[quoted text clipped - 22 lines]
> thanks
> Charles

Reply to this Message

Charles - 13 Dec 2006 11:37 GMT

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

> 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?

Reply to this Message

Rate this thread: