What you'll learn
You will be able to link 3 skills: VBA, Named Ranges and Arrays.
By using VBA, Named Ranges and Arrays together you can create workbooks that are logical, structured and easy to understand.
You will use an approach that reduces Excel workbook error.
You will gain a strategic way to use Excel.
This is not a detailed VBA course, it focuses only on using VBA to create array functions.
Requirements
This course is part of a series which focuses on strategic use of Excel which the instructor calls 'Excel Engineering'. The participants should be experienced in using formulas, templates, Named Ranges and formatting. The course is unsuitable for a beginner. The course itself is prerequisite to future courses which will provide high level applications.
Prerequisites to this course are the following Udemy courses or equivalent knowledge: 1. 'Formatting an Excel Workbook: practical skills' 2. 'Effective Use of Named Ranges' 3. 'Effective use of Templates in Excel'.
The course is only applicable to Windows based systems, not Mac. The course is based on Microsoft Excel 365.
Description
1. Section 1 IntroductionThere are 5 sections in 'Mastering Named Ranges, Arrays and VBA in Excel course':IntroductionVBAArraysNamed Ranges andCloseIn the introduction you will gain an understanding of what to expect in 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.1.1 Lecture 1 Introduction In this lecture it is explained about the 5 sections in the course: the introduction, VBA, Name Ranges, arrays and the close. You will be introduced to the importance and advantages of linking VBA, Named Ranges and arrays.1.2 Lecture 2 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.1.3 Lecture 3 Simple Problem SetupYou will be to set up a basic function in an Excel spreadsheet in order to later apply VBA, arrays and Named Ranges.2. Section 2 VBAIn VBA section, you will learn the necessary fundamental information to later apply VBA to create array functions in Excel.2.1 Lecture 4 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.2.2 Lecture 5 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.2.3 Lecture 6 Creating AddinsWe don't want to repeat the same code in different workbooks instead we want to create an addin which can then be applied by different workbooks. In this lecture you will learn how to create an addin.2.4 Lecture 7 Applying AddinsOnce an addin is created you can apply it to different workbooks.2.5 Lecture 8 Simple Conversion Function AddinIn 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 addin, and then apply it to a workbook.2.6 Lecture 9 Complex Conversion Function Addin 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 addin and apply it to a workbook.2.7 Lecture 10 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.2.8 Lecture11 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.3. Section 3 ArraysIn the Arrays section you will understand what is meant by an array and how it compares to normal spooling of equations.3.1 Lecture 12 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.3.2 Lecture 13 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.3.3 Lecture 14 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.4. Section 4 Named RangesIn the Named Ranges section you will apply array functions to Named Ranges. Excel appears to be limited in its use of Named Ranges. Addins 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.4.1 Lecture 15 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.4.2 Lecture 16 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.4.3 Lecture 17 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 addin 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.5. Section 5 CloseIn the Close section 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.5.1 Lecture 18 Closing RemarksThis course has been introductory only but it shows the advantages of linking your arrays, VBA and Named Ranges.
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 Standard Deviation Functions
Lecture 3 Simple Problem Setup
Section 2: VBA
Lecture 4 Introduction to VBA
Lecture 5 VBA - Creating Functions
Lecture 6 Creating Addins
Lecture 7 Applying Addins
Lecture 8 Simple Conversion Function Addin
Lecture 9 Complex Conversion Function Addin using Select Case
Lecture 10 Object Oriented Programming
Lecture 11 Using Globals
Section 3: Arrays
Lecture 12 Spooling
Lecture 13 Creating VBA Array Functions
Lecture 14 Array Example
Section 4: Named Ranges
Lecture 15 Creating Named Ranges
Lecture 16 Using Named Ranges in VBA
Lecture 17 Copying and Grouping Named Ranges
Section 5: Close
Lecture 18 Closing Remarks
Lecture 19 Bonus Lecture
This course is primarily aimed at people who use formulas. In particular those users who would be considered qualitative analysts. Such as financial modellers, economists, engineers, accountants and scientists.,The problem that we use as a focus is simply setting up a least squares minimisation 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.