EED Non-Worksheet Functions for VBA?

[Former Member]

I am trying to write some custom VBA to go along with our implementation of BPC's Excel Add-On. Basically I am trying to query the server for a list of vendors that I could use to then populate a listbox with. In a worksheet I would use a function like =EVEXP("Production", "All_Vendors") to have cells populated with this information. However, when I try to call this function directly in VBa using EED.g_clsExcel.evprp("Production", "All_Vendors") I get an error that an Argument is not optional, which I assume is related to the formula expecting a range for a worksheet.

So my question is, does anyone know of any function that would allow me to query the server and get an array/dictionary/collection/etc... of values that I can use in a VBA function, as opposed to having to write the values to a worksheet and read from there?

[Former Member · Accepted solution]

I am trying to write some custom VBA to go along with our implementation of BPC's Excel Add-On. Basically I am trying to query the server for a list of vendors that I could use to then populate a listbox with. In a worksheet I would use a function like =EVEXP("Production", "All_Vendors") to have cells populated with this information. However, when I try to call this function directly in VBa using EED.g_clsExcel.evprp("Production", "All_Vendors") I get an error that an Argument is not optional, which I assume is related to the formula expecting a range for a worksheet.

So my question is, does anyone know of any function that would allow me to query the server and get an array/dictionary/collection/etc... of values that I can use in a VBA function, as opposed to having to write the values to a worksheet and read from there?

=================================================================================================

Hi,

BPC does not support what you want, you need to read table or cube directly from your worksheet to get array.

There is an easy way to get a list for a dimension, you may make EVDRE in the other sheet which will be hidden with EXPAND ONLY option. Then VBA get the list of member you want. To make it possible, you need to control expansion in order to get the list before expanding your report. For example, there are two EVDRE or EVEXP(I recommend using EVDRE from performance perspective), 1st EVDRE is supposed to get an dimension for the purpose of reference(listing), 2nd EvDRE is supposed to make a report to a user, then you can make 2nd EvDRE turned off before expanding 1st EVDRE expanding, Once 1st EVDRE is expanded then execute array function of VBA to get an list, then do 2nd EvDRE expand. To minimize loss of performance, you need to make 1st EVDRE expanded with EXPAND ONLY, and listing should be coded with array(Range or Variant type).

If you have already connection with SQL from your Excel, then just get the relevant member from MBR table directly to your worksheet(hidden range or another worksheet), then get list and then expand EvEXP or EVDRE.

Hope this helps,

Regards,

YH Seo