3 dimensional array

[esbencito]

Hi all,

I am trying to create a 3D array with VBA and then store the data in a "named range" to use it in one of my formulas. However, I don't seem to get it working. I have three named ranges on my worksheet, so each of them should represent an "area" and each of these areas contain 8 rows times 4 columns.

Below the code I have so far:

Please Login or Register  to view this content.

Once the array has been added to the workbook as a "named range" called "Cluster", I want to access the data by using an INDEX formula as below, where 1 = row 1, 2 = column 2, and 3 = area 3. Hence the result should be 52 as it refers to the second column, first row in range "KNITS".

Please Login or Register  to view this content.

[6StringJazzer]

A name must refer to a formula that returns a worksheet range. You are trying to create a name to refer to a VBA array.

If you first write your array Clus to a set of cells (in the way your data in arranged in your example), then name that set of cells Cluster, your INDEX formula will work. But I don't think you need to do that because you are reading the Clus data from the worksheet to begin with.

Here is your example. I have added the named range Cluster, and now your INDEX formula works. I didn't touch your code and I am not sure you even need the code. The most your code would need to do is analyze the worksheet data to create the named range.

[rorya]

A name must refer to a formula that returns a worksheet range. You are trying to create a name to refer to a VBA array.

That's not correct. You can certainly assign a 1- or 2-D VBA array to a name (or indeed just a number, text, or other formula). What you can't do is assign an array with more than 2 dimensions, since Excel has no way of representing that in a formula.

If code is required to create one named range, then you might do something like this:

Please Login or Register  to view this content.

[esbencito]

Thanks Rory!!

This isn't what I wanted to hear but helping a lot in finding the right way to use arrays/ named formulas. I have actually managed to store the data in a 2D array rather than 3D. Though, it's quite a lot of data for a named formula.

I get to about 3000 data points in total in my actual data set. This is way beyond the expected 255 character limit, however, for some reason it still works?? What is the limit for named formulas? I tried 5000 data points and my Excel crashed when saving the file...

[rorya]

I would recommend you don't store anything longer than 255 characters in a name. Why do you need to store the data as an array, not a range?

[esbencito]

The data is coming from a different workbook, I use it in a formula to test for certain conditions, so I just wanted to store a "few" numbers without having to paste them anywhere in a sheet first...

[rorya]

Personally, I would suggest you do store it in a range. It will make auditing your results a lot easier.

[esbencito]

you mean storing in a sheet first then analyzing the data from there?

[rorya]

Yes. It'll be much easier to see where the results come from that way.

[esbencito]

True... won’t have much of a choice anyway I guess! Thanks again both for helping out!!