c#: programmatically create a "named range" in worksheet scope

Question

How can we create a 'named range' that has its scope set to a worksheet? (as we can do this manually from Excel, I guess there is a way to this in code)

Setting a name using the 'Range.Name' property creates a workbook-scoped named range. I tried prefixing the range name with '<Sheet Name>!' as suggested here, but it doesn't work. The name doesn't get set at all that way.

Any idea as to how this can be done in C#?

Update (2013/05/16): Answer by shahkalpesh worked. The original code (in VS 2010) I used is:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range range = ws.Range[ws.Cells[x,y], ws.Cells[(x + height), (y + width)]];
range.Name = "MyName"; // MyName in workbook scope

The modified code that works is:

ws.Names.Add("MyName", range); // MyName in worksheet scope

Thanks, idssl.

I find recording Macro in Excel, then looking at the code it generates helps you find the code behind to do things in C# or whatever language you're looking for. — Mikey Mouse
– Mikey Mouse, Commented May 15, 2013 at 15:02

shahkalpesh · Accepted Answer · 2019-07-11 09:10:25Z

13

Assumptions:
Sheet1 has cells A1:A4, for which you want to create a named range

VBA:

Sheet1.Names.Add("tada", Sheet1.Range("A1:A4"))

I suppose it will be identical in c# (except the ; at the end) and passing empty arguments for optional parameters.

edited Jul 11, 2019 at 9:10

answered May 15, 2013 at 15:02

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mitja Bezenšek Over a year ago

Yeah it is something like shahkalpesh wrote, expcept he is missing the closing parentheses: worksheet.Names.Add("MyRange", worksheet.Range("A1:A4"));

AndrewWhalan Over a year ago

Another little "gotcha" is the scoping of named ranges. Add it to the workbook object using workbook.Names.Add(...) if you need to access it across other sheets.

Collectives™ on Stack Overflow

c#: programmatically create a "named range" in worksheet scope

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related