0

I've been using a rather long embedded CUBEVALUE() function, which is a pain to work with. It looks something like:

=IFERROR(VALUE(CUBEVALUE(arg1;arg2;arg3));CUBEVALUE(arg1;arg2;arg3))

Due to the CUBEVALUE function and its arguments, it's becoming a REALLY long function and thus not easy to work with. Since there are only 3 arguments, which are written in different cells, I'd like to create something like

=MyFunction(A1,A2,A3)

and use A1, A2 and A3 as "arg1, arg2, arg3" in the function mentioned first. This way its possible to "pull" the function so it would calculate using the input in B1:B3 and C1:C3 etc. as well.

The function works fine and can be pulled through and such, but my question is how to rename this loooong function into something more user-friendly, as it requires only 3 cells as an input and the rest of the text in the function just makes it hard to use for end-users.

Using UDF is not an option because CUBEVALUE can't be called through VBA... and any attempt to stich strings together and using the final result with INDIRECT also seems to fail.. In a similar question on this site, someone refers to using "asynchronous UDF's", but no further information was given (and what I could find seemed irrelevant).

Jerros
  • 11
  • 1
  • 8
  • 1
    Use named range(s) possibly with dynamic `Refers To:` sources to shorten your formula. `Application.Evaluate` or a simple set of square brackets is enough to bypass VBA's reluctance to process a [SUMPRODUCT function](https://support.office.com/en-us/article/sumproduct-function-4e0bffa7-4291-4635-a61f-6aaa9399e7ff). Have you tried that with `CUBEVALUE`? –  Dec 03 '15 at 09:36
  • `Evaluate` doesn't work. VBA can't communicate with the PowerPivot environment at all. The `CUBE` functions can't be called or used or calculated in VBA, it's specified so on the Microsoft website unfortunately. : c Named ranges would be a problem, because then the three input cells can't be specified anymore, so no functionality like `=MyFunction(A1,A2,A3)`. – Jerros Dec 03 '15 at 11:00
  • I am not understanding how, if you only have 3 arguments, and they are written in cells, that your CUBEVALUE function is getting really long. What am I missing here? – Bob Phillips Dec 06 '15 at 16:00
  • See the first code block in the description. It looks something like this: `=IFERROR(VALUE(CUBEVALUE("ThisWorkbookDataModel";"[Table1].[Person].["&A1&"]";"[Table1].[Variable].["&B1&"]";"[Table1].[Time].["&C1&"]";"[Table1].[Value]";"[Measures].[MyMeasure]"));CUBEVALUE("ThisWorkbookDataModel";"[Table1].[Person].["&A1&"]";"[Table1].[Variable].["&B1&"]";"[Table1].[Time].["&C1&"]";"[Table1].[Value]";"[Measures].[MyMeasure]"))`. For an End-user, it would be undesired to select the three input cells six times, and they wouldn't know what to input where... – Jerros Dec 07 '15 at 07:44
  • So since it takes only 3 arguments, a simple `MyFunction=(arg1;arg2;arg3)` is much easier for users. – Jerros Dec 07 '15 at 07:45
  • There are many things you could do to make it (seemingly simpler. You could put all of the model text references in defined names, such as _person refersto := Table1].[Person].[ and soon. The formula then becomes '=IFERROR(VALUE(CUBEVALUE("ThisWorkbookDataModel";_person&A1&"]";_variable"&B1&"]";_time&C1&"]";_value;_mymeasure));CUBEVALUE("ThisWorkbookDataModel";_person&A1&"]";_variable"&B1&"]";_time&C1&"]";_value;_mymeasure))', You could also put the whole CUBEVALUE function in a cell and reference that cell twice in the final formula. – Bob Phillips Dec 08 '15 at 13:06

1 Answers1

0

You shouldn't really have several long cube functions. Allocate some space in hidden rows/columns or in header rows/columns to add your cubemember functions. Then throughout most of your report, you should just have cubevalue functions that reference other cells with error handling around them. Proper use of absolute and relative references are your friend.

Peter Meyers has some great tips for this here, slides 20 - 24. I have an example Excel file with cube functions on my blog here.

mmarie
  • 5,598
  • 1
  • 18
  • 33