CODEWITHSUNDEEP
c#excelexcel-dna
Terry
Terry

Reputation: 1992

Excel C API (via ExcelDNA) and Named Range Scopes

If I have workbook with ranges:

Inputs = Sheet1!A1:A10, Scope: Workbook
Sheet1!Inputs = Sheet1!B1:B10, Scope: Worksheet/Sheet1

If I activate Sheet1, the following results occur:

XlCall.Excel( XlCall.xlfGetName, $"!Inputs" ) => =R1C2:R10C2.
XlCall.Excel( XlCall.xlfGetName, $"!Inputs", 2 )=> true indicating that it is scoped to the worksheet

If I activate a different sheet and make the same calls, I get the following:

XlCall.Excel( XlCall.xlfGetName, $"!Inputs" ) => =Sheet1!R1C1:R10C1.
XlCall.Excel( XlCall.xlfGetName, $"!Inputs", 2 )=> false indicating that it is scoped to the workbook.

If I delete my worksheet scoped Name and repeat the process. Calling when Sheet1 is active yields:

XlCall.Excel( XlCall.xlfGetName, $"!Inputs" ) => =R1C1:R10C1. Note C1
XlCall.Excel( XlCall.xlfGetName, $"!Inputs", 2 )=> false indicating that it is scoped to the workbook.

If I activate a different sheet and make the same calls, I get the following:

XlCall.Excel( XlCall.xlfGetName, $"!Inputs" ) => =Sheet1!R1C1:R10C1.
XlCall.Excel( XlCall.xlfGetName, $"!Inputs", 2 )=> false indicating that it is scoped to the workbook.

Problem: I don't see an easy way to get a Workbook vs Worksheet scoped Name via C API. The best way I can think to do it is to call xlfGetName with the info_type parameter and ensure it is returning the proper value based on requested scope.

If they get the incorrectly scoped Name, then change the active sheet (based on requested scope) appropriately and try again.

Is that the best method? Is there something I'm missing from the C API documentation I'm reading?

Upvotes: 0

Views: 41

Answers (0)

Related Questions