The problem scenario - VBA For Excel

Throughout much of this book, we will be referring to an Excel workbook called SALESMAN. XLS. This contains a number of worksheets including that displayed. The purpose of this worksheet is to maintain weekly sales data for each representative employed by a sales company.The top section contains administrative data such as the date the worksheet was written, and so on. The lower section contains data showing a column of representative names, along with the total sales to date, then the grey shaded region that contains weekly sales data for each of four weeks (per month), the next column contains the monthly total for these weekly sales, the next column shows the monthly bonus earned by the corresponding representative, and finally, an end of month sales column. This is defined as the sales to date plus the end of month sales.

The following named ranges have been set in the weeklysales worksheet.

Throughout this book, we will use many examples that are based upon the SALESMAN workbook. The file is available for downloading from the Made Simple website at. Alternatively, you can create this file yourself, but make sure that it is identical and includes the same named ranges.

Reasons for using macros

  • Executing repetitive tasks Many Excel business tasks require frequent repetition of some keyboard or mouse operations. For example, updating a company car expenses monthly spreadsheet might involve copying a range of cells to another, followed by clearing a range ready for next month s entry, followed by updating the total sales to date. A macro can be created to represent a task like this and then invoked whenever required; perhaps saving a great deal of time to the user who would otherwise have to retype the whole task over and over again whenever it is to be used.
  • The Scenario worksheet, showing the named ranges

    The Scenario worksheet, showing the named ranges

  • Input validation Data may require validation. For example, when a number that represents a person s age is entered, anything outside the range 0 to 120 would probably be unacceptable. A macro can be used to ensure that data entered into a range of cells is restricted within this specified range of values.
  • User interface control Many novice Excel users can feel overwhelmed by the complexity of using spreadsheets, even when just entering data. A macro can provide a friendlier interface that may include helpful dialog boxes. For example, a data entry clerk may work more efficiently by using a customized interface from a macro instead of entering data directly into the sheet.
  • Decision-making A spreadsheet is a matrix of cell locations that can contain values, formulae and relationships. The key point to note is that all of the elements in the matrix are changed automatically when one or more of the assumptions are changed. This facility allows a series of outcomes to be explored, providing answers to what-if questions that are an essential part of decision making. Macros can be created for this purpose.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

VBA For Excel Topics