General Approach to Wrapping Custom Application around Excel 2003

The project is to create a software that lets user forecast health outcomes using a simple (deterministic) spreadsheet simulation model. The setup is currently a pretty typical spreadsheet model: User inputs "model parameters" (about 75 individual parameter values and five 15x1 parameter vectors)  on user interface worksheet, calculations are processed on a "simulation model" worksheet and output tables and graphs are displayed on a "results" worksheet.

I have been asked to turn this into a "deployable application" with some additional functionality. Specifically, users should be able to save their model parameter sets (perhaps as comma-separated values?) and save their output as a report consisting of text tables and graphs (ideally in a PDF and/or HTML format). When they run the "application" they should be able to browse through their saved parameter sets and pick one to load in (and have option to create a new one).

Ultimately, it would be especially great if user could interact with the output as well. Instead of just a static set of precanned tables and charts, it would be great if user could be given a summary table and then be able to drill down or generate other tables of more detailed information, etc. One way might be to let user export raw output data as a pivot table and let them use Excel's pivot table functions to manipulate data.

I am seeking advice on a general approach to implementing this. I have decent VBA skills, and also novice experience with Visual Basic Express and Visual Web Developper. Some have suggested that I write the entire software as a web-based tool so user doesnt even need Excel, others have suggested I do the whole thing in VB and store parameter sets as textfiles. The web-based tool approach could work. However, this project also has the goal of buidling transferrable skills for users, so I thought the Excel environment would be nice. The users will be analysts in government offices of developing countries. Some will know pivot tables, but many will not... if as part of the training, they learn to use pivot tables, they can then transfer that pivot table skill to other excel based work they do. My fear is that by giving them a standalone VB application, we will lose an opportunity for the training to teach some transferable skills.  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I have no doubt others will put in their twopennyworth. However here are my thoughts on. Firstly, users of numerical data, wherever they are need to understand, appreciate and be able to use Excel. Let's not mess around and suggest it should be something else, as MS Excel is now the worldwide de facto standard - for good or for bad. So Excel it is.

Having established that, then the question is how best to improve people's Excel skills. The only possible, practical way is to get them to use Excel. If you encode the whole project in something other than VBA then no one will be able to dig into it in the same way as VBA in Excel. That 'digging' process is essential for learning.

However Excel is not secure and people could get into the code and mess it up easily. Having said that I believe that most of what you want to achieve could be done in VBA. I believe that if you are not too sure yourself of any particular aspect of the project implementation, then there are plenty of experts here who would guide you through that. It's all about cutting up the project into bite-sized chunks that can be dealt with in a question here. You have good VBA skills so perhaps you won't need any help from EE but as you're a PSMember I would not hesitate to ask as many questions as you need.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevereschAuthor Commented:
Thank you for the feedback. I concur with your perspective on the "digging in" learning process for novice Excel users. I know my question was not well-formed in a digestable pieces... what I have been struggling with is whether VBA alone is sufficient to develop the "file management" tasks (that is, the saving and loading of parameter sets), plus I need to be sure that the end product looks and feels "professional". Thats why I was entertaining a VB Express / Excel hybrid, pure VB Express, or Visual Web Developer. I guess it will be wise to stick to VBA where my skills are strongest.

Thanks, Steve

Firstly, thanks for the grade.

>I guess it will be wise to stick to VBA where my skills are strongest.

I believe you are right to stick with what you know best - VBA. It has the added advantage that the better Excel users will want to learn it as well so by you sticking to it it really does improve the general team skill-set.

As for the file management aspects I feel sure that the top experts here on EE (but not me) will be able to solve those issues for you. Just ask and I'll bet that within 24hrs you'll have the answers.

The saving and loading of parameter sets cannot be that hard if a separate worksheet (preferarbly not a separate workbook) contains them all in a logical and easily accessible layout. That surely is, if anything, the easiest part of the jigsaw puzzle.

Hope to see your questions flowing...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.