Overview

SECTION 1 INTRODUCTION TO EFFECTIVE USE OF NAMED RANGESExcel has a functionality called "Defined Names". In general Defined Names can have multiple uses. The main use is to give a name to a set of cells. This is called a Named Range; and is the focus of the course. Please note this course is only applicable to Windows based systems.Lecture 1 Introduction to CourseThis lecture provides an overview of the course, which focuses on the use of Named Ranges in Excel.To provide an overview of the benefits of using Named Ranges and to briefly describe what is covered in the course. In brief, the advantages of effective use of Named Ranges are:Applying formulae to a set of dataReduced errorImproved clarity of the workbookImproved automationImproved workbook navigationThese outcomes are referred to as the ‘listed advantages'.In brief there are two ways to use Excel (from a modelling viewpoint). The first way is to use A1 notation (or formula referencing). The second way is to use Named Ranges. The most common approach is A1 notation, and the second approach is to use A1 notation with only some Named Ranges. A dedicated Named Range approach is novel. You will understand the reasons for using such a unique approach.Lecture 2 Course StructureThis lecture provides an overview of what is being covered in the course. The course mainly consists of 2 parts:Part 1 Introduction to Named Ranges, Sections 2 - 5.Part 2 Advanced Named Ranges, Sections 6 - 10(With a stronger focus on using VBA add-ins)SECTION 2 PART 1 - INTRODUCTION TO NAMED RANGES - The Relevance of Modelling Concepts to Named Ranges.You will develop a sense of the mathematical concepts required to later define and use Named Ranges effectively.Lecture 3 Modelling ConceptsAlthough there are many courses that discuss Named Ranges. There are very few (if any) that consider how to use Named Ranges effectively. That is many Excel users are able to use Named Ranges, but very few can use them efficiently to achieve the listed advantages. Understanding how to use Named Ranges effectively commences by first considering modelling concepts. This lecture is conceptually mathematical but does not require you to use mathematics per say.The lecture simple explores concepts such as ensuring that mathematical models in a workbook are clearly defined, as well as associated inputs, outputs and parameters.You will develop a sense of the mathematical concepts required to later define and use Named Ranges effectively.SECTION 3 METHODS OF HOW TO USE NAMED RANGES EFFECTIVELYThe methods section includes the fundamental methods of how to use Named Ranges effectively.Skills you will learn include:Creating workbook-scoped Named RangesAvoiding error using Named RangesCreating worksheet-scoped Named RangesApplying Data validation using Named Ranges.Clearly there are two types of Named Ranges: workbook scope and worksheet-scope.The advantages of one type of scoped Named Range over another are also discussed.Lecture 4 Creating workbook-scoped Named RangesWorkbook-scoped Named Ranges can be easily applied throughout an entire workbook.For beginners using Named Ranges, creating workbook-scoped Named Range is the most common approach.In this lecture it is explained how to create a workbook Named Range using two approaches:Creating a Named Range using the Name BoxCreating a Named Range using the Name Manager.You will successfully create a workbook-scoped Name Ranges by using the Name Box and the Name Manager.Lecture 5 Avoiding error using Named RangesThere are many sources of error when using Excel. Perhaps the most common source of error is caused by inter-workbook linkage (where cells from one workbook are linked to another workbook).In this lecture we discuss how the problem is avoided using Named Ranges.You will learn:How to create inter-workbook linkage.How error is created by not using Named RangesHow to link data from different workbooks using Named RangesHow error is avoided by using Named RangesYou will identify how inter-workbook linkage error is avoided by using Named Ranges.Lecture 6 Creating worksheet Named RangesWorksheet Named Ranges have less ‘scope' than workbook Named Ranges. In this lecture we discuss what is meant by scope. Simply put, a worksheet-scoped Named Range is immediately seen only for a single worksheet.However even though it is only seen immediately in a worksheet it can still be applied to different worksheets. In this lecture we discuss the advantages and disadvantages of using worksheet Named Ranges compared to workbook Named Ranges. You will create a worksheet Named Range, and access the Named Range from different worksheets.Lecture 7 Data Validation using Named RangesData validation is a way of ensuring that cell inputs are restricted to specified values. This ensures consistency. i.e. If a data validation list includes ‘No' but not ‘false', it is much easier to later interrogate the results if the answers are consistent.There are many uses of Data Validation but here we focus on Lists.You will be taught how to create a data validation list with:unnamed cells,workbook -scoped Named Ranges,worksheet-scoped Named RangesHowever using worksheet Named Ranges for data validation in different worksheets is a little bit tricky and it may be necessary to create a ‘defined name' that corresponds to a worksheet-scoped Named Range.You will create data validation lists using Named Ranges.SECTION 4 PRACTICAL USE OF APPLYING NAMED RANGESIn this section we apply Named Ranges to a worksheet that uses A1 notation.We convert all equations to Named-ranged based equations.Lecture 8 Creating a flowchart for Named Range designThe workbook and worksheet for which the Named Ranges are to be applied are introduced.The particular worksheet is a template and is later to be copied.The next step in applying Named Ranges is to create a flowchart. This allows one to visually identify the cells that make up a Named Range.You will identify dataflow using a flowchart with a view to creating Named Ranges.Lecture 9 Creating and applying Named RangesUsing the flowchart design, Named Ranges are created. At this stage Named Ranges can have workbook-scope.The Named Ranges are then applied to the existing formulae.All formulae are converted from A1 notation to Named Range notation meaning that the worksheet is now: Easier to understandLess error proneMore automatedYou will learn to create and apply Named Ranges so that all existing formulae in the worksheet use Named Ranges and there is no A1 notation.Lecture 10 Copying Named RangesExcel is not specifically designed for copying Named Range within a worksheet. However if one copies a worksheet, then all Named Ranges (whether workbook or worksheet scope) are also copied.This means that if a worksheet is a template, then copying the worksheet allows all Named Ranges and equations to be copied.In this lecture we copy a worksheet and notice that all equations are copied correctly. You will therefore learn to copy a worksheet (that is a template) and by doing so also copy correctly the associated Named Ranges and equations.SECTION 5 CLOSE - PART 1You will be able to use arrays in different versions of Excel, summarise what you have covered in Part 1 of the course and revise the advantages of using Named Ranges.In this section we include 2 lectures:Using arrays for early versions of ExcelSummary of courseLecture 11 Using arrays for early versions of ExcelExcel has substantially improved over the years, particularly in the handling of arrays. Indeed arrays is now so effective many Excel users are unaware they are using arrays.An array is when an equation is applied to a set of data rather than individual cells, which are then autofilled to different cells. That is, the autofill is applied automatically by Excel rather than the user.In earlier versions of Excel array handling is a bit more manual with the user required to select the range of data when an array equation is to be applied.In this lecture we apply an array equation using early versions of Excel.(Here it appears that later versions of Excel are part of Office 365, whereas earlier versions are pre-Office 365).If a participant has a pre-Office 365 version then the objective is to apply an array equation.If a participant does not have Office 365, they are informed of what to do should they be required to use an early version.Lecture 12 SummaryIn this lecture you will be able to summarise what you have achieved in Part 1 of the course:How to create Named Ranges (both workbook and worksheet scope).How to use Named Ranges for Data ValidationHow to identify appropriate Named Ranges by considering a flowchart diagramHow to apply Named Ranges to a worksheet in order to make all formulae based on Named Ranges including how to use array equationsHow to copy Named Ranges via copying a worksheet.The result of those skills is that your workbooks are:easier to understand,less error-prone,and more automated.SECTION 6 INTRODUCTION TO PART 2 - ADVANCED NAMED RANGESThere are 5 sections in Part 2IntroductionVBAArraysNamed Ranges andCloseIn the introduction you will gain an understanding of what to expect in Part 2 of the course through an outline. The course links up VBA, Arrays and Named Ranges so that you will be able to use these tools effectively and synergistically. This is not a detailed VBA course, it focuses only on using VBA to create array functions. The problem that we use as a focus is simply setting up a least squares minimalization function for mineral processing however you do not need to be a mineral processor to do this course. We do not go into mineral processing in depth.Lecture 13 Part 2 IntroductionIn this lecture it is explained about the 5 sections in Part 2 of the course: the introduction, VBA, Named Ranges, Arrays and the close. You will be introduced to the importance and advantages of linking VBA, Named Ranges and Arrays.Lecture 14 Standard Deviation FunctionsIn this lecture we provide a simple function in which Named Ranges and arrays will later be applied using VBA. The explained problem is fundamental to mineral processing. It is a very simple problem that anyone with basic maths skills can understand. You will understand the formula that will later be used to explain VBA, Named Ranges and Arrays.Lecture 15 Simple Problem SetupYou will be able to set up a basic function in an Excel spreadsheet in order to later apply VBA, Arrays and Named Ranges.SECTION 7 VBAIn VBA section, you will learn the necessary fundamental information to later apply VBA to create array functions in Excel.Lecture 16 Introduction to VBAVBA is visual basic for applications. This is not a VBA course. You will identify the part of VBA to be used for linking arrays and Named Ranges.Lecture 17 VBA - Creating FunctionsIn this lecture you will apply a simple function (not using either Named Ranges or arrays) to the specified problem. You will learn how to create a simple function.Lecture 18 Creating Add-insWe don't want to repeat the same code in different workbooks instead we want to create an add-in which can then be applied by different workbooks. In this lecture you will learn how to create an add-in.Lecture 19 Applying Add-insOnce an add-in is created you can apply it to different workbooks.Lecture 20 Simple Conversion Function Add-inIn this lecture you will create a simple function to estimate the standard deviation based on the confidence of a measurement's accuracy. You will create the function and add it to an add-in, and then apply it to a workbook.Lecture 21 Complex Conversion Function Add-in using Select CaseIn this lecture we extend the code of the previous lecture based on more options for the confidence of a measured variable. Here we introduce the select Case method which is a more elegant approach than nested if statements. You will create the function, add it to the add-in and apply it to a workbook.Lecture 22 Object Oriented ProgrammingYou will develop an overall understanding of what object oriented programming is. In the context of VBA, you will gain a basic idea of what object oriented programming is. By getting an object we gain access to all methods, properties and events associated with that object. The most common object is a worksheet object. You will have opportunity to practice using objects.Lecture 23 Using GlobalsWhen we create VBA functions, we want them to be efficient. We don't want to repeat the same calculations over and over again. If we set up parameters as global variables they can be accessed once and then utilised repeatedly creating VBA error.SECTION 8 ARRAYSIn the Arrays section you will understand what is meant by an array and how it compares to normal spooling of equations.Lecture 24 SpoolingSpooling was incorporated in Excel 365. This made Arrays much easier to use. Spooling automatically determines the cells where an array function is to be applied.Lecture 25 Creating VBA Array FunctionsThus far, functions have been very simple. In an array function we want to be able to apply a function to a set of data rather than one cell at a time. This is why we use arrays. You will understand what is an array and why it is necessary to create VBA array functions.Lecture 26 Array ExampleYou will be able to create VBA functions that are applied to arrays. You will use an example of an array function applying it to the simple problems already specified.SECTION 9 NAMED RANGESYou will apply array functions to Named Ranges. Add-ins created by the instructor are used to show you how to do things like copy Named Ranges so they can be applied to new equations.(Excel appears to be limited in its use of Named Ranges).Lecture 27 Creating Named RangesNamed Ranges are useful if we want to avoid A1 notation so that the functions have more meaning and therefore are easier to understand. You will review how to create Named Ranges.Lecture 28 Using Named Ranges in VBAVBA isn't directly connected to Excel. That is, if we change the position of data in Excel and we refer to it in VBA it may cause an error. By using Named Ranges we can make the VBA code easier to understand and less error prone. In this lecture you will use Named Ranges in VBA code.Lecture 29 Copying and Grouping Named RangesNamed Ranges are applied to a set of data but suppose we want to copy that data onto the same worksheet to create a new set of data. We can do that but we can't simultaneously copy the Named Range. An add-in is provided by the instructor that you can use to copy Named Ranges within a worksheet.In normal Excel, if we copy an array function which is applied to A1 notation data it should apply correctly to the new copied data.But it won't do that if we use Named Ranges so again we have to come up with a strategy in order to apply any copied array function that uses Named Ranges to be applied to the new copied Named Ranges. You will learn an effective strategy using Find and Replace.SECTION 10 CLOSE - PART 2In the Close section Part 2 you will discover some of the ways in which the course material is applied to practical problems. The main focus is flowchart based process modelling.Lecture 30 Closing RemarksThis course has been introductory only but it shows the advantages of linking your arrays, VBA and Named Ranges.Lecture 31

Skills

Reviews