FastTips for Microsoft Excel 4.0: Macros Q&A

SUMMARY

Questions and Answers: Macros

1. Q. How do 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 command.
   
   To move one column to the right of the current location, use the following command:
   
   =SELECT("RC[1]")
   
   To move one row down from the current location, use the following command:
   
   =SELECT("R[1]C")
   
   You can combine these two commands as well. To move three columns to the right and up five rows, use the following command:
   
   =SELECT("R[-5]C[3]")
   
   Using the square brackets tells Excel to move relative to the current location. In the first two examples, when there is no number following the row or column, Excel will stay in the same column or row that the active cell was in.



2. Q. How can I get Excel to repeat the same set of commands in my macro?
   
   A. Excel 4.0 has many ways of creating loops within a macro. The two most common methods of creating loops are very similar to methods used in programming languages such as Basic and Pascal.
   
   

   Method 1

   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 commands within the loop. To create a FOR-NEXT loop, enter the FOR formula in one cell, enter all the commands you want to carry out below it, and at the bottom of your column of commands, enter the NEXT statement. See pages 154-155 of the "Microsoft Excel Function Reference" for a full explanation of the FOR-NEXT loop, as well as a small example.
   
   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 commands on each cell in a range. See page 155 of the "Microsoft Excel Function Reference" for more information.
   
   

   Method 2

   The second method is called a WHILE loop. This function is used mainly when you want to carry out a series of commands until some condition becomes true or false. To create a WHILE loop, enter the While command in one cell, enter the steps of the loops below it, and finally, end the loop with a NEXT command. See page 455 of the "Microsoft Excel Function Reference" for a full explanation of the WHILE command, and a small example.



3. Q. How can I get more information on calling my own routines, which I have written in C or Pascal, from Excel?
   
   A. Microsoft Excel for the Macintosh version 4.0 supports the calling of external code resources (referred to as dynamic-link libraries in the Windows environment). These code resources can be written in C, Pascal, or with any compiler that supports the compilation of code resources. Some of the packages on the Macintosh that currently support this include: Think C and Think Pascal, both from Symantec Corporation, as well as Macintosh Programmer's Workshop (MPW) C, MPW Pascal, and MPW Assembler, from Apple Corporation.
   
   For information on using code resources and developing your own, see the Appendix in the "Microsoft Excel Function Reference" or call the Microsoft Developer Services Team and obtain a copy of the Microsoft Excel 4.0 Software Development Kit. You can reach the Developer Services Team by calling (800) 227-4679.

Additional query words: noupd

Keywords          : 
Version           : 
Platform          : 
Issue type        : 

Last Reviewed: March 24, 1999