current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

Excel allegedly allows the storage of arrays of constants in individual cells (e.g. A1={1,2,3,4,5}). This is referred to as an "Array Constant." This should make for a very powerful feature allowing users to get more than 2 dimensions out of a spreadsheet. However, I can't seem to figure out how to extract the actual data without having to parse it as a string first. The latter method defeats the whole purpose of the array constant.

If one gets Range('A1').Value from the example above, it returns 1.

Also, IsArray(Range("A1")) returns False while IsArray(Range("A1:A2")) returns True.

Am I missing something, or are these "Array Constants", not actually "Arrays" but just Strings or Doubles?

share|improve this question
1  
AFAIK there's no way to use an array stored in one cell, even the worksheet functions can't do that. You have to store the array in as many cells as there are elements. –  GSerg Dec 13 '13 at 10:27
    
You can get the array by using Range("A1").FormulaArray. Good thing is that if the Range doesn't contain Array then it will return Null –  Pankaj Jaju Dec 13 '13 at 10:36

1 Answer 1

up vote 0 down vote

Am I missing something...

I'm afraid you may be... ;-(

Here's the relevant text from the article

To create a horizontal constant

  1. Use the workbook from the previous column, or start a new workbook.

  2. Select cells A1 through E1.

  3. In the formula bar, enter the following formula and press CTRL+SHIFT+ENTER:

So the article doesn't show an array being stored in a single cell, but a range of cells. Entering the formula as described in a single cell will return the first element in the array only. Checking the Value property for the range using VBA confirms this.

share|improve this answer
    
Yes, but it also says "You create array constants by entering a list of items and then manually (yes, manually) surrounding the list with braces ({ })" –  TimY Dec 13 '13 at 10:43
    
@Tim_Y Yes, but those can only be used inline as a part of a formula, not stored on their own to be referenced later. –  GSerg Dec 13 '13 at 11:27
    
That's what I think as well now. I think what page is somewhat misleading. –  TimY Dec 13 '13 at 11:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.