Declaration of an array in Excel VBA

Question

I have in a Excel field (created by someone) a formula containing an Array:
=PERCENTILE(Scale1, 0.5).
I need to expand this array to other fields.

Usually an Array is a range like A1:C52, so expanding it means modifying the formula to, let's say A1:C152.

But this "Scale1", even if I can see its border in a blue color, I can't modify its range.

I tried to search where this "Scale1" is defined, but without success. Does somebody know how to modify it?

(I can't just replace "Scale1" with A1:C152, because "Scale1" is used in multiple places...)

Answer 1

This sounds like somone has used names. You can modify them interactively in the UI:

• Go to the Formulas tab/ribbon
• Click on Name Manager
• Select Scale1
• Change the range in the text field at the bottom
• Click the Close button

(In Excel 2003, a similar dialog can be opened with "Name > Define..." (in the Insert menu).

or via VBA:

ThisWorkbook.Names("Scale1").RefersTo = "Table1!$A$1:$C$152"

Answer 2

If you are using Excel 2007/2010, you can open the xlsx document's XML contents. Look for 'Scale1' this way. To do this in code, use the OpenXML APIs.

Retrieving Excel cell values: http://msdn.microsoft.com/en-us/library/bb332058%28office.12%29.aspx

Similar question on recalculating formulas: http://stackoverflow.com/questions/2668643/openxml-sdk-make-excel-recalculate-formula

Finally, if you need to do this in VBA, I found this article on editing elements, but have not tried this: http://www.jkp-ads.com/articles/Excel2007FileFormat02.asp

For Excel 2003, you can install the Compatibility Pack which will allow Office 2003 to open, edit, save, and create files using the Open XML Formats new to the 2007 Microsoft Office system.

Answer 3

In Excel 2003, the names manager is at Insert|Name|Define.