Swapping Two Numbers

Although this Tip pertains to swapping values between two VBA variables, you might also be interested in swapping two worksheet cells. My freely available SwapTwoCells macro will swap the cells in Selection using Copy/Paste with Paste options and support for Undo plus Redo/Repeat. I use it with shortcut key Ctrl+Shift+S. See My Excel Toolbox: https://sites.google.com/view/MyExcelToolbox/

I don't know if this will help anyone. Back in the dim and distant past I worked as an electronic technician, and often worked with digital circuits, including IC's containing various logic circuits, including XOR gates.

The term XOR is a contraction of Exlusive OR

The truth table for the XOR function is as follows (A & B are inputs, O/P is the resulting output).

A B = O/P

0 0 0
1 0 1
0 1 1
1 1 0

In words, the output is 1 (TRUE) if either of the the inputs is 1 (TRUE), but only one of the imputs is 1(TRUE).. This differes from the simple OR gate where the output is 1 (TRUE) if either or BOTH inputs are 1 (TRUE)

In my day, when performed with multiple bit words, the logic operation was done on a bit-wise manner.

Hi Peter,
So it seems that in a ……_
=Number1 Xor Number2
_........ operation, this happens:
_ First the two “normal” ( decimal ) numbers are converted to their Binary ( 0 , 1 type) representation.
_ Then some specific Boolean logic is applied to those binary numbers( as shown at the link you gave ). That logic returns a different binary output.
_ That binary output is then converted back to a normal , ( decimal ) , number

It seems that the result of that logic just happens to do a swap on numbers when used as Rick showed: The Xor is a specific “Boolean Binary Computer thingy” ….. if you give it decimal numbers than, rather than crashing, it tries to convert them to their binary equivalent, then does the binary Boolean logic on that. I am not sure why it then decides to convert its answer back to a decimal. Maybe that is specific to the VB version of the Xor

I don’t feel too comfortable with the Xor thing, I am not sure why. In any case I usually sort and swap things that are a mixture of text and numbers, so it is of less use to me as it seems only to work on numbers
I feel comfortable for now with the use of a temporary variable way.

But another interesting thing to know about…
:)

Xor Operator
Microsoft doc on Xor Operator is @
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/xor-operator
There is also the Xand Operator. However I remember reading somewhere that the are not often used because both varaibles are always evaluated wherther or not that the second variable is necessary..
Anyway I ran a few more test with the previus macro and realised that the first calc for a is either A + B or B - A. Here is a list of a few swaps.

Swap A with B
A= 4 B= 56
a= 60 'A + B
B= 4 'B= A
A= 56 'A= a - B

Swap A with B
A= 12 B= 13
a= 1 'B - A
B= 12 'B = A
A= 13 'A = a + b

Swap A with B
A= 10 B= 110
A= 100 'a= B - A
B= 10 'B- a
A= 110 'A = B + a

Swap A with B
A= 64 B= 23
a= 87 'a = A + B
B= 64 'B = A
A= 23 'A = a - B

Alan and Willy

I tried my own version but still can't quite see the logic

Sub test3()
Dim a, b
Set a = [d3]
Set b = [E3]
Debug.Print "Swap A with B"
Debug.Print "A= " & a & vbTab & "B= " & b
a.Value = a Xor b
Debug.Print "A= " & a
b.Value = a Xor b
Debug.Print "B= " & b
a.Value = a Xor b
Debug.Print "A= " & a
End Sub

Results
Swap A with B
A= 67 B= 4
A= 71
B= 67
A= 4

Mind you as Rick said, it is easy to remember

Thanks Willy.
I see from what you did that it works. I am still puzzled how. I expect it is just to do with basic Computer logic that I never learnt ( yet ).
:)

@Alan
I also am puzzled by this elegant solution. So I have been experimenting a bit and although I don't fully understand the working, playing with the following macro is quite interesting.

Sub test()
A = 1
B = 2
A = A Xor B
Debug.Print A & "|" & B
B = A Xor B
Debug.Print A & "|" & B
A = A Xor B
Debug.Print A & "|" & B
End Sub

Result:
3|2
3|1
2|1

I also discovered that you can use XOR for rounding a decimal number:

Sub test2()
A = 1.49
'A = 1.5
Debug.Print A Xor 0
End Sub

Result:
A = 1.49 -> 1
A = 1.5 ->2

Have fun :-)

Thanks Michael,
Rick Rothstein has also mentioned that Xor solution over at Allen’s Excel Tip page
h t t p s : / / excel.tips.net/T002525_Swapping_Two_Numbers
I can’t use it yet because I only use things I understand. …_
_... I haven’t found an explanation on the internet simple enough for a computer idiot like me to understand yet :(
Alan Elston

There's a very cool algorithm for swapping binary numbers called the XOR Swap which needs no intermediate storage area. Since all Excel values are stored as binary numbers, it would seem you could use this algorithm somehow. Wkipedia has a good article on it, but in summary, if you have two variables X and Y, then to swap them:

X := X XOR Y
Y := Y XOR X
X := X XOR Y

... and Viola!

Thomas Papavasiliou

Thanks Thomas, I had realized that in case of swapping rows and columns didn't work. Here is my solution, at least the loop is only used in that case.

Sub swapRanges()
Dim x As Variant, y As Variant, i As Long
Dim rng1 As Range, rng2 As Range, nr
If Selection.Areas.Count <> 2 Then
Exit Sub
Else
Set rng1 = Range(Selection.Areas(1).Address)
Set rng2 = Range(Selection.Areas(2).Address)
End If

If rng1.Cells.Count <> rng2.Cells.Count Then
Exit Sub
End If
If rng1.Rows.Count = rng2.Columns.Count Then
nr = rng1.Rows.Count
ReDim x(1 To nr)
ReDim y(1 To nr)

For i = 1 To nr
x(i) = rng1(i)
y(i) = rng2(i)
Next

For i = 1 To nr
rng2(i) = x(i)
rng1(i) = y(i)
Next

Else
x = [rng1]
rng1.Value = rng2.Value
rng2 = x
End If
End Sub

To Peter Atherton

Thanks for your elegant approach as stated in "Sub swapRanges2()".

I dare to propose another test to avoid cases that have the same number of cells but a different number of rows and columns as for example 4 rows by 3 columns and 3 rows by 4 columns.
As we all know, macro results cannot be undone and if macro runs in such a case, we get some #NA errors.
I think a single test for identical rows (or columns), preceding the test for the identical number of cells, will avoid such potential errors.

On further thoughts, there is no need to have two arrays, one will do; just like a sort algotithm.

Sub swapRanges2()
Dim x As Variant
Dim rng1 As Range, rng2 As Range
If Selection.Areas.Count <> 2 Then
Exit Sub
Else
Set rng1 = Range(Selection.Areas(1).Address)
Set rng2 = Range(Selection.Areas(2).Address)
End If

If rng1.Cells.Count = rng2.Cells.Count Then
x = [rng1]
rng1.Value = rng2.Value
rng2 = x
End If
End Sub

Jim

I liked your macro and decided to try my own. The following creates ranges for both areas and reads the ranges into arrays. The arrays are copied into each others range. So no looping.

Sub swapRanges()
Dim x As Variant, y As Variant
Dim rng1 As Range, rng2 As Range

If Selection.Areas.Count <> 2 Then
Exit Sub
Else
Set rng1 = Range(Selection.Areas(1).Address)
Set rng2 = Range(Selection.Areas(2).Address)
End If

If rng1.Cells.Count = rng2.Cells.Count Then
x = [rng1]
y = [rng2]
End If
rng1 = y
rng2 = x
End Sub

DISLAIMER: I cannot find the original source for the code below, so I cannot give proper credit to the originator.

Quite a few years I decided there must be a way to swap two cells and found several methods similar to that in this article. One day I stumbled across the VBA code below which allows me to swap two ranges of cells, as long as they are the same configuration of cells. I was using this macro so often (and still do to this day) that I placed an icon for it in the QAT.

Sub SwapRange()
Dim arr1()
Dim arr2()
Dim Counter As Long
Dim CellCount As Long

If Selection.Areas.Count <> 2 Then
MsgBox "You must have two areas selected", vbCritical, "Aborting"
Exit Sub
ElseIf Selection.Areas(1).Cells.Count <> Selection.Areas(2).Cells.Count Then
MsgBox "Your selections must have the same number of cells", vbCritical, "Aborting"
Exit Sub
End If

CellCount = Selection.Areas(1).Cells.Count
ReDim arr1(1 To CellCount)
ReDim arr2(1 To CellCount)

For Counter = 1 To CellCount
arr1(Counter) = Selection.Areas(1).Cells(Counter)
arr2(Counter) = Selection.Areas(2).Cells(Counter)
Next

For Counter = 1 To CellCount
Selection.Areas(1).Cells(Counter) = arr2(Counter)
Selection.Areas(2).Cells(Counter) = arr1(Counter)
Next

End Sub

Thanks for the feedback, Ron. I made that change.

-Allen

Hey Allen:
I like the abstracts you use in the newsletter.

In the newsletter there is a minor typo in the abstract. It says:
<snip>
want to exchange the values held by two macros.
</snip>

I'm sure you meant to say
<snip>
want to exchange the values held by two VARIABLES.
</snip>

PS: I liked it better when you used to include those abstracts in the articles also. They provide a good intro.