Custom Function caller

[EdwinStraver]

It is extremely important for us that the custom functions can receive the location of the cell, that is calling them. (For example, if the same custom function is called from A3, and C5, we need to know inside the function, if we are getting called from A3 or C5)

Can this be incorporated?

[saunders77]

Thanks for the comment, Edwin! Could you tell me a little more about your use case? Why do you want to know the cell location?

[EdwinStraver]

Hi Michael,

Thanks for your reply. We are planning an add-in that takes the workbook, performs a simulation or optimization, and then the custom functions show different results. We have all the results available, but when the custom function is computing, we need to to know from which cell it is called to return the proper value.

We currently have a COM addin, and there we use a call like

XLOPER12 xAddress; Excel12(xlfCaller, &xAddress, 0);

To retrieve that information.

Hope that helps!

Edwin From: Michael Saunders [mailto:notifications@github.com] Sent: Tuesday, December 19, 2017 4:33 PM To: OfficeDev/Excel-Custom-Functions Excel-Custom-Functions@noreply.github.com Cc: Edwin Straver Edwin@solver.com; Author author@noreply.github.com Subject: Re: [OfficeDev/Excel-Custom-Functions] Custom Function caller (#4)

Thanks for the comment, Edwin! Could you tell me a little more about your use case? Why do you want to know the cell location?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/OfficeDev/Excel-Custom-Functions/issues/4#issuecomment-352929030, or mute the threadhttps://github.com/notifications/unsubscribe-auth/Aaq1Iort1_81kdSHdzCNeaxN2D4OmD38ks5tCFW5gaJpZM4RHY5W.

[jimmcslim]

I think alot of people will be hoping that the custom functions API permits much of the same functionality currently permitted by the XLL API.

Another use case for wanting to know which cell or range of cells (assuming that array formula will eventually be supported) has invoked the function for the purpose of logging.

[saunders77]

We're considering this request. @jimmcslim , could you clarify why you want to log the ranges of caller cells?

[jimmcslim]

I have built an Excel add-in using the excellent Excel-DNA open source library, which is a .Net wrapper around the XLL API.

I'm not sure if you are querying my logging specifically, or wanting ranges specifically... but here is my more general use case.

I work in the field of financial markets, so the addin allows uses to 'load' market data into a cell and then reference that market data elsewhere.

So I might have a formula 'MyAddin.GetMarketData(some set of financial instruments, some date range)' and it will both do the lookup (asynchronously) and return as the value of the cell the string 'MarketData.A1' where A1 is the cell in which the formula was added. The GetMarketData function will also have populated an in-memory cache item with the key set to A1 and the value being the market data that was loaded.

Then I have another formula 'MyAddin.LoadMarketData(some specific financial instrument date, some specific date, a reference to the cell containing MyAddin.GetMarketData'. The third argument to this function could either be a reference to the cell, or it simply be the string 'MarketData.A1'. Either way I need to get the key I need to lookup the data.

Basically, knowing the context in which a worksheet function is evaluating, i.e. which cell it was called from, is realllllllly handy to have both to maintain these sorts of object caches, and to do some logging when dealing with LARGE spreadsheets (as I am sure you can appreciate we have in the finance industry)

[keithalewis]

Maybe the final, hidden argument of async functions can be used for this.

[keyur32]

We've been chatting with @EdwinStraver offline. We're working to introduce this API, which will be passed into asynchronous functions as a method on the invocationContext.getAddress() (a parameter that we pass into your function).

I'll keep this open for now until we have support in the Office Insiders build.

[keithalewis]

FWIW, this is available in the C SDK. It is called xlfCaller. https://docs.microsoft.com/en-us/office/client-developer/excel/xlfcaller.

[keyur32]

Hey folks,

Good news, starting on the December insiders build 1901 (Build 11128.20000), this is now available! We'll have official documentation updated at a later time (and I'll close this then). But for now here is what you need:

a) In your customfunctions.json metadata, for the function you wish to add: options : { "requiresAddress": true }

b) In your code, it will be the last parameter passed in will have an address property, i.e. 'invocationContext' in this case:

function getAddress(parameter1, invocationContext) { return invocationContext.address; //will be in the form !, i.e. Sheet1!A1 }

[keyur32]

This is fixed and docs are updated. closing this issue.