Microsoft KB Archive/104998

{|
 * width="100%"|

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

-

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/support/supportnet/overview/overview.asp

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

Technology :