Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Creation of arrays Dynamically

  Asked By: Harry    Date: Oct 22    Category: MS Office    Views: 1072
  

I want to create arrays dynamically. Means to say ,
I will create and use the array if the name finds a mention in
other sheet.
Example
Sheet2 has
A B C D E
1 PRCM L4 WKUP TIME DM
2 L3 L1 L6 L0 L8
3 L2 L5 L1

So my VBA code acting on sheet1 needs to have an array with names
PRCM L4 WKUP TIME DM.
I can't define the array beforehand since I will not be knowing the
width of Row1 and also the names are dynamic. Does VBA support dynamic
creation of array ?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Nathan Evans     Answered On: Oct 22

You have to Dim the array  to start with and then ReDim it afterward.
There are some caveats to using Redim though so check the help. If
it's a one dimension array you should have no problems.

The code  should look something like

Dim slArray() as string
Dim ilArray as integer
..
..
ilArray = 20
ReDim Preserve slArray(ilArray)

.. or

ReDim Preserve slArray(Ubound(slArray)+1)

 
Answer #2    Answered By: Laurie Lawrence     Answered On: Oct 22

You can redim multidimensional arrays  as well.....
If you use the keyword redim, all existing data in the
array would be deleted. to avoid this use the keywords
"redim preserve"

Illus:

REDIM TEST(5,5) AS STRING ' would reset dimension but
delete existing contents of TEST

REDIM PRESERVE TEST(5,5) AS STRING ' would not only
reset the dimension but also retain existing contents
of TEST.

 
Answer #3    Answered By: Madaniyah Malik     Answered On: Oct 22

The problem is I don't know how many array  names I have to define
before hand
A B C D E
PR L WK TIME DM Yo
L3 L1 L6 L0 L8
L2 L5 L1

In the above as per my requirement I could have declared arrays
PR L WK TIME DM Yo If the number of names  were constant. But the
number of names can vary beyond or less than Yo, depends on the
application.
Also my requiremnt is a multidimensional array

 
Answer #4    Answered By: Essie Garza     Answered On: Oct 22

I am not very familiar with what you are trying to do, but I would
probably start off my code  with getting a count of the names, and using
that as a variable in the declaration of the array.

Something like:

Dim intName1 as Integer
Dim intName2 as Integer
- get a count of selected cells.
intName1 = ActiveWorkbook.ActiveCells.Count
intName2 = ActiveWorkbook.ActiveRows.Count
REDIM PRESERVE TEST(intName1,intName2) AS STRING

Please know that this is just off the top of my head to give you an idea
of how to figure out how many items are in a list. You are going to
need to figure out how to get the selected Columns and Rows counted to
populate the variables. (Unless Ray can step in and provide a better
answer!

 
Didn't find what you were looking for? Find more on Creation of arrays Dynamically Or get search suggestion and latest updates.




Tagged: