XL4: How to Manipulate Array Variables Using a Macro

ID: Q104998

The information in this article applies to:

SUMMARY

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.


MORE INFORMATION

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:

http://www.microsoft.com/supportnet/refguide/



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
   "elements"
   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: 


   =SET.NAME("array",array&""""&INDEX(elements,counter,1)&"""").


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
       "array"
   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
       "array"
   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
        "array"
   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())

where:

    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
Platform          : MACINTOSH WINDOWS 
Issue type        : kbinfo

Last Reviewed: May 17, 1999