Is there a way to enter a formula from the formula builder via the COM API?

andreas.knetsch

I coded a marco for Excel VBA. It enters a formula, which I have designed using the formula editor, into a cell of the excel spreadsheet. In oreder to achive this, I am using the following command:

Cells(1, 1).FormulaLocal = formula

It works just fine. However the data retrievel won't get updated until the macro has finished, which is beginning to become a major problem. Is there any way that a formula entered in a similar fashion gets updated? I looked into the API, but I am pretty lost there.

Thank you for any help.

Andreas

Alex Putkov.1

Hi Andreas,

The behavior you experienced is expected. The worksheet function you paste into the worksheet and your VBA macro are executed on the same thread. The function cannot return the data into the worksheet until your macro exits and makes the thread available to the function. If I understand correctly all you're trying to do is retrieve the data and get notified when the data is ready, right? There are multiple ways you can achieve that. You can use Eikon COM APIs, which will eliminate any need for worksheet functions at all. All the data you retrieve using =TR, =RHistory or =RSearch worksheet functions can be retrieved using COM objects. This portal provides extensive documentation, code samples and tutorials to help you. A simpler option is to use MACRO parameter in the worksheet function you paste. This parameter allows the function to run a macro once the function finished retrieving the data into the worksheet. The workflow will then be: your macro will need to exit after pasting the worksheet function into a cell. The function will retrieve the data and launch another macro. In this latter macro you can process the data retrieved. For details on the syntax see Help section on function parameters for the function you use and look for the parameter named MACRO. Also see attached a quick example.

Best regards,

Alex Putkov

macrokeyword.zip