Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Dependent ComboBox: Dynamic Range vs VBA

Posted on 2011-05-11
Medium Priority
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

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 35736329

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

Expert Comment

ID: 35736344
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
ID: 35736379
@smartchaps, can you substantiate that? That sounds as if you're painting with a rather broad brush.  And who is "Dear"????
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 35736518
@ 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
ID: 35736712
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.

Expert Comment

ID: 35810408
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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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