build vba script for sensitivity anaylsis

[Exequiel3k]

Hello to all!

For the past two weeks, I've been trying to learn VBA in order to finish this project. I've come to the realization that I clearly won't have the skills to finish it within the time constraint set-forth and am in dire need of help for the last part of the sheet..

What happens is that I need a way to display the outcome based on 8 different inputs in order to find the best possible settings for the 8 inputs.
If it was 2-3 variables I could of used the Data-Analysis package, but I can`t.
I tried the Solver-Analysis tool by setting up all the constraints although everytime I run it the results are very inconsistent even if I stretch the time allowed for processing.

Is there anybody who could please help me out in writing the script in order to do a proper sensitivity analysis?
I really appreciate you taken the time to read this..



*I've stripped the file as much as I can and ZIP`d it to fit within the size limit of 1mb*

[AlphaFrog]

I looked at your sheet and didn't really follow what you are doing.

One concern though was that you want to calculate all combinations of the eight inputs.
25*25*20*20*20*4*4*6 = 480,000,000 combinations

That many calculations is more that Excel could do in any reasonable amount of time.

[Exequiel3k]

I`ve stripped a lot of things so it looks a tad sketchy, but the formulas/sequences are all good..

Yeah, it's a lot of combinations :p My computer is very fast and I'm more than wiling to wait a couple of hours (if it's possible) for the report to come out..

About the length, the script could be based on something like (5*5*4*4*2*2*2*3 /or/ any combination which you prefer) and I could go in and modifiy the range to run the larger-settings after..

[AlphaFrog]

Each of the 480,000,000 combinations is 5 calculations = 2.4 billion data values. Then of course you'll want to further process those 2.4 billion individual values in some way. Assuming Excel doesn't melt your desktop Cray computer, this is way beyond hours; it's more like days or even weeks. Assuming it doesn't crash.

[Exequiel3k]

haha, excel is capped at around 25% of the CPU usage by windows ;p

I'm concious of the what I'm asking the cpu to process though for the sake of the script it could even be a combination of 2*2*2*2*2*2*2*2 (or any other settings prefered) and I'd scale it up afterwards..

The thing is, once the report comes out with only the combinations which gave -all- positve outcomes; it will be very easy to process..

[Lemice]

Hello Exequiel3k,

Here is a shot at what you are attempting to do (I think)

Here is the code:

Please Login or Register  to view this content.

And here's your file with the macro. Simply open the Editor and F5, or click the button in Sheet2. The macro will take in the last line with the 8 input combination in Sheet2, increase it by 1, and starts from there, until it executes a certain amount of calculation, it will stop (Defined by Constant Limit at the top of the macro).

As you can see, Line 31 is a line I test by putting in a random combination of 8 inputs, and the macro will take it from there.

I did it like this because of the follow reasons:

1. Having a limit is for testing purposes - partial testing is much more reasonable and controllable when you have this huge amount of combination to test (FYI it takes about 1.53s to finish 10 calculations in my laptop, and it's fairy good)
2. Having a test to take in the last combination of the last line in Sheet2, will enable you to continue testing. and
3. This is too resource and time consuming because the macro simply generate a combination, paste it back to sheet1, force a recalculation, then paste the results in B25:B29 in an Array to later paste in Sheet2. If you want to further optimize this, you would want to move every calculation in Sheet1 into the macro, it should significantly reduce the time needed to calculate instead of this back-and-forth relationship between the macro and the worksheet (too inefficient)

You can easily tell the macro to only print out combination that give positive / negative results, however you want.

Is this what you were looking for?

Also, I suggest changing the title of the thread according to Forum Rules

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

[Exequiel3k]

Wow Lemice, this is exactly what I was hoping for!!
I really appreciate you taking the time to script this :D

Also, I've changed the heading.. :p

[Lemice]

Thank you for the feedback.

If you have found a fitting solution to your problem, please mark the Thread as [SOLVED] using the Thread tools right above post #1. It keeps things neat and tidy. Also, don't hesitate if you have any other questions regarding to the codes.

And, have a nice day.