Displaying & Selecting a Scenario using VBA [Modeling in Excel]

Posted on September 24th, 2010 in Financial Modeling , VBA Macros - 20 comments

If you are building financial models or any other type of excel based decision models, chances are, there will be multiple scenarios in your model. Whenever you have multiple scenarios, you may want an easy, intuitive way to select one of them. In this post, I will present an interesting scenario display & selection technique that I received by email from our reader Itay Maor.

First see the scenario selection in action:

Excel Scenarios - Select and Display one Scenario using VBA - Demo

Download the sample workbook with scenario selection macro

Click here to download the workbook (.xlsm file) that Itay emailed me.

How does this work?

In order to understand how this works, first you must know the limitations of this file. It  can only support up to 5 scenarios.

The workbook has a bunch of macros – ChangeScenario, AddTab, RemoveTab, RenameTab etc.

Excel Scenario Selection - How the macro works

Here is how the magic behind this macro is cast:

  1. When you click any tab, that particular scenario’s input values are loaded by running ChangeScenario macro
  2. When you click the ‘x’ button, that particular scenario’s tab is hidden and other tabs are moved accordingly by running the macro RemoveTab.
  3. When you click the ‘+’ button, a new scenario tab is displayed by un-hiding one of the remaining tabs. This uses the macro AddTab.
  4. When you close the workbook, the tab order, scenario values are all preserved automatically.

The workbook uses very simple but clever macros to hide / un-hide tabs and display and select scenarios. I encourage you to dissect the macros and play with the file to understand it better. Go here to download the file.
Financial Modeling using Excel - Online Classes by Chandoo.org & Pristine

Thank you Itay,

Thank you so much for sharing your work with us Itay. I have learned some valuable macro tricks exploring your code. I am sure our readers will be able to learn something from it. Thank you.

How do you handle multiple scenarios?

I never used a technique like Itay’s. Usually I prefer a scenario selection sheet with data validation and conditional formatting (more on this later in a post). I would like to know how you handle multiple scenarios. Please share using comments.

Share your workbooks, example files with us

I am always looking for new and interesting ways to solve problems using Excel. If you have something fun, exciting or useful to share, please email me your workbook / tip / article to at chandoo.d @ gmail.com. I would love to learn from you and share your ideas with others here.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

20 Responses to “Displaying & Selecting a Scenario using VBA [Modeling in Excel]”

  1. Hui... says:

    I prefer to use Data tables for Multiple Scenarios
    Not only can you setup any number of Scenarios this way, Data Tables can also handle any number of input/output variables and are updated as a model is changed

  2. DV says:

    I hope Chandoos method of scenario selection sheet with data validation and conditional formatting would be a no macro thing which a Macro Panicked guy can use.
    @ Hui Can u elaborate bit more about ur way

  3. Sergey says:

    Hey Chandoo,

    Great job posting this long needed scenario wrapper here. One thing though I cannot download the actual xlsm file. The links downloads a bunch of xml files. Can you please help?

    Thanks,
    Sergey

  4. Hui... says:

    @DV Have a read of http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/#monitor-multiple-vars
    Read from the Top down to and including the section called Multiway Data Tables

  5. Chandoo says:

    @Sergey... You need to just drag and drop the .xlsm file in Excel 2007 or above. If you double click, windows shows the contents of the file. Since .XLSM files are a type of zip files, you are looking at them...

  6. Sergey says:

    Chandoo, thanks a lot! Sorry for obvious question 🙂 This is the essence of your blog for me though - every time I'm on it I learn new cool things about Excel. Much appreciated keep it up!

  7. Praveen says:

    Hi Chandoo,

    Interesting post. I have one question, though not related to scenarios. How do you create the visual in this post?
    I hope you'll help me learn this technique.

    Thanks!

  8. ADNAN says:

    plz tell me solution how could i make my own scenario

  9. Mark says:

    In all my years of Excel programming I have never seen anyone use Tabs on a worksheet like Itay demonstrates. I will begin thinking out of the box, looking for ways that I can produce better solutions using some of his methods.

    The vba code is tight, and exceptionally well written by someone who obviously posses a great depth of understanding the object model and coding.

    Excellent work Itay. Thank you for sharing.

  10. Ben says:

    I can't work out how to open 🙁

  11. Ben says:

    doh, didn't see there were two links, got it working now. Would really like to recreate that tab format, any chance of a tutorial? Can't seem to find the macro in vba thats attached to the tabs, they look awesome.

  12. Tonio says:

    Hi,

    I didn't find the .xlsm file in the zip.

  13. Tonio says:

    Hi,

    It works fine. I previously just didn't drag and drop the zip file in Excel.

    Thanks

  14. Luis Alberto says:

    Cannot download file, same as Sergey posted on Sept 24 & Ben

  15. Luis Alberto says:

    Ok sorry, I got it right, regards to all, It is a fine piece of work

  16. Arun says:

    Hi Chandoo, the file which you have given, not able to open,. it is zipped file but no have a excel file. I beleive they all websites files.. Can you please help.. or can you send me raw file(not zipped) at my email address. thanks..

    • Chandoo says:

      @Arun: the download contains .xlsm file which is accidentally shown as zip file in windows. You just have to download the file and directly open it in Excel 2007 or above. If you double click on it, Windows will try to unzip the file.

  17. GCA says:

    Ditto to what Mark (post 9) said. Excellent job Itay, I hope we get to see more examples from you because I am willing to bet that this isn't the only insanely clever workbook design you have in your portfolio.

  18. [...] technique gives the same outcome as the Display and Select Scenarios using VBA article, but easier to [...]

  19. Ghazanfar J says:

    clever!

Leave a Reply