Read Excel named range to Array

**dizabled** · 08-30-2011, 10:52 PM

This one is probably easy for you guys
I have a named array of cell in Excel
Ex. cells A1 to A10 are called "Description"
I am trying to read this range into an array in VBA

This way, if one line is added to Description, the code would read the new entry.

Can anyone help ?
thanks

**protonLeah** · 08-30-2011, 11:09 PM

Please Login or Register  to view this content.

**MarvinP** · 08-30-2011, 11:10 PM

Hi dizabled and welcome to the forum.

This sounds like a job for Dynamic Named Ranges. If you add a line to your array the DYNAMIC range gets bigger.

See http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.exceluser.com/explore/dynname1.htm

**MarvinP** · 08-30-2011, 11:19 PM

Hi,

Why did you use Transpose when

Please Login or Register  to view this content.

seems to work also?

**protonLeah** · 08-31-2011, 12:45 AM

That's the only way I know to reduce from a 2D to 1D vector/array

**MarvinP** · 08-31-2011, 09:34 AM

I tried the Transpose in your code on a 4x2 array and it became a 2x4 array. Still a 2 dimensional array.

I'm not good with getting stuff into arrays so I was excited to see your code and mine below it really put cells into a VBA array. Thanks for the lesson. Now this 2d to 1d confused me.

**dizabled** · 08-31-2011, 09:13 PM

Thanks for helping me out.

What's with "application", what does this refer to ?
I was trying to work around using
workbook(Mybook).sheet(Mysheet).range(myrangename)

**Mordred** · 08-31-2011, 09:26 PM

Hi dizabled, please use code tags around code as per rule number 3 of the forum rules (located in my signature.

**MarvinP** · 08-31-2011, 09:30 PM

Hi,
Application as the start means in the program you are using. Look at Application Object in
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Here is a hint. When in the immediate window and I want to see what words are understood, start with the word "Application" and then a period. A list of all possible next words will appear. This really helps me see what can be put after the period in objects.

**protonLeah** · 08-31-2011, 10:09 PM

Some, though not all, Excel worksheet functions can be accessed by VBA by means of statements such as:
y=Appliction.Transpose(...
which is the same as:
z=WorksheetFunction.Transpose(....