====================================================================== Microsoft(R) Product Support Services Application Note (Text File) WE0607: MACROS QUESTIONS AND ANSWERS ====================================================================== Revision Date: 10/93 No Disk Included The following information applies to Microsoft Excel version 4.0 for Windows(TM). ----------------------------------------------------------------------- | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY | | ACCOMPANY THIS DOCUMENT (collectively referred to as an Application | | Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER | | EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED | | WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR | | PURPOSE. The user assumes the entire risk as to the accuracy and | | the use of this Application Note. This Application Note may be | | copied and distributed subject to the following conditions: 1) All | | text must be copied without modification and all pages must be | | included; 2) If software is included, all files on the disk(s) | | must be copied without modification (the MS-DOS(R) utility | | diskcopy is appropriate for this purpose); 3) All components of | | this Application Note must be distributed together; and 4) This | | Application Note may not be distributed for profit. | | | | Copyright (C) 1993 Microsoft Corporation. All Rights Reserved. | | Microsoft, Microsoft Press, MS-DOS, and QuickC are registered | | trademarks and Windows is a trademark of Microsoft Corporation. | | Borland and Turbo-Pascal are registered trademarks of Borland | | International, Inc. | |---------------------------------------------------------------------| VERSION 4.0 of Microsoft Excel for Windows includes the "Microsoft Excel Function Reference" and two user's guides. There are several chapters in the user's guides with information on creating Microsoft Excel macros. The "Microsoft Excel Function Reference" contains every Microsoft Excel function. If you want more information on creating, using, and writing Microsoft Excel macros, Microsoft Press(R) has published a book titled "The Complete Guide to Microsoft Excel Macros" by Charles Kyd and Chris Kinata. For more information on this book, contact a local book retailer, or call Microsoft Press at (800) 677-7377. 1. Q. How can I move the active cell relative to its current location in my worksheet? A. To move the active cell relative to its current location, it is easiest to use R1C1 notation with a SELECT function. To move one column to the right of the current location, use the following function: =select("rc[1]") To move one row down from the current location, use the following function: =select("r[1]c") You can combine these two functions as well. To move five rows up and three columns to the right, use the following function: =select("r[-5]c[3]") Using the brackets tells Microsoft Excel to move relative to the current location. In the first two examples, when there is no number following the row or column, Excel remains in the same column or row that the active cell was in. 2. Q. How can I get version 4.0 of Microsoft Excel for Windows to repeat the same set of functions in my macro? A. Microsoft Excel 4.0 offers several ways to create loops within a macro. The two most common methods of creating loops are very similar to the methods used in programming languages such as Basic and Pascal. The first method is called a FOR-NEXT loop. It is used mainly when you know how many times you want to carry out the functions within the loop. To create a FOR-NEXT loop, enter the FOR formula in one cell, enter all the functions you want to carry out below it, and at the bottom of your column of functions, enter the NEXT statement. For a full explanation of the FOR-NEXT loop, as well as a short example, please refer to pages 154-155 of the "Microsoft Excel Function Reference". A looping structure related to the FOR-NEXT loop is the FOR.CELL loop. This particular form of a FOR-NEXT loop is used when you want to carry out your series of functions on each cell in a range. For more information on this method, please refer to page 155 of the "Microsoft Excel Function Reference". The second method is called a WHILE loop. This function is used mainly when you want to carry out a series of functions until some condition becomes true or false. To create a WHILE loop, enter the WHILE function in one cell, enter the steps of the loops below it, and finally, end the loop with a NEXT function. For a full explanation of the WHILE function, and a short example, please refer to page 455 of the "Microsoft Excel Function Reference". 3. Q. How can I get more information on calling my own routines that I have written in C or Pascal from version 4.0 of Microsoft Excel for Windows? A. Microsoft Excel 4.0 supports the calling of dynamic-link libraries (DLLs). These libraries can be written in C, Pascal, or any compiler that supports the compilation of DLLs. Packages that currently support this functionality include Microsoft C, Microsoft QuickC(R) for Windows, Borland(R) C/C++, and Borland Turbo-Pascal(R) for Windows. For information on using DLLs and developing your own DLLs, please refer to the Appendix in the "Microsoft Excel Function Reference" or call Microsoft Developer Services to obtain a copy of the Microsoft Excel 4.0 Software Development Kit. You can reach Developer Services by calling (800) 227-4679.