Excel user defined functions

dermot

If my Excel model has user defined functions, ie multiline VBA functions that are called by worksheet cells, can Anaplan handle them?

I could not find anything about them online

 

thank you

JaredDolich

@dermot 

I think you'll find the Excel add-in to be exactly what you need. With the latest upgrade you can even push data back to Anaplan.

But, you cannot control the pull/push to/from Anaplan from VBA. Well, at least I haven't been successful. So, it may seem a little manual and you have to make sure  your users have the proper security to get the data from Anaplan.

I would highly encourage you though to work with your Anaplan business partner to evaluate your spreadsheet to see if it's possible to build the same in Anaplan.

I think you'll be surprised.

Also, don't hesitate to ask in this Community exchange how you might solve some tricky Excel functions in Anaplan. There are some amazingly smart people here that love answering those types of questions.

About the only time I really need to bring data to Excel is when I need to iterate on the data or when I just need that little extra formatting for reporting. Those are a little harder to do in Anaplan. Plus, I usually use Tableau, Power BI, R or Python for that, depending on what it is I need.

Anaplan is pretty flexible. 

I can't wait to hear what your experience is like with Anaplan. Hope it's set up soon!!

 

nathan_rudman

Anaplan is pretty good at handling exception and special ways to calculate something.

 

Jared, I can confirm VBA isn't able to control the add in.

JaredDolich

@dermot 

Great question.

For Excel, Anaplan has an add-in which is completely independent of the VBA in your sheet.

Your functions should continue to work.

 

If you're asking if you can control Anaplan using VBA then I would say not really.

The best solution is to use the Anaplan Add-In to pull your data, then once it's in your sheet you can use your functions.

It's been a while since I built my own functions but a few years ago, I noticed that sometimes the functions wouldn't be raised unless I touched the cell because I was doing a lot of iterations in my function. That may have been improved now.

 

Let me know if this answers your question. If not, give me a more specific example of what your function does. It would be fun to try to replicate it.

dermot

Thank you for that answer, Jared

The situation is that this organisation is in the process of purchasing Anaplan, and I am assisting in the redesign of an existing Excel forecasting model. I want to do this in a way that will make it easy to integrate with Anaplan in the future. However, at the moment, it seems to be hard to get any specific information about Anaplan until the purchase is completed, hence my question.

If you are saying that the Anaplan add-in hooks into Excel, and so you can use Excel normally, but have access to various Anaplan functions (or maybe it's the other way round and Anaplan has access to Excel and can run it for you), then that makes things pretty straightforward. I think Excel will need to continue to be used because there are quite a few exceptions and oddball situations that have to regularly be catered for.

So it sounds as though I can plan the Excel model as normal, and then integrate things like data collection with Anaplan later on.

(wrt raising functions, normally one would include the line Application.Volatile at the start of a user defined function, which forces recalc every time Excel recalculates)..

Thank you again for helping me understand this.

dermot

Thanks for the assistance, Jared, much appreciated

I think I know enough to keep going and I can learn more once Anaplan is available to us

 

Which answers my question,

dermot