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




Posts: 20
Joined: 2016-02-22
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: 17132
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: Andrei Smolin Invoking a COM add-in from an Excel XLL add-in: advanced sample. 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.

Regards from Belarus (GMT+3),

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




Posts: 20
Joined: 2016-02-22
Thank you, I'll look into it
Posted 01 Nov, 2018 08:32:46 Top