Dependent ComboBox: Dynamic Range vs VBA

Posted on 2011-05-11
Last Modified: 2012-05-11
Hi Experts,
I’m creating a number of Excel templates to populate a bespoke financial system from remote locations.

The User is greeted with a UserForm on Workbook_Open that captures initial input using dependent lists / Comboboxes. The UserForm for the first template worksheet is currently driven by dynamic ranges within the worksheet.

Future worksheets will have more complexity and I feel that the dynamic ranges may become cumbersome to setup so I’ll need a VBA Solution.


In terms of populating the UserForm List / ComboBoxes is VBA or a dynamic solution faster? The template needs to run as fast as possible as it’ll be used in regions of the World with poor internet connectivity.

I don’t mind dealing with the cumbersome nature, if it leads to instant dropdowns.

Question by:JohnDiddy77
    LVL 50

    Accepted Solution


    native Excel functions are faster than any VBA. If you can use Excel functions to define the dynamic ranges, they should beat any VBA defined alternative.

    Can you provide a little more detail?

    cheers, teylyn
    LVL 1

    Expert Comment

    Dear I think VBA is a good option as it gives you good versatility and through macros you can get the required results.
    LVL 50

    Expert Comment

    @smartchaps, can you substantiate that? That sounds as if you're painting with a rather broad brush.  And who is "Dear"????

    Author Comment

    @ teylyn: This is my initial thought. I'm just concerned with completing updates in case we add new countries, funds etc... that changes the structure of the Dynamic ranges e.g. 12 instead of 7 columns.

    @smartchaps: Yes, VBA / Macros are massively flexible. However, the application will be used in remote parts of the world with terrible net connections and slow systems, so I need to select an option that will allow for the best performance in all conditions.

    PS Whilst I assume smartchaps meant "Dear John Diddy" - The British PM made a "Dear" comment recently that didn't go down too well! Just saying... :)
    LVL 85

    Expert Comment

    by:Rory Archibald
    If you are loading controls on a form, I don't really see what the difference is between named or unnamed ranges - you simply assign the relevant range's Value to the List property of the control. Whether it is named or not will not impact performance but using named ranges will make it far easier to maintain the list without having to alter the code.
    LVL 1

    Expert Comment

    First of all sorry for the delay as I was out of city and was not having access to net.
    Dear telvyn (Sorry but I mostly use Dear before addressing a name), Dear in my post was used for Dear John Diddy (as he has already made clear) and I think u also don't mind addressing you Dear telvyn.
    If I am to select, I will go for VBA as it is flexible, works well with slow connections also and makes forms user friendly. For me VBA is better.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now