D
Don
I'm in the process of learning how to use VSTO for Excel after several years
working with VBA. My question is about "best practices" for initializing
data. SOme background ....
My application:
Create an excel spreadsheet summarizing Problem Reports extracted from a
database based on search criteria specified at run time.
The problem:
I want to map responsibilities for individual problems to different managers
for summarization purposes. The concept of "managers" is not represented in
this (or any) database. Each Problem report is assigned to an individual,
whose name I can extract from the database.
My VBA solution:
I have an addin (xla) with a custom tool bar and button to prompt for query
criteria.
I created a flat text file where every record is like "manager
name";"individual name" and I load this into a global scripting.dictionary
using "manager name" as the item value and the "individual name" as the key
during the worksheet open event for the addin. I can then dynamically
populate a "managers" column in the spreadsheet after a query by looking up
the names of the responsible individuals returned from my query in this
dictionary. I've hardcoded a default location for this data file, but if it
is not found, I open a "open file" dialog.
My question:
I can carry on on with the same approach but I'm not sure that is the best
way to think about the problem. Should I keep the data file as an object in
my assemby or should I use a different technique all together? Should I
create "people" objects for every individual and provide a manager methods or
have a single class/object that holds my dictionary. What ever approach I
take I want to preserve the nice feature of the current sollution - any time
new people come or go it is easy to edit this data file without having to
update my addin.
My gut feeling is I need to develop a new mental model for how I look at
solving problems like this but I'm not quite sure yet what the new model
should be.
working with VBA. My question is about "best practices" for initializing
data. SOme background ....
My application:
Create an excel spreadsheet summarizing Problem Reports extracted from a
database based on search criteria specified at run time.
The problem:
I want to map responsibilities for individual problems to different managers
for summarization purposes. The concept of "managers" is not represented in
this (or any) database. Each Problem report is assigned to an individual,
whose name I can extract from the database.
My VBA solution:
I have an addin (xla) with a custom tool bar and button to prompt for query
criteria.
I created a flat text file where every record is like "manager
name";"individual name" and I load this into a global scripting.dictionary
using "manager name" as the item value and the "individual name" as the key
during the worksheet open event for the addin. I can then dynamically
populate a "managers" column in the spreadsheet after a query by looking up
the names of the responsible individuals returned from my query in this
dictionary. I've hardcoded a default location for this data file, but if it
is not found, I open a "open file" dialog.
My question:
I can carry on on with the same approach but I'm not sure that is the best
way to think about the problem. Should I keep the data file as an object in
my assemby or should I use a different technique all together? Should I
create "people" objects for every individual and provide a manager methods or
have a single class/object that holds my dictionary. What ever approach I
take I want to preserve the nice feature of the current sollution - any time
new people come or go it is easy to edit this data file without having to
update my addin.
My gut feeling is I need to develop a new mental model for how I look at
solving problems like this but I'm not quite sure yet what the new model
should be.