I am using Excel 2007 and I have designed a form that has several combo boxes. Currently, I am populating the combo boxes thru VBA by using .additem. For example, one combo box might be:
The problem with hard-coding it this way is the amount of effort in maintaining the code; that is, every time I want to update the entries, I have to modify the code. Since multiple people will be using this Excel workbook separately (i.e., not a shared workbook), I would have to release a new revision every time I made changes like this.
So, I have a few design questions:
1. I'm considering the idea of putting this list of entries in a separate file (database table) so that I could update this table independently, as needed (instead of having to update the VBA code and issue a new revision of the program). Can you give me an example of how could I write a For-Next loop that would best accomplish this? Assume the database (Access or SQL) is not local, but on a webhost server (internet site).
2. How big of a performance hit would it be for VBA to pull in data from an external source vs having it hardcoded internally? I know it's a very subjective question, but I hoping that someone may have some experience in this area and can answer it or provide a better approach. I'm trying to weigh the ease of maintenance vs performance.
Code:
With .City
.AddItem "Abilene"
.AddItem "Abbotsford"
.AddItem "Abidjan"
.AddItem "Adana"
.AddItem "Addis Ababa"
.AddItem "Adelaide"
.AddItem "Ahmedabad"
.AddItem "Akron"
etc...
End WithThe problem with hard-coding it this way is the amount of effort in maintaining the code; that is, every time I want to update the entries, I have to modify the code. Since multiple people will be using this Excel workbook separately (i.e., not a shared workbook), I would have to release a new revision every time I made changes like this.
So, I have a few design questions:
1. I'm considering the idea of putting this list of entries in a separate file (database table) so that I could update this table independently, as needed (instead of having to update the VBA code and issue a new revision of the program). Can you give me an example of how could I write a For-Next loop that would best accomplish this? Assume the database (Access or SQL) is not local, but on a webhost server (internet site).
2. How big of a performance hit would it be for VBA to pull in data from an external source vs having it hardcoded internally? I know it's a very subjective question, but I hoping that someone may have some experience in this area and can answer it or provide a better approach. I'm trying to weigh the ease of maintenance vs performance.
