AnaplanXL & VBA Macros

RyanMadcharo

Hello, I have two questions related to building a VBA macro that uses AnaplanXL.

Above is a picture of what I'm building toward, a data grid with several header rows. The color-coded headers are our list dimensions in Anaplan. In this example the yellow cell's value should be the intersection of Dept_1, Line_Item_1, Version_B, Channel_1, Mar 26, and Scenario_1, data that lives in an Anaplan model.

So far my macro gives users the ability to choose the list members to build the header rows/ columns, and builds this empty shell. My next move is to populate the data.

For my questions:

Question 1: What is the best AXL formula for populating the grid? I'm currently working with XL3LOOKUP() because it's the only one I know of. My VBA script is currently building unique XL3LOOKUPs for every cell in the grid. Is this correct? Or is there a different formula/method that would allow me to work with lists/arrays of inputs?

Question 2: Is it possible to have my VBA script call XL3LOOKUP() (or a related function) and then write the numeric values of that call into the data grid? If this is possible, it would be better than populating all the cells with formulas, but my attempts to use Application.Run or Application.Evaluate have returned errors so far.

Thank you!

(Extra note: I've picked a Formula report over a Grid report because I've oversimplified the picture. The Line Items come from 3 different modules with slightly incongruent dimensionality. My macro is already handling the dimensional nuance.)

ElizabethS

Anaplan XL does not operate well with native Excel Macros; there are a few AXL-specific macros you can include in a VBA script. https://help.anaplan.com/macros-c0cbb118-c095-4932-891d-94d863034dc8 . However, we generally recommend that you avoid using Macros in your AXL workbooks.

BIT, I think some of the native AXL formulas may help you achieve what you are trying to do with this report. Have you seen the list of available functions? https://help.anaplan.com/anaplan-xl-reporting-formula-reference-435a47a6-030a-4ecc-be09-f49a98394253

A good place to start learning the basics of how formula reports work in AXL is this tutorial video available in the community (there are loads more videos to help teach and inspire). https://video.anaplan.com/watch/jEzvM1N5eJxNVboRvThv3g

And remember that grids are the most dynamic and performant, so we recommend that you use Grid Reports for most things. In your case of combining 3 different modules with mismatched dimensionality, it may still be possible to use multiple grids and use the link grids functionality against the common dimensions - you can do some creative options to make this seamless in many cases. Here is another tutorial video: https://video.anaplan.com/watch/wFv39GbUSQY7WXnEXNkYkz

Please let us know what you come up with!