Posts 1 - 10 of 12
First | Prev. | 1 2 | Next | Last
|
|
|
|
ewessely
Posts: 55
Joined: 2019-01-31
|
Hi everyone!
I'm having troubles with getting a reference in A1style.
Situation:
I've a long running UDF and during the execution some cells have to be updated. I've to take care that the name of the sheet from where the UDF has started could be renamed or the workbook could be stored under a different name during the running of the UDF. So - storing the caller in A1style is not really an option.
My (incomplete) solution (simplified for easier understanding):
In the UDF I get the caller (ADXExcelRef object) and store this object together with a hash in a global dictionary.
Next I start a task (with the hash and some other parameters) doing the long runing work.
To allow update some cells (next to the entered formula) to inform the user about the progress and status, the task sends messages via the SendMessage.
The Addin Module reacts on these messages in AddinModule_onSendMessage. One of the parameters is the hash.
Via the hash, we get the ADXExcelRef object of the originally calling cell.
Now I try to get an A1style of the ADXExcelRef object to get a range from where I can manipulate cells for feedback (and finally present the results of the long running task)
Problem 1:
Trying to use ConvertToA1Style
*) if sheet is not renamed and still exists
throws error
Unable to cast object of type 'AddinExpress.MSO.ADXXlReturnValue' to type 'System.String'.
Source: AddinExpress.MSO.2005
*) if the sheet as been deleted in the meantime I get an error (which can not been handled via try/catch)
Exception Source: AddinExpress.MSO.2005
Exception Type: System.AccessViolationException
Exception Message: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Exception Target Site: TryExcel12
---- Stack Trace ----
AddinExpress.MSO.ExcelAPI.TryExcel12(xlFunction As Int32, result As Object&, freeOperationMemory As Boolean, parameters As Object[])
AddinExpress.MSO.2005.dll: N 0042 (0x2A) IL
AddinExpress.MSO.ExcelAPI.Excel(xlFunction As Int32, freeOperationMemory As Boolean, parameters As Object[])
AddinExpress.MSO.2005.dll: N 0023 (0x17) IL
AddinExpress.MSO.ADXExcelRef.ConvertToA1Style()
AddinExpress.MSO.2005.dll: N 0000 (0x0) IL
Problem 2
To work around problem 1 I wrote a helper to build my A1Style by my own like this
internal static string ExcelRefToA1Style(ADXExcelRef aDXExcelRef)
{
try
{
object sheetName = XLLModule.CurrentInstance.CallWorksheetFunction(ADXExcelWorksheetFunction.SheetNm, aDXExcelRef);
string formulaR1C1 = string.Format("={0}!R{1}C{2}", sheetName, aDXExcelRef.RowFirst + 1, aDXExcelRef.ColumnFirst + 1);
string formulaA1 = AddinModule.CurrentInstance.ExcelApp.ConvertFormula(formulaR1C1, XlReferenceStyle.xlR1C1, XlReferenceStyle.xlA1).ToString().Replace("=", "");
return formulaA1;
}
catch (Exception)
{
return "";
}
}
This works fine, as long as the sheet still exist - renaming of the sheet is handle correctly.
But if the sheet has been deleted (the sheetId of the aDXExcelRef is no longer valid) I get the same System.AccessViolationException exception as above.
Do you have any ideas how to solve?
br
Erichew |
|
|
Posted 15 Nov, 2020 16:23:42
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18823
Joined: 2006-05-11
|
Hello Erich,
ewessely writes:
In the UDF I get the caller (ADXExcelRef object) and store this object
I suppose you can call ADXExcelRef.ConvertToA1Style() right at this moment and store the result.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 16 Nov, 2020 02:52:02
|
|
Top
|
|
|
ewessely
Posts: 55
Joined: 2019-01-31
|
Hello Andrei,
thanks for the soon replay.
Unfortunately the workbook and/or sheet can bee renamed/deleted during calling the udf and deliver results because of the long running.
The big questions are:
Why can ADXExcelRef.ConvertToA1Style() be called only in the udf and throws an error calling it elsewhere.
or as workaround
Is there a reliable way to find out whether a worksheet exists based on a given sheetId (the only thing we no for sure - names can change).
best regards
Erichew |
|
|
Posted 16 Nov, 2020 03:33:10
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18823
Joined: 2006-05-11
|
Hello Erich,
ADXExcelRef.ConvertToA1Style() can only be called in the context of an XLL call.
ewessely writes:
Is there a reliable way
I don't know if there's an answer to this question.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 16 Nov, 2020 07:26:02
|
|
Top
|
|
|
ewessely
Posts: 55
Joined: 2019-01-31
|
Hello Andrei,
how can I "switch" into the context of XLL.
Just for test I've tried the approach to call from the AddinModule
XLLModule.CurrentInstance.CallWorksheetFunction(ADXExcelWorksheetFunction.UDF, new object[] { "XLLtest", "input"})
For a first test this is only a simple function to test how to call the xll function
public static string XLLtest(string input)
{
return input;
}
But this also gives an error:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at AddinExpress.MSO.ExcelAPI.TryExcel12(Int32 xlFunction, Object& result, Boolean freeOperationMemory, Object[] parameters)
at AddinExpress.MSO.ExcelAPI.Excel(Int32 xlFunction, Boolean freeOperationMemory, Object[] parameters)
at AddinExpress.MSO.ADXXLLModule.CallWorksheetFunction(ADXExcelWorksheetFunction function, Object[] parameters)
Any idea?
best regards
Erichew |
|
|
Posted 16 Nov, 2020 08:20:47
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18823
Joined: 2006-05-11
|
Hello Erich,
You can't switch to that context. Excel switches your code to that context when it calls your UDF.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 16 Nov, 2020 08:40:41
|
|
Top
|
|
|
ewessely
Posts: 55
Joined: 2019-01-31
|
Hello Andrei,
You can't switch to that context. Excel switches your code to that context when it calls your UDF.
I assumed that. It's the idea behind the CallWorksheetFunction(ADXExcelWorksheetFunction.UDF... that Excel will do the switching.
But why does this simple call produce an protected memory error instad of giving me back the string.
The existance of ADXExcelWorksheetFunction.UDF let me assume that it should be possible to call XLL UDF from AddinModule. What have I overlooked here?
best ragards
Erichew |
|
|
Posted 16 Nov, 2020 08:51:23
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18823
Joined: 2006-05-11
|
|
|
Posted 16 Nov, 2020 09:22:59
|
|
Top
|
|
|
ewessely
Posts: 55
Joined: 2019-01-31
|
Hello Andrei,
I gave this also a try.
Unfortunately calling ConvertToA1Style() for an ADXExcelRef where the sheet has been deleted does not throw a catchable error.
Instead it also throws the "Attempted to read or write protected memory....".
Shouldn't ADX throw an reasonable error (which we could handle)?
best regads for your prompt replies and suggestions
Erichew |
|
|
Posted 16 Nov, 2020 10:51:42
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18823
Joined: 2006-05-11
|
Hello Erich,
I suggest that you get the caller cell address right after you get the ADXExcelRef representing the caller cell. Also, your add-in module can trace deletions.
That is, when your UDF is called, you get the caller, get the caller's address, inform the add-in module that the UDF starts (I suppose you can do this via a direct call, not via SendMessage), and start performing your calculations. Informing the add-in module would mean adding the cell address to a collection/ The add-in module can check that collection whenever a deletion occurs.
Sorry, I don't understand what you want to do next.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 17 Nov, 2020 01:28:14
|
|
Top
|
|
|
Posts 1 - 10 of 12
First | Prev. | 1 2 | Next | Last
|
Forums
Topics
Search
Help
Login
Register