Thread-safe function that relies on a non-thread safe function

Jul 8, 2010 at 11:58 AM
Edited Jul 8, 2010 at 12:02 PM

I'm trying to write a function that should, the first time that it's called, load and pre-process some data from a file so that subsequent calls to the function are much faster.

In pseudo code:

public static string MainFunction {

if (FirstTimeCalled)
   LoadAndProcessDataInALengthyProcess();

return GetVeryQuickResultFromPreLoadedData();

}

 

The problem is that LoadAndProcessDataInALengthyProcess needs to get the full path of the calling file in order to load the correct data file, and to do this the code must be declared with IsMacroType=true. This means that I have to declare MainFunction with IsMacroType=true, which means it's not considered thread-safe. I'd really like to have MainFunction be thread-safe in order to improve the performance. Is there any way around this?

LoadAndProcessDataInALengthyProcess calls the following method that only works if declared IsMacroType=true.

 [ExcelFunction(IsMacroType=false)]
    private static string GetCallingFileName()
    {
        try
        {
            
            var reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
            var sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference);

            var path = (string)XlCall.Excel(XlCall.xlfGetDocument, 2, sheetName);
            var filename = (string)XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName);
            return Path.Combine(path, filename);
        }

        catch (Exception ex)
        {
            return string.Empty;
        }
    }

 Cheers

Chris.

[Edited to fix the formatting]

Coordinator
Aug 4, 2010 at 9:02 PM
Nice question.
Coordinator
Aug 5, 2010 at 10:37 AM

Hi Chris,

If your GetVeryQuick function really is, you should be updating tens of thousands of cells a second. You want to get the stuff into huge sheets even faster using multithreaded recalc!?

Fine. Let's try:

Is the SheetId that you get in the ExcelReference from xlfCaller unique for the Excel session across all the open workbooks? I think so (it might just be a pointer).

If so, you might be able to build the [SheetId] -> [SheetName and Workbook path] mapping separately, and then just access the stored map from your function. To build the map, you'll need to find the right events to hook, maybe something like Workbook_NewSheet and Application_NewWorkbook etc. These can be hooked up in you AddIn's AutoOpen implementation. At every interesting event, you call a macro that updates the SheetId ->... info. Your fast function then just looks it up when needed.

I don't know if it will work though....

 

Regards,
Govert