issue with RefersTo in non-EN languages

S

scoober

Sorry for the long post, but in brief:

Setting RefersTo property on a Microsoft.Office.Interop.Excel.NamedRange in
PT-BR yields different/unexpected results when the same property is
subsequently retrieved (as compared to creating the NamedRange using
names.Add()). Using Portuguese/Brazil o/s, if I use names.Add() to create a
named range for "='Plan1'!$B$3:$D$5", when I later retrieve the RefersTo
property, I get =Plan1!$B$3:$D$5, good so far. But, if I *set* the RefersTo
property on the named range to "='Plan1'!$B$3:$D$5", then when I later
retrieve that same property I get =Plan1!L3C2:L5C4 -- this is unexpected.

Yes, I saw this post:
http://msdn.microsoft.com/newsgroup...ming&mid=25b895f2-75f9-4245-a16d-aeee3909775b
but it does not seem to be the same issue.

I have the same issue running German, except that the returned RefersTo is
something like: =Tabelle1!Z3S2:Z5S4

Details:
Development: Win XP SP2 (EN-US), VS 2005 SP1 + VSTO 2005 SE, Excel 2003 SP2
Runtime: Win Server 2003 SP1 (PT-BR), VSTO 2005 SE, Excel 2003 (11.5612.5606)

See code snippet below.
In EN-US, "output" values when the properties are retrieved are consistent
and expected.
In PT-BR (Portuguese/Brazil), there are inconsistencies, particularly in
Test1 and Test2. Take note that in Test1, the RefersTo property after using
names.Add() is =Plan1!$B$3: $D$5 but after using the namedRange.RefersTo
setter, it is =Plan1!L3C2:L5C4.

Empirically, it looks like I can probably change over to using
get_AddressLocal (A1) + RefersToLocal to get a consistent result, but the
behavior in Test1 seems like a bug -- unless I am missing something - which
could definitely be the case.

I would prefer to use get_Address() since that *should* be
locale-independent. Is there some way I can do that and get back predictable
values from namedRange.RefersTo on different locales when using the two
approaches of setting RefersTo?

// In the code snippet below there are 4 tests. Each test creates a named
range using names.Add and dumps out its properties
// It then explicitly sets the RefersToXXX property and dumps out the
properties again.
// Test1 uses get_Address (A1 style) + .RefersTo
// Test2 uses get_Address (R1C1 style) + .RefersToR1C1
// Test3 uses get_AddressLocal (A1 style) + .RefersToLocal
// Test4 uses get_AddressLocal (R1C1 style) + .RefersToR1C1Local

using Excel = Microsoft.Office.Interop.Excel;

// . . .
private void ThisWorkbook_Startup (object sender, System.EventArgs e)
{
Excel.Workbook workbook = this.InnerObject;
Excel.Worksheet worksheet = workbook.ActiveSheet as Excel.Worksheet;
Excel.Range testRange = worksheet.get_Range ("$B$3:$D$5", Type.Missing);
Excel.Names names = workbook.Names;
string refersTo, addr, name, msg;
Excel.Name namedRange;

// TEST 1

name = "test1_A1";
addr = testRange.get_Address (true, true,
Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, Type.Missing);
refersTo = "='" + worksheet.Name + "'!" + addr;

// just add the name
namedRange = names.Add (name, //Name
refersTo, //RefersTo
true, //Visible
Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro,
shortcut, category, NameLocal
Type.Missing, // RefersToLocal
Type.Missing, // CategoryLocal
Type.Missing, // RefersToR1C1
Type.Missing); // RefersToR1C1Local

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Add");

// now get the range via names.item
namedRange = names.Item (name, Type.Missing, Type.Missing);

// and set RefersTo directly
namedRange.RefersTo = refersTo;

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)");

// TEST 2

name = "test2_R1C1";
addr = testRange.get_Address (true, true,
Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1, false, Type.Missing);
refersTo = "='" + worksheet.Name + "'!" + addr;

// just add the name
namedRange = names.Add (name, //Name
Type.Missing, //RefersTo
true, //Visible
Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro,
shortcut, category, NameLocal
Type.Missing, // RefersToLocal
Type.Missing, // CategoryLocal
refersTo, // RefersToR1C1
Type.Missing); // RefersToR1C1Local

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Add");

// now get the range via names.item
namedRange = names.Item (name, Type.Missing, Type.Missing);

// and set RefersTo directly
namedRange.RefersToR1C1 = refersTo;

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)");

// TEST 3

name = "test3_A1Local";
addr = testRange.get_AddressLocal (true, true,
Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, Type.Missing);
refersTo = "='" + worksheet.Name + "'!" + addr;

// just add the name
namedRange = names.Add (name, //Name
Type.Missing, //RefersTo
true, //Visible
Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro,
shortcut, category, NameLocal
refersTo, // RefersToLocal
Type.Missing, // CategoryLocal
Type.Missing, // RefersToR1C1
Type.Missing); // RefersToR1C1Local

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Add");

// now get the range via names.item
namedRange = names.Item (name, Type.Missing, Type.Missing);

// and set RefersTo directly
namedRange.RefersToLocal = refersTo;

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)");

// TEST 4

name = "test4_R1C1Local";
addr = testRange.get_AddressLocal (true, true,
Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1, false, Type.Missing);
refersTo = "='" + worksheet.Name + "'!" + addr;

// just add the name
namedRange = names.Add (name, //Name
Type.Missing, //RefersTo
true, //Visible
Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro,
shortcut, category, NameLocal
Type.Missing, // RefersToLocal
Type.Missing, // CategoryLocal
Type.Missing, // RefersToR1C1
refersTo); // RefersToR1C1Local

msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Add");

// now get the range via names.item
namedRange = names.Item (name, Type.Missing, Type.Missing);

// and set RefersTo directly
namedRange.RefersToR1C1Local = refersTo;
msg = "Input: refersTo: " + refersTo + "\nOutput:" +
"\nRefersTo: " + namedRange.RefersTo.ToString () +
"\nRefersToLocal: " + namedRange.RefersToLocal.ToString () +
"\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () +
"\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString ();
MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)");


Here is the output:

// OUTPUT

// TEST 1

test1_A1 after names.Add
Input: RefersTo: ='Plan1'!$B$3:$D$5
Output:
RefersTo: =Plan1!$B$3:$D$5
RefersToLocal: =Plan1!$B$3:$D$5
RefersToR1C1: =Plan1!R3C2:R5C4
RefersToR1C1Local: =Plan1!L3C2:L5C4

test1_A1 after names.Item + namedRange.RefersTo (set)
Input: RefersTo: ='Plan1'!$B$3:$D$5
Output:
RefersTo: =Plan1!L3C2:L5C4
RefersToLocal: =Plan1!'L3C2':'L5C4'
RefersToR1C1: =Plan1!L3C2:L5C4
RefersToR1C1Local: =Plan1!'L3C2':'L5C4'

// TEST 2

test2_R1C1 after names.Add
Input: RefersTo: ='Plan1'!R3C2:R5C4
Output:
RefersTo: =Plan1!'R3C2':'R5C4'
RefersToLocal: =Plan1!R3C2:R5C4
RefersToR1C1: =Plan1!'R3C2':'R5C4'
RefersToR1C1Local: =Plan1!R3C2:R5C4

test2_R1C1 after names.Item + namedRange.RefersTo (set)
Input: RefersTo: ='Plan1'!R3C2:R5C4
Output:
RefersTo: =Plan1!$B$3:$D$5
RefersToLocal: =Plan1!$B$3:$D$5
RefersToR1C1: =Plan1!R3C2:R5C4
RefersToR1C1Local: =Plan1!L3C2:L5C4

// TEST 3

test3_A1Local after names.Add
Input: RefersTo: ='Plan1'!$B$3:$D$5
Output:
RefersTo: =Plan1!$B$3:$D$5
RefersToLocal: =Plan1!$B$3:$D$5
RefersToR1C1: =Plan1!R3C2:R5C4
RefersToR1C1Local: =Plan1!L3C2:L5C4

test3_A1Local after names.Item + namedRange.RefersTo (set)
Input: RefersTo: ='Plan1'!$B$3:$D$5
Output:
RefersTo: =Plan1!$B$3:$D$5
RefersToLocal: =Plan1!$B$3:$D$5
RefersToR1C1: =Plan1!R3C2:R5C4
RefersToR1C1Local: =Plan1!L3C2:L5C4

// TEST 4

test4_R1C1Local after names.Add
Input: RefersTo: ='Plan1'!L3C2:L5C4
Output:
RefersTo: =Plan1!$B$3:$D$5
RefersToLocal: =Plan1!$B$3:$D$5
RefersToR1C1: =Plan1!R3C2:R5C4
RefersToR1C1Local: =Plan1!L3C2:L5C4

test4_R1C1Local after names.Item + namedRange.RefersTo (set)
Input: RefersTo: ='Plan1'!R3C2:R5C4
Output:
Input: RefersTo: ='Plan1'!L3C2:L5C4
Output:
RefersTo: =Plan1!$B$3:$D$5
RefersToLocal: =Plan1!$B$3:$D$5
RefersToR1C1: =Plan1!R3C2:R5C4
RefersToR1C1Local: =Plan1!L3C2:L5C4
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Storing and retrieving 3-D arrays in/from workbook names 2
urgent help needed to clean up code 1
Filtering data 2
Compare Names in a column and create single list with adjacent vla 6
2 Dim Array Sort, on any columns, ascend or descend. Guidance des 0
Help With Input Box 3
Need assistance with secondary axis in Excel 2007 0
formail + email 3

Top