Skip to Content
author's profile photo Former Member
Former Member

EED Non-Worksheet Functions for VBA?

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 24, 2011 at 12:06 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.