XL4: How to Manipulate Array Variables Using a Macro

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

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 engineers 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()

   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

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

   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

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

   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

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

   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
   

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