How To Develop Software In Excel

1. How To Develop Software In Excel Spreadsheets
2. Visual Basic
3. How To Develop Software In Excel Function
4. How To Develop Software In Excel Shortcut

If you like this video, here's my entire playlist of Excel tutorials: the basics of using Microsoft Excel, including the anatom. Entering the data. The columns in a database are called fields. You can add as many may be. This how-to is based on Microsoft Excel 2016, but the same steps should also apply to Excel 2010, Excel 2013 and Excel 2019. Depersonalize file properties. First create a new, or open an existing excel workbook. Save it in a directory where all the users that needs access has access to it. Open the file and go til File- Options.

Use Excel Scenarios to store several versions of the data in a worksheet. Download the sample file and watch the video or follow the written instructions

Introduction

With Scenarios in Excel, you can store multiple versions of data, in the same cells. For example, when preparing a budget, the Marketing and Finance departments may have different forecasts for sales. You can store each forecast as a Scenario, print them separately, or compare them side-by-side.

How To Develop Software In Excel Spreadsheets

Note: There is a limit of 32 changing cells in a Scenario

Visual Basic

To see the steps for setting up the Scenarios, and showing them, please watch this short video tutorial. The written instructions are below the video.

Set up the Excel Scenarios Worksheet

How To Develop Software In Excel Function

Although Excel scenarios can be complex, a simple example is used here.

1. Delete all sheets except Sheet1
2. Rename Sheet1 as Budget
3. On the Budget sheet, enter the Marketing budget, as shown below
4. Name the following cells (there are Naming instructions here: Name a Range). Naming the cells is not required, but will make it easier to manage the scenarios, and read the reports:
   • Name cell B1 as Dept
   • Name cell B3 as Sales
   • Name cell B4 as Expenses
   • Name cell B6 as Profit
5. In cell B6, enter the following formula:
   =Sales - Expenses

Create the First Excel Scenario

1. On the Ribbon's Data tab, click What If Analysis
2. Click Scenario Manager
3. In the Scenario Manager, click the Add button
4. Type name for the Scenario. For this example, use Marketing.
5. Press the Tab key, to move to the Changing cells box
6. On the worksheet, select cells B1
7. Hold the Ctrl key, and select cells B3:B4
   Note: There is a limit of 32 changing cells
8. Press the Tab key, to move to the Comment box
9. (optional) Enter a comment that describes the scenario.
10. Click the OK button
11. The Scenario Values dialog box opens, with a box for each changing cell.
12. You could modify these values, but in this example they contain the values currently on the worksheet, and don't need to be changed.
13. Click the OK button, to return to the Scenario Manager.
14. Click the Close button, to return to the worksheet

Create the Second Excel Scenario

1. To prepare for the Finance scenario, change the values in cells B1, B3 and B4, as shown below
2. On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager.
3. In the Scenario Manager, click the Add button
4. Type name for the second Scenario. For this example, use Finance.
5. The Changing cells box should show the previous selection -- B1,B3:B4 -- so leave that as is.
6. Press the Tab key, to move to the Comment box
7. (optional) Enter a comment that describes the scenario.
8. Click the OK button
9. The Scenario Values dialog box opens, with a box for each changing cell.
10. Click the OK button, to return to the Scenario Manager.
11. Click the Close button, to return to the worksheet

Show an Excel Scenario

Once you have created Scenarios, you can show them. In this example, the Finance scenario is currently visible. To change to a different scenario:

1. On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager.
2. In the list of Scenarios, select Marketing
3. Click the Show button
4. Click the Close button.

Add Scenario to Excel Ribbon

An easier way to switch between Scenarios, is to add a command to the Ribbon. Follow these steps, to add a Custom Group, and put the Scenario command in that group.

1. Right-click on the Ribbon, and click Customize the Ribbon
2. From the drop down list at the top left, select All Commands
3. In the list of commands that are currently on the Ribbon, click the plus sign for Data, then click Data Tools. That group contains the Scenario Manager, so we'll add the new group beside it.
4. Below the list, click the New Group button.
5. Then, click the Rename button, type Scenario as the name for the group, and click OK
6. At the left, in the list of All Commands, scroll down to find Scenarios
7. Make sure that the new Scenario group is still selected in the list at the right.
8. Click on Scenarios, then click Add, to put Scenarios in the Scenario group.
9. Click OK, to close the window, then click the Data tab, and select a Scenario to view.

Download the Sample File

Download the zipped sample Excel Scenario file

How To Develop Software In Excel Shortcut

There is another Excel Scenario example on the Contextures blog.

More Scenario Tutorials

An Excel add-in can be really useful when you have to run a macro often in different workbooks.

For example, suppose you want to highlight all the cells that have an error in it, you can easily create an Excel add-in that will highlight errors with a click of a button.

Something as shown below (the macro has been added to the Quick Access Toolbar to run it with a single click):

Similarly, you may want to create a custom Excel function and use it in all the Excel workbooks, instead of copy pasting the code again and again.

If you're interested in learning VBA the easy way, check out my Online Excel VBA Training.

Creating an Excel Add-in

In this tutorial, you'll learn how to create an Excel add-in. There are three steps to create an add-in and make it available in the QAT.

• Write/Record the code in a module.
• Save as an Excel Add-in.
• Add the macro to the Quick Access Toolbar.

Write/Record the Code in a Module

In this example, we will use a simple code to highlight all the cells that have error values:

If you are writing code (or copy-pasting it from somewhere), here are steps:

• Open an Excel Workbook.
• Press Alt + F11 to open the VB Editor Window.
• In the VB Editor, you would see the workbook objects listed in the project explorer. If you can't see that, go to View –> Project Explorer.
• Right-click on any of the objects in the workbook. Go to Insert option and click on ‘Module'. This will insert a module object.
• Double-click on the module and enter the above code (copy-paste it).
• Press Alt+F11 to go back to the Excel Worksheet.

Note: If you are recording a macro, Excel automatically takes care of inserting a module and putting the code in it.

Now let's go ahead and create an add-in out of this code.

Save and Install the Add-in

Follow the below steps when you are in the workbook where you have inserted the code.

• Click the File tab.
• Click on ‘Save As'.
• In the Save As dialogue box, change the ‘Save as' type to .xlam. The name you assign to the file would be the name of your add-in. In this example, the file is saved with the name Highlight Errors.
  • You'll notice that the path of the file where it gets saved automatically changes. You can use the default one or change it if you want.
• Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins.
• In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.

Now the add-in has been activated.

You may not see any tab or option appear in the ribbon, but the add-in gets activated at this stage and the code is available to be used now.

Can i screenshot on my macbook pro. The next step is to add the macro to the Quick Access Toolbar so that you can run the macro with a single click.

Note: If you are creating an add-in that has a custom function, then you don't need to go to step 3. By the end of step 2, you'll have the function available in all the workbook. Step 3 is for such codes, where you want something to happen when you run the code (such as highlight cells with errors).

Save and Install the Add-in

To do this:

• Right-click on any of the ribbon tabs and select Customize Quick Access Toolbar.
• In the Excel Options dialog box, Select Macros from the Choose commands from the drop-down. You'll notice that the macro ‘HighlightErrors' is listed there.
• Click on the ‘HighlightErrors' Macro and click on Add. This will add the macro to the list on the right.
• Click OK. This will add the macro to the Quick Access Toolbar.

Now to run this code in any workbook, select the dataset and click on the macro icon in the QAT.

This will highlight all the cells with errors in red color. You can also use this macro in any workbook since you have enabled the add-in.

Caution: The changes done by the macro can't be undone using Control + Z.

You can also create custom functions and then save it as an Excel add-in. Now, when you enable the add-in, the custom functions would be available in all your Excel workbooks.

You May Also Like the Following Excel Tutorials:

• Working with Cells and Ranges in Excel VBA.
• Working with Worksheets in VBA.
• Working with Workbooks in VBA.
• Using Loops in Excel VBA.
• Using IF Then Else Statement in Excel VBA.
• How to Create and Use Personal Macro Workbook in Excel.
• Useful Excel Macro Code Examples.

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster