xlfTextref

  • Thread starter Thread starter Jerry W. Lewis
  • Start date Start date
J

Jerry W. Lewis

Am I missing something, or does xlfTextref from the C API not support
named ranges?

If I have a (possibly disjoint) range of cells named myRange, then the
Excel 4 macro command
=TEXTREF("myRange")
returns the range named by myRange, but the C command
Excel4(xlfTextref, &xArgRef, 1, TempStr(" myRange"));
returns xlretFailed.

Jerry
 
Jerry W. Lewis said:
Am I missing something, or does xlfTextref from the C API not support
named ranges?
Click to expand...

You are missing xlfGetName. TEXTREF is for converting A1 or R1C1-style
strings into ref OPER's.
 
If myRange is a named range refering to the union of B1:B3 and D1:D6,
then the Excel 4 macro statement
=TEXTREF("myRange")
returns "[xl4.xls]Sheet1!R1C4:R6C4,[xl4.xls]Sheet1!R1C2:R3C2" and
=GET.NAME("myRange")
returns "=[xl4.xls]Sheet1!R1C4:R6C4,[xl4.xls]Sheet1!R1C2:R3C2"

Neither syntax seems to work in the C API:
Excel4(xlfTextref, &xArgRef, 1, TempStr(" myRange"));
returns xlretFailed, and
Excel4(xlfGetName, &xArgRef, 1, TempStr(" myRange"));
returns xlretSuccess, but xArgRef contains a #NAME? error.

Still wondering if I am missing something or if the C API does not
support going determining the range defined to a name.

Jerry
 
Excel4(xlfGetName, &xArgRef, 1, TempStr(" myRange"));
returns xlretSuccess, but xArgRef contains a #NAME? error.

Still wondering if I am missing something or if the C API does not
support going determining the range defined to a name.
Click to expand...

"""
Syntax

GET.NAME(name_text, info_type)
Name_text can be a name defined on the macro sheet; an external reference
to a name defined on the active workbook, for example, "!Sales"; or an
external reference to a name defined on a particular open workbook, for
example, "[Book1]SHEET1!Sales". Name_text can also be a hidden name.
"""

Defining myRange on the worksheet and calling the following worksheet
function with "!myRange" returns the range in R1C1 notation (its actually
Z1S1 in a German Excel).

[WorksheetFunction(DeferRecalculation=true)]
public static XlOper GetName(String name)
{
return Excel.Call(Excel.Functions.xlfGetName, name);
}


Jens.
 
Thank you, that works; but is apparently yet another example where the C
API Excel4() function works differently than the corresponding Excel 4
macro function. Are these differences documented anywhere, or must they
be discovered by trial and error?

In Excel 4, info_type is an optional argument, whose input values (as
implemented in Excel 2000 and Excel XP) are wrong in MACROFUN.HLP. The
C API function Excel4(xlfGetName) uses info_type as documented in
MACROFUN.HLP, rather than as implemented in Excel 4.

In Excel 4, GET.NAME accepts either "myRange" or "!myRange", and both
usages are sanctioned in the Examples from MACROFUN.HLP. The difference
is that the return with "!myRange" omits "[xl4.xls]Sheet1!". As you
pointed out, Excel4(xlfGetName) only accepts TempStr(" !myRange") and
not TempStr(" myRange")

Jerry

Jens said:
Excel4(xlfGetName, &xArgRef, 1, TempStr(" myRange"));
returns xlretSuccess, but xArgRef contains a #NAME? error.

Still wondering if I am missing something or if the C API does not
support going determining the range defined to a name.
Click to expand...

"""
Syntax

GET.NAME(name_text, info_type)
Name_text can be a name defined on the macro sheet; an external reference
to a name defined on the active workbook, for example, "!Sales"; or an
external reference to a name defined on a particular open workbook, for
example, "[Book1]SHEET1!Sales". Name_text can also be a hidden name.
"""

Defining myRange on the worksheet and calling the following worksheet
function with "!myRange" returns the range in R1C1 notation (its actually
Z1S1 in a German Excel).

[WorksheetFunction(DeferRecalculation=true)]
public static XlOper GetName(String name)
{
return Excel.Call(Excel.Functions.xlfGetName, name);
}


Jens.
Click to expand...
 
Jerry W. Lewis said:
Thank you, that works; but is apparently yet another example where the C
API Excel4() function works differently than the corresponding Excel 4
macro function. Are these differences documented anywhere, or must they
be discovered by trial and error?
Click to expand...

Welcome to the club. Microsoft documenation is just a rough guide, you need
to have a chat with your debugger to find out how things really work. Or if
you are feeling lazy, you can post your question to
microsoft.public.excel.sdk. :-)
 
Back
Top