How to get Excel's defined name of a cell instead of A1 address

Add-in Express™ Support Service
That's what is more important than anything else

How to get Excel's defined name of a cell instead of A1 address
The list of defined names are in "Name Manager" in Formulas ribbon tab 
chrzastek




Posts: 7
Joined: 2019-03-05
Hi Add-In Express Support Team and anyone interested,

In Excel I can define a name e.g. "input" to a cell and use this name instead of "A1". I am trying to get this name. I know how to get A1 name, but not defined named. The list of those defined names can be seen in "Name Manager" in Formulas ribbon tab. I presume that the key may be xlA1 parameter.

procedure TAddInModule.adxExcelAppEventsSheetChange(ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
begin
ShowMessage(Target.Cells.Item[1, EmptyParam].Address[false, true, xlA1, false, EmptyParam] + ': ' + String(Target.Text));
end;

Please advise. I am using Rio.

Regards,
Arkadiusz
Posted 05 Mar, 2019 10:03:53 Top
Dmitry Kostochko


Add-in Express team


Posts: 2803
Joined: 2004-04-05
Hello Arkadiusz,

To get a list of named ranges you can use the Names collection and Name object. Please have a look at the code below:

procedure TAddInModule.adxRibbonTab1Controls0Controls0Click(Sender: TObject; const RibbonControl: IRibbonControl);
var
  i: integer;
  book: _Workbook;
  ret: string;
begin
  // get named ranges
  book := ExcelApp.ActiveWorkbook;
  if Assigned(book) then begin
    ret := '';
    for i := 1 to book.Names.Count do
      ret := ret + book.Names.Item(i, EmptyParam, EmptyParam).Name_ + #13#10;
    if (ret <> '') then
      ShowMessage(ret);
  end;
end;

procedure TAddInModule.adxRibbonTab1Controls0Controls1Click(Sender: TObject; const RibbonControl: IRibbonControl);
var
  book: _Workbook;
begin
  // select named range

  // variant #1 (Note! Name1 shall exist)
  book := ExcelApp.ActiveWorkbook;
  if Assigned(book) then begin
    ExcelApp.Range['Name1', EmptyParam].Select();
  end;

  // variant #2 (note! Name1 shall exist)
  book := ExcelApp.ActiveWorkbook;
  if Assigned(book) then begin
    ExcelApp.Goto_('Name1');
  end;
end;


Also, please have a look at the following MSDN articles, hope they will be useful:
Workbbok.Names
Names object
Name object
Posted 06 Mar, 2019 04:10:24 Top
chrzastek




Posts: 7
Joined: 2019-03-05
Thank you Dmitry,

Your explanation is very helpful. I applied it to the code and here is how I discover cell's name:

procedure TAddInModule.adxExcelAppEventsSheetChange(ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
var
  book: _Workbook;
  i : integer;
begin
  book := ExcelApp.ActiveWorkbook;
  if Assigned(book) then begin
    ret := '';
    for i := 1 to book.Names.Count do
      if book.Names.Item(i, EmptyParam, EmptyParam).RefersToRange.Address[false, true, xlA1, false, EmptyParam] =
         Target.Cells.Item[1, EmptyParam].Address[false, true, xlA1, false, EmptyParam] Then
        begin
          ShowMessage(book.Names.Item(i, EmptyParam, EmptyParam).Name_);
        end;
  end;
end;


Is there an easier way to discover cell's name directly from Target to avoid for loop?

Regards,
Arkadiusz
Posted 06 Mar, 2019 05:21:10 Top
Dmitry Kostochko


Add-in Express team


Posts: 2803
Joined: 2004-04-05
Hello Arkadiusz,

Please have a look at the Range.Name property and Name object:
Range.Name property
Name object

Is it what you are looking for?
Posted 06 Mar, 2019 08:07:43 Top
chrzastek




Posts: 7
Joined: 2019-03-05
Hello Dmitry,

Right. This is exactly what I am looking for.
In VBA I used to get name of a range this way:

Function GetRangeName(ByVal Target As Range) As String
    On Error GoTo GetRangeNameErrorHandler
    GetRangeName = Target.Name.Name
    Exit Function
GetRangeNameErrorHandler:
    GetRangeName = ""
End Function


In Delphi I got an error when using Target.Name.Name, but with try... except it works:

procedure TAddInModule.adxExcelAppEventsSheetChange(ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
  try
    ShowMessage(Target.Name.Name);
  except
  end;
end;


Thank you again for your help and directions.

Grateful regards,
Arkadiusz
Posted 06 Mar, 2019 08:30:28 Top
Dmitry Kostochko


Add-in Express team


Posts: 2803
Joined: 2004-04-05
Hello Arkadiusz,

In Delphi I got an error when using Target.Name.Name, but with try... except it works:


I think the exception may be thrown by Excel if you select a range that is not named. Probably you can add condition Target.Name <> nil.
Posted 06 Mar, 2019 09:49:36 Top
chrzastek




Posts: 7
Joined: 2019-03-05
Hello Dmitry,

With

if Target.Name <> nil then ShowMessage(Target.Name.Name);


I get an error "E2008 Incompatible types" (Delphi Rio) and cannot compile DLL. Delphi does not want to compare Target.Name with nil.

With try...except I can compile DLL and do not get exception in Excel.
I would like to be on safe side and check if pointer is nil, but Target.Name is OleVariant and cannot be compared with nil. Please advise if you know solution for this. Anyway thank you for your time.

Regards,
Arkadiusz
Posted 06 Mar, 2019 10:02:52 Top
Dmitry Kostochko


Add-in Express team


Posts: 2803
Joined: 2004-04-05
Hello Arkadiusz,

I have just tried to use VarIsClear() but this did not help - Excel throws an exception, it does not return an error value. So, your approach with try...except is the only solution.
Posted 06 Mar, 2019 10:26:59 Top