Distinguish start up of functions and user input

Nov 3, 2014 at 10:47 AM
Edited Nov 3, 2014 at 10:49 AM
Hi.

I need a way to figure out if the UDFs are being called on start up or if the user calls them.

What I desire is when the user opens an excel with an UDF in it, the user will get asked if he wants to fetch the newest data, if the user answers this question with NO all function will return the value from the cell they are in with this:
var eR = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
return eR.GetValue();
After the excel starts the user may go to one of the cells where an UDF is inside and tries to evaluate the formula now the UDF should go and get new data.

Its this even possible and if so how can I do it?

For now my function looks like this:
        private static bool _fetchNewData = false;
        [ExcelFunction(IsMacroType = true)]
        public static object TestFunction()
        {
            //Only ask this question on start up of Excel and suppress all UDFs if answered with NO
            //if user than tries to evaluate the UDF fetch new data
            var result = MessageBox.Show("Fetch new data?", "Fetch new data?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
            _fetchNewData = result == DialogResult.Yes;

            if (_fetchNewData)
            {
                //get new data...
                return new object();
            }
            else
            {
                var eR = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
                return eR.GetValue();
            }
        }
Coordinator
Nov 4, 2014 at 10:40 AM
Are you unsure of when to ask the question?

I guess you have some options:
  • You can add an AutoOpen implementation in your add-in - this will run when the add-in is loaded (either because Excel has started and is loading the add-in, or because the user has File->Opened your add-in or added it to the add-ins list. None of your UDF functions will run before the AutoOpen.
  • You can register event handlers to detect when a new workbook has been opened.
-Govert
Nov 4, 2014 at 11:36 AM
I am unsure about the perfect timing of the question to disable the UDFs and enable the UDFs again.

What I have done so far is the following:

public class ExcelAddIn : IExcelAddIn
{
    ExcelComAddIn _comAddIn;

    public void AutoOpen()
    {
        try
        {
            _comAddIn = new MyComAddIn();
            ExcelComAddInHelper.LoadComAddIn(_comAddIn);
        }
        catch (Exception e)
        {
            MessageBox.Show("Error loading COM AddIn: " + e.ToString());
        }
    }

    public void AutoClose()
    {

    }
}

[ComVisible(true)]
public class MyComAddIn : ExcelComAddIn
{
    public MyComAddIn()
    {
    }
    public override void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
    {
        //check if there is any UDF defined...
        var result = MessageBox.Show("Fetch Data?", "Fetch Data?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
        ExcelFunctions.FetchData = result == DialogResult.No ? FetchData.Dont : FetchData.Automatically;
    }
    public override void OnDisconnection(ext_DisconnectMode removeMode, ref Array custom)
    {
        
    }
    public override void OnAddInsUpdate(ref Array custom)
    {   
    }
    public override void OnStartupComplete(ref Array custom)
    {
        if (ExcelFunctions.FetchData == FetchData.Dont)
            ExcelFunctions.FetchData = FetchData.Manual;
    }
    public override void OnBeginShutdown(ref Array custom)
    {
    }
}
The enum inside the my "ExcelFunctions" should work as the following:
public enum FetchData
{
    Dont = 0, //do not fetch data, (this should only be active if the user answered the start up question with no and only untill excel started up and did it things)
    Manual = 1, //if the user answered the start up question with no - FetchData should change to manual after the start up routine is over (this will also block RTD updates)
    Automatically = 2, // this will be the case when user says YES to start up question (RTD updates will update values)
}
With this setup I am facing the problem that the enum FetchData is getting set to early back to .Manual at a time where excel didn't call all existing UDFs in the opened workbook, worksheets, and this results in a undesired result that the UDFs fetch data without the user explicitly calling them.
Coordinator
Nov 4, 2014 at 5:47 PM
You should not assume that a workbook recalculates when it is opened.

You might consider using an Application.AfterCalculate event (for Excel 2010+) or SheetCalculate events for knowing when a calculation is complete.
Nov 5, 2014 at 6:21 PM
Edited Nov 6, 2014 at 11:56 AM
You are saying its not always the case that when a Workbook opens that it recalculates, so how could I tell the difference if it does calculate at start or not.

Assuming I register an event for Application.AfterCalculate and the workbook doesn't recalculate at start the event will fire some time in the future where I don't want to do anything anymore.

Any idea how I can block the recalculation on workbook open, with a question for the user if he wants to do so or not?
Nov 17, 2014 at 9:59 AM
Any chances you have a different idea up your sleeve?
Nov 27, 2014 at 1:03 PM
Edited Nov 27, 2014 at 1:04 PM
After some further testing I noticed that only those UDFs that wrap a RTD function are getting called.
So maybe is there a way to tell RTD to stop trying to connect to data on workbook open?