CODEWITHSUNDEEP
vbaexcelexcel-2003
serhio
serhio

Reputation: 28586

Declaration of an array in Excel VBA

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...)

Upvotes: 0

Views: 905

Answers (3)

Marc Thibault
Marc Thibault

Reputation: 1728

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

Upvotes: 0

Edward Leno
Edward Leno

Reputation: 6327

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: 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.

Upvotes: 0

Codo
Codo

Reputation: 78835

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"

Upvotes: 2

Related Questions