Understanding Arrays

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Arrays make it possible for you to refer to a series of variables by the same name and to use a number (an index) to tell them apart. This helps you create smaller and simpler code in many situations, because you can set up loops that deal efficiently with any number of cases by using the index number. Arrays are useful when you must store a number of values of the same type, but you do not know how many, or you do not want to create individual variables to store them all.

For example, suppose you must store a numeric value for every day of the year. You could declare 365 separate numeric variables, but that would be a lot of work. Instead, you can create an array to store all the data in one variable. The array itself is a single variable with multiple elements; each element can contain one piece of data.

You can use loops, together with a couple of special functions for working with arrays, to assign values to or retrieve values from the various elements of an array.

In This Section

• Creating Arrays
  Understand how to create two types of arrays in Microsoft® Visual Basic® for Applications (VBA) — fixed-size arrays and dynamic arrays.
• Arrays and Variants
  Learn how a Variant variable can store an array.
• Assigning One Array to Another
  Assign one array to another if two dynamic arrays have the same data type.
• Returning an Array from a Function
  Call a procedure that returns an array and assign it to another array.
• Passing an Array to a Procedure
  Declare an array in one procedure, and then pass that array to another procedure to be modified.
• Sorting Arrays
  Understand how to sort an array, which is an iterative process that requires a complex algorithm.
• Using the Filter Function to Search String Arrays
  Search a string array if you simply must know whether an item exists in the array by using the Filter function.
• Using a Binary Search Function to Search Numeric Arrays
  Learn how the binary-search algorithm performs efficient searching on a sorted array — whether numeric or string.
• Searching a Dictionary
  Use object programming constructs, such as For Each…Next and With…End With statements, to work with the Dictionary object.

Related Sections

• Getting the Most Out of Visual Basic for Applications
  Write code that is fast, efficient, easy to read and maintain, and, if possible, reusable with a solid working knowledge of Microsoft® Visual Basic® for Applications (VBA) — what features the language includes and what you can do with it.
• Working with Strings
  Understand how to get information from strings.
• Working with Numbers
  Learn how to use numeric values and data types in Microsoft® Visual Basic® for Applications (VBA).
• Working with Dates and Times
  Manipulate date values in Microsoft® Visual Basic® for Applications (VBA), and understand how VBA stores date values internally.
• Working with Files
  Understand the Scripting Runtime object library, and work with drives, folders, and files as objects.
• Tips for Defining Procedures in VBA
  Define a Function or Sub procedure, and use the options available to you to make your code more extensible or more flexible.
• Optimizing VBA Code
  Understand how to streamline your Microsoft® Visual Basic® for Applications (VBA) code to streamline your memory requirements.