Mastering Named Ranges, Arrays and VBA in Excel

1. Section 1 Introduction

There are 5 sections in 'Mastering Named Ranges, Arrays and VBA in Excel course':

• Introduction

• VBA

• Arrays

• Named Ranges and

• Close

In 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 Functions

In 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 Setup

You 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 VBA

In 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 VBA

VBA 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 Functions

In 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 Addins

We 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 Addins

Once an addin is created you can apply it to different workbooks.

2.5 Lecture 8 Simple Conversion Function Addin

In 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 Case

In 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 Programming

You 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 Globals

When 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 Arrays

In 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 Spooling

Spooling 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 Functions

Thus 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 Example

You 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 Ranges

In 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 Ranges

Named 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 VBA

VBA 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 Ranges

Named 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 Close

In 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 Remarks

This course has been introductory only but it shows the advantages of linking your arrays, VBA and Named Ranges.