We define a function that schedules a macro call to update the format of the calling range.
    public static DateTime asyncFormatCaller()
    {
        object caller = XlCall.Excel(XlCall.xlfCaller);
        if (caller is ExcelReference)
        {
            ExcelAsyncUtil.QueueAsMacro(
                delegate
                {
                      // Set the desired selection, then apply formatting
                      using (new ExcelSelectionHelper((ExcelReference)caller))
                      {
                          XlCall.Excel(XlCall.xlcFormatNumber, "h:mm:ss");
                      }
                }
            });
        }
        return DateTime.Now;
    }

Above we use the following helper class to keep track of the current selection in a macro, so that the selection is correctly restored after the macro has completed.
    // Helper class to deal with Excel selections in 'using' style
    public class ExcelSelectionHelper : XlCall, IDisposable
    {
        object oldScreenUpdating;
        object oldSelectionOnActiveSheet;
        object oldActiveCellOnActiveSheet;

        object oldSelectionOnRefSheet;
        object oldActiveCellOnRefSheet;

        public ExcelSelectionHelper(ExcelReference refToSelect)
        {
            oldScreenUpdating = Excel(xlfGetWorkspace, 40);
            Excel(xlcEcho, false);

            // Remember old selection state on the active sheet
            oldSelectionOnActiveSheet = Excel(xlfSelection);
            oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

            // Switch to the sheet we want to select
            string refSheet = (string)Excel(xlSheetNm, refToSelect);
            Excel(xlcWorkbookSelect, new object[] { refSheet });

            // record selection and active cell on the sheet we want to select
            oldSelectionOnRefSheet = Excel(xlfSelection);
            oldActiveCellOnRefSheet = Excel(xlfActiveCell);

            // make the selection
            Excel(xlcFormulaGoto, refToSelect);
        }

        public void Dispose()
        {
            Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

            string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);
            Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

            Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);

            Excel(xlcEcho, oldScreenUpdating);
        }
    }

An improved function could first check whether the format of the caller needs to be updated before scheduling the macro call.

Last edited Dec 9, 2012 at 9:48 PM by govert, version 5

Comments

govert Sep 8, 2014 at 8:52 PM 
Since version 0.32, the call to ExcelAsyncUtil.Initialize() is no longer required.

harleybl Jan 16, 2014 at 4:56 PM 
I don't think the examples will work if you don't have a call to ExcelAsyncUtil.Initialize() in either an AutoOpen or a macro.