Add-in function calling problem on start

[kumar_8675]

I have an VB6.0 automation add-in which has a set of functions that rea
data from an xml file and return the values. These functions are calle
from excel 2003 spreadsheet cells.

I have one problem in that when the spreadsheet launches, it does no
call the add-in functions and populate the data in the cells. I need t
click on the cells and do a tab change for the functions to be calle
and cells to be populated.

How can I force the add-in functions to be called on the start of th
spreadsheet so that it would populate the sheet with the new values.

Appreciate your help

 

[Stephen Bullen]

Hi Kumar,

I have one problem in that when the spreadsheet launches, it does not
call the add-in functions and populate the data in the cells. I need to
click on the cells and do a tab change for the functions to be called
and cells to be populated.

How can I force the add-in functions to be called on the start of the
spreadsheet so that it would populate the sheet with the new values.

I don't think there's an option for 'Recalc on open' for automation addin
functions, which leaves three alternatives:

1. In your automation addin, implement the IDTExtensibility2 interface
(or add an 'Addin' designer). In the OnConnection event, store a
reference to the Excel Application object you're given, then mark each of
your functions as volatile, by starting them with the statement
Application.Volatile = True. This will mean that Excel calls your
function every time it recalcs (including when first loading), so you'll
probably also want to implement some sort of cache of the results.

2. Use a separate addin (or code in the workbook) to respond to the
Workbook_Open event and force a CalculateFull when it's opened.

3. Remember to press Ctrl+Alt+F9 to update all the calcs when you open
the file.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk