I need to create a Monte Carlo excel add-in that will allow me to run simulations while displaying a pop up status bar for say "completing 1 of 50,000 simulations" or something like that, which also has a pause, start, and stop button on the window so if I need to cancel the simulation then I can. I already have an excel spreadsheet that is set up with a hidden personal file that also opens that has a formula I use to simulate random normal variables, but my problem is I don't know how to make this more efficient. Also, I would like to find a way to store data from the simulations and have it create a summary tab afterwards, but I don't know if that's possible. Right now, I just have a copy/paste built into the macro to drop the values into another worksheet. My problem is I would like to be able to use this simulation add-in for multiple projects which will have different setups, so I would like to be able to click on the Monte Carlo add-in allow me to basically define how many simulations, then select say the yearly simulated return and the ending simulated portfolio value and have those values stored somehow or summarized somewhere to make data analysis easier. I'm not that familiar with all this, so I could use some help. I need everything done in excel though because I would like to be able to send my colleagues the add-in to use as well. If you look in the attached macro file you'll see in Visual Basic editor Module97 has the coding I have been using to simulate random normal variables and the function. The excel file below is the file I am using to simulate portfolio returns which I want to have a way where I can store the simulated returns and the final portfolio value so I can use those in a summary somehow. The add-in will be used for multiple files though that's why I want to find a way to program it so that it can be used in various applications. For someone who knows what they are doing I don't expect this project to be that difficult.
There are also a ton of macros in the macro file that are for formatting styles and what not, I was wondering how easy it would be to make those macros into some kind of add-in so I don't have to have the hidden personal file.