Array formula. An array formula performs an operation on multiple values instead of a single value. The final result of an array formula can be either one item or an array of items, depending on how the formula is constructed. To work correctly, many array formulas need to be entered with control + shift + enter. An array is a series of data in a row, column, or a combination of rows and columns. You have probably used them before. B2:F2 is an array, also commonly referred to as a range of cells. An array formula performs calculations on the data in one or more arrays, returning either a single or multiple results.
What is an Array? An array is a memory location capable of storing more than one value.
The values must all be of the same data type. Let's say you want to store a list of your favourite beverages in a single variable, you can use an array to do that. By using an array, you can refer to the related values by the same name. You can use an index or subscript to tell them apart.
The individual values are referred as the elements of the array. They are contiguous from index 0 through the highest index value. This tutorial assumes you are using Microsoft Excel version 2013. The knowledge still applies to other versions of Microsoft Excel as well.
In this tutorial, you will learn-. What are Advantages of arrays? The following are some of the benefits offered by arrays.
Group logically related data together – let's say you want to store a list of students. You can use a single array variable that has separate locations for student categories i.e. Kinder garden, primary, secondary, high school, etc. Arrays make it easy to write maintainable code. For the same logically related data, it allows you to define a single variable, instead of defining more than one variable. Better performance – once an array has been defined, it is faster to retrieve, sort, and modify data. Types of arrays VBA supports two types of arrays namely;.
Static – These types of arrays have a fixed pre-determined number of elements that can be stored. One cannot change the size of the data type of a Static Array. These are useful when you want to work with known entities such as the number of days in a week, gender, etc.
For Example: Dim ArrayMonth(12) As String. Dynamic – These types of arrays do not have a fixed pre-determined number of elements that can be stored. These are useful when working with entities that you cannot predetermine the number. For Example: Dim ArrayMonth As Variant Syntax for declaring arrays Static arrays The syntax for declaring STATIC arrays is as follows: Dim arrayName (n) as datatype HERE, Code Action Dim arrayName (n) datatype. It declares an array variable called arrayName with a size of n and datatype.
Size refers to the number of elements that the array can store. Dynamic arrays The syntax for declaring DYNAMIC arrays is as follows: Dim arrayName as datatype ReDim arrayName(4) HERE, Code Action Dim arrayName datatype. It declares an array variable called arrayName without specifying the number of elements ReDim arrayName(4). It specifies the array size after the array has been defined.
Array Dimensions An array can be one dimension, two dimensions or multidimensional. One dimension: In this dimension, the array uses only one index.
For example, a number of people of each age. Two dimensions: In this dimension, the array uses two indexes. For example, a number of students in each class. It requires number of classes and student number in each class.
Multi-dimension: In this dimension, the array uses more than two indexes. For example, temperatures during the daytime. ( 30, 40, 20). VBA Array Demonstrated with Example We will create a simple application. This application populates an Excel sheet with data from an array variable. In this example, we are going to do following things.
Create a new Microsoft Excel workbook and save it as Excel Macro-Enabled Workbook (.xlsm). Add a command button to the workbook.
Set the name and caption properties of the command button. Write the code that populates the Excel sheet Let do this exercise step by step, Step 1 – Create a new workbook. Open Microsoft Excel. Save the new workbook as VBA Arrays.xlsm Step 2 – Add a command button Note: This section assumes you are familiar with the process of creating an interface in excel.
If you are not familiar, read the tutorial.
In my experience, most of the people who I train have never heard of – let alone use – Array Formulas and Functions in Excel. However, once I demonstrate how they work, they become “hooked” and want to learn more about these “Magic Formulas!” In this tutorial, I demonstrate, “step-by-step” how to use the FREQUENCY Function in Excel. Previous Tutorial on The Company Rocks Website If you, I used the same data set and a Pivot Table to Group these 500 plus Sample Records by Age Bracket. Using a Pivot Table, you can quickly “group” ages into brackets – without writing a single formula!
However, on the down side, you must use a consistent “step-value” for these groupings – in this case by 10 years. When you need more flexibility in organizing your Age Brackets, use the Frequency Function in Excel. How to Use the FREQUENCY Function in Excel. Create your “Bin Array.” In this example, enter – in ascending order – the ages that you want to find the “frequency of occurrences” between. Select the “Array of Cells” that will contain your FREQUENCY Function results. Write the FREQUENCY Function – 1st argument is the “Dataarray”. In this case, I created a Named Cell Range called “Age.” this is mu “Best Practice Tip!”.
The 2nd argument is the “Binarray.” Choose the vertical range of cells that you created in step 1 of this list. Make sure that the “size” of this Array matches the “size” of your Array Formula Selection. Complete the Array Function with the keyboard combination of “Ctrl + Shift + Enter.” This “CSE” combination is essential when entering all Array Formulas and Functions! My Secure Shopping Website I invite you to visit my secure shopping website – – where you can preview all of the training resources that I offer. If you want to learn more about formulas and function, I offer a great resource: Regardless of the version of Excel that you are currently using, you will pick up many great tips to improve your Excel skills!
Watch Tutorial in High Definition on my YouTube Channel – DannyRocksExcels. View My Tutorial Now on YouTube.