Catégorie : Excel VBA Course

Let us demonstrate how the Scenario object works with a simple example.

We will create an expense table for LLC “Megatop” for January and forecast the expenses for the next month.

Our forecast will be based on the assumption that the relative structure of expenses in February will remain the same, while the absolute value will increase due to inflation.

We assume the inflation rate in February will be:

• best case: 1%
• worst case: 7%
• most likely: 3%

Setting up the analysis as follows Work with Scenarios

We will organize the scenario:

• Place a list box on the worksheet and, in the Properties window, set its Name property to lstScenarios.

When the workbook is opened, the Workbook_Open event procedure will automatically fill the list box with the names of possible inflation scenarios for the following month.

Clicking on a list item will:

1. • enter the chosen inflation rate into cell E2;
   • recalculate the projected expenses for the next month.

• In cell B7, enter a formula to calculate the total expenses:

=SUM(B2:B6)

• Select the range C2:C7 and enter the following array formula:

=B2:B7*(1+E2)

To confirm, press Ctrl+Shift+Enter (since this is an array formula).
This formula allows you to calculate the expected expenses for the entire range of values at once.

VBA Code Example

In code, such scenarios are implemented:.

Expense Scenarios Based on the Scenario Object. ThisWorkbook Module

Private Sub Workbook_Open()
    Dim sc As Scenario
    Dim i As Integer
    Dim V As Variant
    ' Delete existing scenarios
    For Each sc In Worksheets("January").Scenarios
        sc.Delete
    Next
    With Worksheets("January")
        ' Add new scenarios from values in column D (rows 10–12)
        For i = 10 To 12
            V = .Cells(i, 4).Value
            .Scenarios.Add Name:=.Cells(i, 3).Value, _
                ChangingCells:=.Range("E2"), Values:=V
        Next
        ' Configure the list box
        With .lstScenarios
            .ColumnCount = 2
            .ListFillRange = "C10:D12"
            .BoundColumn = 2
            .ListIndex = 0
        End With
    End With
End Sub

Expense Scenarios Based on the Scenario Object. Worksheet January Module

Private Sub lstScenarios_Click()
    Worksheets("January").Scenarios(lstScenarios.Text).Show
End Sub

This example demonstrates how to:

• Define different inflation scenarios;
• Use the Scenario Manager programmatically with VBA;
• Automatically update expenses when a scenario is selected from a list box.

The Scenario object allows you to store multiple values in a single cell and represents a scenario.
The Scenarios collection consists of Scenario objects and contains all the scenarios of a worksheet.

Table. Methods of the Scenarios Collection

Method	Description
Add	Adds a new scenario. Add(Name, ChangingCells, Values, Comment, Locked, Hidden) • Name — the name of the scenario; • ChangingCells — the range allocated for the scenario’s changing cells; • Values — an array of values entered into the changing cells; • Comment — a text string of comments; • Locked — a logical (Boolean) property. If set to True, modification of the scenario is blocked; • Hidden — a logical (Boolean) property. If set to True, the scenario is hidden.
CreateSummary	Adds a new worksheet to the workbook and creates a summary report. CreateSummary(ReportType, ResultCells) • ReportType — the type of report. Permissible values: → xlStandardSummary (a standard outline report); → xlSummaryPivotTable (a PivotTable report). • ResultCells — a reference to the cell or range of cells with formulas dependent on the values of the cells specified in the ChangingCells parameter of the Add method.The report is created on a separate worksheet and is not linked to the source data. It is very useful to assign names to the cells specified in the ResultCells parameter before generating the report. Otherwise, instead of meaningful names, the report will contain less comprehensible cell references.

Table Methods of the Scenario Object

Method	Description
Show	Displays the scenario by entering the scenario’s values into the changing cells.
Delete	Deletes the scenario.
ChangeScenario	Changes the group of changing cells in a scenario. ChangeScenario(ChangingCells, Values) • ChangingCells — the group of cells that will act as the new set of changing cells; • Values — an array with new values for the changing cells.

Table. Properties of the Scenario Object

Property	Description
ChangingCells	Returns the range of the changing cells. Example: ActiveSheet.Scenarios(1).ChangingCells.Select
Values	Returns an array of the current values of the changing cells. Example: ActiveSheet.Scenarios(1).Values = Range(« B1:B3 ») or ActiveSheet.Scenarios(1).Values = Array(1, 3, 5)

 

Let us consider an example. Suppose the project costs amount to 700 million rubles. The expected revenues over the next 5 years are, respectively, 70 million rubles, 90 million rubles, 300 million rubles, 250 million rubles, and 300 million rubles.

We need to assess the economic feasibility of the project by its internal rate of return, given that the market rate of return is 12%.

Also consider the following alternatives (project costs indicated with a minus sign):

• (–600; 50; 100; 200; 200; 300)
• (–650; 90; 120; 200; 250; 250)
• (–500; 100; 100; 200; 250; 250)

To calculate the internal rate of return (IRR), the following function is used:

=IRR(Values; Guess)

In this case, the function for solving the problem uses only the argument Values, one of which must be negative (project costs). If the internal rate of return exceeds the market rate of return, the project is considered economically feasible. Otherwise, the project should be rejected.

The solution for this example is shown:

Formulas for calculation:

• In cell B84:

=IRR(B75:B80)

• In cell C84:

=IF(B84>B82,"The project is economically feasible","The project must be rejected")

Creating Scenarios

Let us consider this example for all combinations of initial data.

To create (or modify) a scenario, use the Scenario Manager command from the What-If Analysis list in the Data Tools group on the Data tab.

In the Scenario Manager dialog box, click Add to add a new scenario. In the Add Scenario window 

enter a new name for the scenario and set the necessary parameters. After clicking OK, you can enter new values for the changing cells.

To save the results for the first scenario, it is not necessary to edit the cell values — simply click OK to confirm the default values and return to the Scenario Manager window.

Adding More Scenarios

To add new scenarios for this task, simply click Add again in the Scenario Manager window and repeat the above steps, changing the values of the initial data.

In Fig. :

• Scenario Turnover_Speed_1 corresponds to data (–700; 70; 90; 300; 250; 300)
• Scenario Turnover_Speed_2 corresponds to data (–600; 50; 100; 200; 200; 300)
• Scenario Turnover_Speed_3 corresponds to data (–650; 90; 120; 200; 250; 250)
• Scenario Turnover_Speed_4 corresponds to data (–500; 100; 100; 200; 250; 250)

By clicking Show, you can view the calculation results on the worksheet for the corresponding set of initial values.

Generating the Scenario Report

To obtain a summary report for all added scenarios, click Summary in the Scenario Manager window.

In the Scenario Summary dialog box

, select the desired type of report and specify the cells that contain the resulting functions.

When you click OK, a report for the scenarios is generated on the corresponding worksheet.

Each unique value in a cell, or each unique group of values for a group of cells, is called a scenario. Scenarios make it possible to perform so-called “what-if” data analysis. You can enter different values into key cells and observe what happens as a result. Quite often, it is necessary to have various solution options at hand, and scenarios provide exactly this possibility for the user.

The Scenario Manager in MS Excel allows you to automatically perform “what-if” analysis for different models. You can create several sets of input data (changing cells) for any number of variables and assign a name to each set. By the name of the selected data set, MS Excel generates analysis results on the worksheet. In addition, the Scenario Manager allows you to create a scenario summary report that displays the results of substituting different combinations of input parameters.

The Scenario Manager is opened with the Scenario Manager command, which is selected from the What-If Analysis list located in the Data Tools group on the Data tab of the ribbon.

In the window that appears, using the corresponding buttons, you can add a new scenario, edit, delete, or display an existing one, as well as merge several different scenarios and obtain a summary report for the existing scenarios.

We will use the Subtotal method and the Outline object to solve a simple task. We will work with a data list having the following fields: OrderID, ShippingCost, RecipientName, RecipientCity, RecipientCountry, which reflects the necessary shipping expenses for delivering orders to specific customers. We need to obtain summary data on the number of orders placed by each customer and the total postal (shipping) expenses for delivering these orders for each country.

Create a form and place on it a toggle switch, a button, a spin control, a text box, and a label.
When the toggle switch is on, it displays the caption Subtotals applied, and the worksheet will create subtotals that count the number of orders placed by each customer and the total postal (shipping) expenses for delivering these orders.

Clicking the Sort by RecipientCountry button performs sorting by the RecipientCountry field of the list, which must be done before creating the subtotals.

The spin control will allow you to manage the display of different outline levels of the subtotals. When the toggle switch is off, it displays the caption Subtotals removed, and the subtotals are removed from the worksheet. They are also removed when the dialog box is closed.

To complete the application, enter the corresponding code in the form module.

The Outline object encapsulates data about the worksheet outline.
The Outline property of a worksheet returns an Outline object.
Table lists the main properties of the Outline object.

Table. Main Properties of the Outline Object

Property	Description
AutomaticStyles	Accepts logical values. If this property is set to True, the outline is built based on automatic styles.
SummaryColumn	Returns the location of the summary columns. The allowable values are the following XlSummaryColumn constants: xlLeft (summary columns are located to the left of the columns being summarized), xlRight (summary columns are located to the right).
SummaryRow	Returns the location of the summary rows. The allowable values are the following XlSummaryRow constants: xlAbove (summary rows are located above the rows being summarized), xlBelow (summary rows are located below).

Displaying a Specified Number of Outline Levels

The Outline object has a single method, ShowLevels, which displays the specified number of outline levels for rows and columns.

ShowLevels(RowLevels, ColumnLevels)

• RowLevels — optional parameter that sets the number of displayed outline levels for rows.
• ColumnLevels — optional parameter that sets the number of displayed outline levels for columns.

Removing an Outline

The ClearOutline method of the Range object removes an outline.
For example, the following instruction removes the outline associated with the range A1:I40:

Range("A1:I40").ClearOutline

Displaying Outline Symbols

The DisplayOutline method of the Window object accepts logical values and controls the display of outline symbols.
For example, the following instruction hides outline symbols:

ActiveWindow.DisplayOutline = False

Automatic Outline Creation

The AutoOutline method of the Range object automatically creates an outline that replaces the existing one.
If the Range object is a single cell, the outline is created for the entire worksheet.
For example, the following instruction creates an outline for the range A1:I40:

Range("A1:I40").AutoOutline

The purpose of structuring is to split the data contained in a worksheet into specific levels of detail. By using structure, it becomes easier to analyze and compare data.

If there is a strict dependency between the data, MS Excel allows you to automatically create a structure: in this case, MS Excel searches for cells that contain formulas summarizing the information in rows and that are located on the left. The data must be consistent in one direction. To perform automatic structuring, all detailed columns must be on one side of the total columns, and all detailed rows must be positioned relative to the totals either only below or only above them. If this condition is not met, the structure must be created manually.

A worksheet can contain only one structure, although it can be divided into several parts.

Showing and hiding structured data can affect parts of the worksheet that are not included in the hierarchy, since rows collapse and expand across the entire width of the worksheet, and columns — across the entire height of the worksheet.

When a structure is displayed, special symbols appear along the left and top edges of the worksheet. These symbols are used to show and hide levels of detail.

Table. Outline Symbols

Outline Symbol	Purpose
Button to show detailed data	Expands details
Button to hide corresponding details	Collapses details
Level numbers	Sequential levels for rows and columns
Outline level	All detailed rows or columns of one level

To automatically create a structure, you should:

• Check that the total formulas contain references to detailed data located in one direction relative to the totals;
• For structuring part of a worksheet, select the desired cell range; for structuring the entire worksheet, select a single cell;
• Use the Create Outline command, choosing it from the Group list located in the Outline group on the Data tab of the ribbon.

To manually structure a worksheet, you need to:

• Select the necessary cells of rows and columns to be grouped into a structure, excluding the cell with the total formula;
• Use the Group command from the Group list located in the Outline group on the Data tab of the ribbon;
• In case of errors or to ungroup data, select the Ungroup command from the Ungroup list located in the Outline group on the Data tab of the ribbon;
• To show or hide structured data, use the Show Detail and Hide Detail commands, also located in the Outline group on the Data tab of the ribbon;
• To return the worksheet to its original state, use the Clear Outline command from the Ungroup list located in the Outline group on the Data tab of the ribbon.

For structured data, it is also possible to create charts based on specified outline levels.

Let us demonstrate, using a business case of constructing a summary table of the expenses of the company LLC “Alliance” for the reporting period, how to create and delete consolidating tables in code. For this, create a workbook containing several sheets, for example, January, February, March, with tables In addition, the workbook must contain an empty sheet named Summary. After that, add the corresponding code to a standard module and to the ThisWorkbook module.

NOTE
The program consolidates an unspecified number of tables. Therefore, you cannot use the Array function (with an unknown size) as the value of the Sources parameter of the Consolidate method. This issue is easily solved in the program — by introducing an additional variable of type Variant, assigning it the values of a dynamic array containing the addresses of the consolidated tables. Afterwards, this auxiliary variable is used as the value of the Sources parameter.

The standard module contains two procedures that implement the business logic of the project:

• ConsolidationBuilder: builds the consolidating table from any number of data sheets whose names differ from the sheet name of the consolidating table (i.e., from the sheet Summary). Before creating the required constructions, this procedure checks whether the Summary sheet already contains a table (more precisely, whether there is any data in its first column). If such data is present, no new construction is performed.
• ConsolidationKiller: deletes the consolidating table. More precisely, it removes the structure created by this table using the ClearOutline method and clears the cell contents using the Clear method. Before deletion, this procedure checks for the presence of such a structure on the worksheet, and if it does not exist, deletion is canceled as unnecessary.

In the ThisWorkbook module, there are two procedures that place the necessary buttons on the Add-ins tab of the ribbon when the workbook is opened, and remove them when the workbook is closed :

• The Open event procedure of the Workbook object constructs a toolbar Consolidation (classic style), on which two buttons are created: Consolidate and Delete Consolidation. These buttons appear on the Add-ins tab in the Custom Toolbars group and execute the procedures ConsolidationBuilder and ConsolidationKiller.
• The BeforeClose event procedure of the Workbook object removes the created Consolidation toolbar with its buttons when the workbook is closed (and, accordingly, the Add-ins tab will not appear when other workbooks are opened).

For programmatically constructing a consolidation table, the Consolidate method of the Range object is used. This method allows you to summarize and consolidate homogeneous data placed in several ranges. On a worksheet, the actions programmed by the Consolidate method correspond to the Consolidate command located in the Data Tools group on the Data tab of the ribbon.

expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)

• expression — a reference to the range or cell in its upper-left corner where the consolidation table will be created.
• Sources — optional parameter, specifies an array of references in R1C1 format to the ranges from which the consolidation table is built. The references must contain full range names including worksheet names. Example:

Array("'January'!R1C1:R5C3", "'February'!R1C1:R5C3")

• Function — optional parameter, specifies the function on which the consolidation table is based. Acceptable values are the following XlConsolidationFunction constants:
  • xlAverage (average),
  • xlCount (number of values),
  • xlCountNums (number of numbers),
  • xlMax (maximum),
  • xlMin (minimum),
  • xlProduct (product),
  • xlStDev (unbiased variance),
  • xlStDevP (biased variance),
  • xlSum (sum),
  • xlVar (unbiased deviation),
  • xlVarP (biased deviation).
• TopRow — optional Boolean parameter. Indicates whether consolidation is based on the column headers of the consolidated ranges.
• LeftColumn — optional Boolean parameter. Indicates whether consolidation is based on the row headers of the consolidated ranges.
• CreateLinks — optional Boolean parameter. Indicates whether the consolidated table is linked to the source tables. If set to True, the consolidated table is displayed as an outline.

Properties of the Worksheet Object Important for Data Consolidation

In data consolidation, three properties of the Worksheet object play an important role.

Table. Properties of the Worksheet object used in data consolidation

Property	Description
ConsolidationOptions	Returns a three-element array. The first element indicates whether the consolidation is based on column headers. The second element indicates whether it is based on row headers of the consolidated ranges. The third element indicates whether the consolidated table is linked to the source tables.
ConsolidationFunction	Returns an XlConsolidationFunction constant that identifies the function on which the consolidation table is built.
ConsolidationSources	Returns an array of references to the ranges on which the consolidation table on the worksheet was built. If there is no such table on the worksheet, this property returns the value Empty.

 

Consolidation by position is performed when it is planned to combine data located in the same cells of different ranges. Consolidation by category is performed when there are several ranges and the goal is to combine these data by rows or columns with identical labels.

Along with consolidation, it is also useful to apply outlining, which can be created automatically. Importantly, the worksheets intended for consolidation do not necessarily have to share the same structure.

Let us describe the process of data consolidation using the example of creating a consolidated table of expenses for the company Alliance LLC for the reporting period from January to March.

• Make sure that all ranges of the data to be consolidated are presented in list format.
  • If consolidation is performed by position, ensure that the layouts of all ranges match.
  • If consolidation is performed by category, ensure that the column or row labels to be combined are identical (case-sensitive).

So, check that your workbook contains three sheets — January, February, March — with the data in the format.

In addition, the workbook must also contain a Totals sheet, where the resulting table after consolidation will be placed.

• Select the top-left cell of the range where the consolidated data should be placed. In our case, select cell A2 of the Totals worksheet.

Select the Consolidate command located in the Data Tools group on the Data tab of the ribbon. The Consolidate dialog box will appear.

• From the Function drop-down list, choose the so-called summary function. This function defines the type of calculation performed when combining the data in the consolidation table. The following functions are available: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev (biased), StdDev (unbiased), Var (biased), Var (unbiased).
  In this case, choose Sum.
• Click in the Reference field, open the sheet containing the first data range for consolidation, enter the reference to this range (in this case January!$A$2:$E$8), and click Add. As a result, the reference to the range will be added to the All references list. Repeat this step for all ranges to be consolidated (in this case, February!$A$2:$E$8 and March!$A$2:$E$8).
  • If the consolidation table should be updated automatically whenever the source data changes, and later there will be no need to change or add data ranges, select the checkbox Create links to source data (this is what we should do in this case).
  • If consolidation is performed by position, leave all fields in the Use labels in group empty. In MS Excel, the labels of source rows and columns are not copied into the consolidated data. If you need labels in the consolidated data, copy them manually. In our case, this checkbox is not selected.
  • If consolidation is performed by category, in the Use labels in group select the checkboxes corresponding to the location of labels in the source ranges: top row, left column, or both. Any labels not matching those in other source areas will appear in separate rows or columns in the consolidated data. In our case, this checkbox is selected.
• Click OK.

As a result, a consolidated table will be created, shown :