Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/104998

From BetaArchive Wiki

XL4: How to Manipulate Array Variables Using a Macro

The information in this article applies to:

  • Microsoft Excel for Windows, versions 4.0, 4.0a
  • Microsoft Excel for the Macintosh, version 4.0


Microsoft Excel has the ability to create and manipulate arrays. You can use array variables to return a series of values in a defined name, a list of files, multiple responses to a dialog box, and other values.

The XLM macro language in Microsoft Excel does not use a classical programming array structure; that is, individual elements of the array cannot be manipulated in the form array(x)=15 where x is a position within the array. A macro routine must be designed to loop through the array and rebuild it in the process of making a change.

NOTE: It is not necessary to use any of the techniques shown below if you are using Visual Basic for Applications.


Below are five macro examples demonstrating how to manipulate elements in a one-dimensional defined array.

NOTE: All elements in the defined names (arrays) referenced below need to be of the same type (number or text); they must either all be text or all be numbers for the macros to work properly. In addition, all defined names are assumed to be defined on the macro sheet. Most have to be previously defined in the Define Name dialog box (from the Formula menu, choose Define Name).

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

Macro 1: Creating a Horizontal Array from a Defined Range of Cell Values

   A1: =SET.NAME("array","{")
   A2: =FOR("counter",1,ROWS(elements))
   A3: =SET.NAME("array",array&INDEX(elements,counter,1))
   A4: =IF(counter<>ROWS(elements))
   A5: =SET.NAME("array",array&",")
   A6: =END.IF()
   A7: =NEXT()
   A8: =SET.NAME("array",EVALUATE(array&"}"))
   A9: =RETURN() 

Explanation of macro:

   A1: Begin defined name "array" with "{"
   A2: Start FOR loop from 1 through number of items in named range
   A3: Concatenate current item into temporary string
   A4: Check to see if last item has been reached
   A5: If not last item, concatenate a comma into "array"
   A6: End IF
   A7: Go to next iteration of loop
   A8: Concatenate closing brace and evaluate string as an array
   A9: End macro 

The example macro above creates an array from a vertical range of cells defined as "elements" by looping through a process of concatenation and separating each value in the string by a comma--this process designates a horizontal array. The EVALUATE() function converts the resulting text string into an array. Note that to create a vertical array instead of a horizontal array, you can change the comma in quotation marks in cell A5 to a semicolon. The defined name "Elements" is assumed to be defined as a single column of cells. For example, the range A1:A5.

NOTE: If the elements of the array are text items, each item must be enclosed in quotation marks, as in {"a","b","c"}. This can be accomplished by modifying A3 above as follows:


Macro 2: Writing Values in a Horizontal Array to a Vertical Range of Cells

   A1: output=B1
   A2: =SELECT(output)
   A3: =FOR("counter",1,COLUMNS(array))
   A4: =FORMULA(INDEX(array,1,counter))
   A5: =SELECT("r[1]c")
   A6: =NEXT()
   A7: =RETURN() 

Explanation of macro:

   A1: Define a starting cell (in this case B1 on the macro sheet)
   A2: Select the starting cell
   A3: Start FOR loop from 1 through number of items in defined name
   A4: Write the current element of the array into the active cell
   A5: Select one cell down from the active cell
   A6: Go to next iteration of the loop
   A7: End macro 

This macro selects a starting cell (B1 in the example) and, using a FORMULA() statement, writes the values of the array into a column.

By using macros 1 and 2 together, values within an array can easily be changed by writing the values out to a cell range, changing the cell values, and rebuilding the array from the new values.

Macro 3: Appending an Element to an Existing Horizontal Array

   A1: =SET.NAME("temp","{")
   A2: =FOR("counter",1,COLUMNS(array))
   A3: =SET.NAME("temp",temp&INDEX(array,1,counter))
   A4: =SET.NAME("temp",temp&",")
   A5: =NEXT()
   A6: =SET.NAME("array",EVALUATE(temp&newelement&"}"))
   A7: =RETURN() 

Explanation of macro:

   A1: Begin temporary string with "{"
   A2: Start FOR loop from 1 through number of items in defined name
   A3: Concatenate current element into temporary string
   A4: Concatenate a comma between elements in temporary string
   A5: Go to next iteration of loop
   A6: Concatenate defined name "newelement" and closing brace, and
       convert string into "array"
   A7: End macro 

This process is similar to Macro 1 above, but uses a temporary string to rebuild the original array and append the value of "newelement". If the original array equals {11,22,33,44,55}, the new value of array would be {11,22,33,44,55,88}. The same note regarding text applies; both existing elements and the new element must be enclosed in quotation marks.

Macro 4: Deleting the nth Element from a Horizontal Array

   A1:  remove=3
   A2:  =SET.NAME("temp","{")
   A3:  =FOR("counter",1,COLUMNS(array))
   A4:  =IF(counter=remove,NEXT())
   A5:  =SET.NAME("temp",temp&INDEX(array,1,counter))
   A6:  =IF(counter<>COLUMNS(array))
   A7:  =SET.NAME("temp",temp&",")
   A8:  =END.IF()
   A9:  =NEXT()
   A10: =SET.NAME("array",EVALUATE(temp&"}"))
   A11: =RETURN() 

Explanation of macro:

   A1:  Set defined name "remove" equal to 3
   A2:  Begin temporary string with "{"
   A3:  Start FOR loop from 1 through number of items in defined name
   A4:  If third element is current, skip further processing
   A5:  Concatenate current element into temporary string
   A6:  Check to see if last element has been reached
   A7:  If not last element, concatenate a comma into temporary string
   A8:  End IF
   A9:  Go to next iteration of loop
   A10: Concatenate closing brace and convert string into "array"
   A11: End macro 

This macro loops through the individual array elements, rebuilding the array in a defined name called "temp". The evaluate statement returns the resulting text string to the original array variable. If the original array were equal to {33,44,55,66} for example, the resulting array would equal {33,44,66}.

As noted above, in order to handle text within an array, the text must be enclosed quotation marks. An example would be array = {"a","b","c","d"}. In order to properly rebuild the array with these text values, replace A5 above with

   A5: =SET.NAME("temp",temp&""""&INDEX(array,1,counter)&""""). 

The result in this case would be array = {"a","b","d"}.

The following two changes in this macro will remove a matching element from an array

    A1: remove=44
    A4: =IF(INDEX(array,1,counter)=remove,NEXT())


    A1: Sets the defined name "remove" equal to 44
    A4: If current element is equal to 44, skip further processing 

In this example, if the original array = {11,22,33,44,55}, the resulting array = {11,22,33,55}.

Additional query words: 4.00a M_eXceL

Keywords : kbmacro kbprg kbprb
Version : WINDOWS:4.0,4.0a; MACINTOSH:4.0
Issue type : kbinfo
Technology :

Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.