This project has moved and is read-only. For the latest updates, please go here.
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)
                      // 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