Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Swapping Two Numbers.
If you do any serious macro programming, there will eventually come a time when you want to swap the values in two numeric variables. In some versions of BASIC, there are commands that handle this. VBA leave you to our own devices, however. The following technique should do the trick for most people:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
TempNum = MyNum1
MyNum1 = MyNum2
MyNum2 = TempNum
When completed, the values in MyNum1 and MyNum2 have been swapped, and TempNum doesn't matter since it was intended (by this technique) as a temporary variable anyway.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2525) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Swapping Two Numbers.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
A great way to customize Excel is to add your macros to a toolbar. That way you can run them quickly and easily.
Discover MoreIf you need to find out how many columns are set to be a specific width, you'll need a macro to help determine the info. ...
Discover MoreThe security features built into Excel 2002 and 2003 allow you to digitally sign your macros so that users can rest ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-09-26 10:49:20
Rick Rothstein
Another method without using a helper variable...
A = A Xor B
B = A Xor B
A = A Xor B
Notice the expression to the right of the equal sign is the same for each of the three steps making it easier to remember :-)
2014-02-23 19:11:29
Bill Mallloy
The solution offered by Ioannis works only for numbers, of course. The first tip is simple and straightforward. Most code can afford an extra variable in order to provide some simplicity. KISS.
2014-02-23 07:35:58
Michael (Micky) Avidan
Another interesting approach (without the helper variable:
MyNum1 = MyNum1 & "," & MyNum2
MyNum2 = Split(MyNum1, ",")(0)
MyNum1 = Split(MyNum1, ",")(1)
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
2014-02-22 13:30:16
Ioannis Nikolopoulos
A solution without the help of another variable:
MyNum1=MyNum1+MyNum2
MyNum2=Mynum1-MyNum2
MyNum1=MyNum1-MyNum2
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments