XL4: How to Manipulate Array Variables Using a Macro
ID: Q104998
|
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