Modifying other cells in custom UDF

Add-in Express™ Support Service
That's what is more important than anything else

Modifying other cells in custom UDF
 
Nikita Bodnar


Guest


Hello!

I'm trying to figure out how to modify other cells on a sheet where an async UDF is calculated.

Could you please provide me with a working code snippet?
Posted 31 Oct, 2018 06:23:03 Top
Andrei Smolin


Add-in Express team


Posts: 18819
 Joined: 2006-05-11
Hello Nikita,

By definition, a UDF cannot modify any cell: it simply returns the value calculated or an error. To be able to modify a cell, you should do this outside of the context of the XLL call to your function. The technique is described in the sample add-in accompanying this blog: https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/. In that sample add-in, the method ShowPane (it is defined on the add-in module) is called in this fashion:
- the XLL UDF calls ADXAddinModule.SendMessage to send a unique message to the hidden window that Add-in Express creates for your COM add-in;
- immediately after that the XLL UDF quits; this is the moment when Excel can switch out of the XLL call context
- when the hidden window receives the message, it raises the OnSendMessage event; at this moment, Excel is in the COM add-in context
- the event handler of the OnSendMessage event calls the ShowPane() method

You should modify this example as follows: instead of calling the ShowPane() method, call some other method that will modify the required cells. That method will be executed in the COM add-in context; as if you've clicked a button to call that method.


Andrei Smolin
Add-in Express Team Leader
Posted 31 Oct, 2018 08:35:44 Top
Nikita Bodnar


Guest


Thank you, I'll look into it
Posted 01 Nov, 2018 08:32:46 Top