|
chrzastek
Guest
|
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: 2875
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:
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.names
https://docs.microsoft.com/en-us/office/vba/api/excel.names
https://docs.microsoft.com/en-us/office/vba/api/excel.name |
|
Posted 06 Mar, 2019 04:10:24
|
|
Top
|
|
chrzastek
Guest
|
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: 2875
Joined: 2004-04-05
|
|
Posted 06 Mar, 2019 08:07:43
|
|
Top
|
|
chrzastek
Guest
|
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: 2875
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
Guest
|
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: 2875
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
|
|