9 thoughts on “Swapping Strings the Tricky Way”

1. sub M_snb()
sn=array("first","second")
debug.print sn(1),sn(0)
end sub

   or
sub M_snb()
sn=split("first|second")
debug.print sn(1),sn(0)
end sub

2. Hi Dick –

   “Exclusive Or” in VBA is Xor.

   
Sub TestOrs()
Dim A As Boolean, B As Boolean

   A = False: B = False
   Debug.Print A Or B; A Xor B

   A = False: B = True
   Debug.Print A Or B; A Xor B

   A = True: B = False
   Debug.Print A Or B; A Xor B

   A = True: B = True
   Debug.Print A Or B; A Xor B

   End Sub

   Results:

FalseFalse
TrueTrue
TrueTrue
TrueFalse

   … mrt

3. Another way to swap two strings without using any other variables…

   sOne = “Dick”
   sTwo = “Kusleika”

   sOne = sOne & sTwo
   sTwo = Left(sOne, Len(sOne) – Len(sTwo))
   sOne = Mid(sOne, Len(sTwo) + 1)

   As for the “swap integers” method that served as the seed for your blog article, you do not need to use logical operators to do it, simple addition and subtraction is sufficient…

   X = X + Y
   Y = X – Y
   X = X – Y

   And, as long as we are talking about “geeky” ways to do things, how about a “geeky” way to calculate the minimum and maximum between two numbers…

   Min = (X + Y – Abs(X – Y)) / 2

   Max = (X + Y + Abs(X – Y)) / 2

4. Exclusive OR, in excel formulae, I was enlighted to read a while back, is quite easy, although there is no XOR formula.
   “=A1B1” works quite nicely

5. Alex – the XOR() worksheet function has been introduced in Excel 2013.

6. @Alex,

   I think you included a “not equal” sign (less than sign followed by a greater than sign) between the A1 and B1 cell references which are not showing up in your message… I believe that is because this blog’s comment processor assumed them to be HTML tags with nothing between them.

   @Everyone,

   In my message above, I outline a method to swap two numbers using simple addition and subtraction… I forgot to mention that this method in not restricted to integer values the way the Xor method is.

7. In my first message in this thread, I posted two methods of swapping two variables without using any other intermediate variables… one for text strings only and one for numbers (integers or floating point) only… now here is a method that works with either numbers or text strings (the variables’ declarations can be used to control the outputted data type if desired):

   For Text Strings
   ————————————–
   sOne = “One
   sTwo = “Two”
   sOne = ” ” & sOne & ” ” & sTwo & ” ”
   sTwo = Trim(Replace(” ” & sOne & ” “, ” ” & sTwo & ” “, “”))
   sOne = Trim(Replace(” ” & sOne & ” “, ” ” & sTwo & ” “, “”))

   For Numbers
   ————————————-
   sOne = 1.234
   sTwo = 9.876
   sOne = ” ” & sOne & ” ” & sTwo & ” ”
   sTwo = Trim(Replace(” ” & sOne & ” “, ” ” & sTwo & ” “, “”))
   sOne = Trim(Replace(” ” & sOne & ” “, ” ” & sTwo & ” “, “”))

8. It always nice to read how Rick might do something but I’d use the vanilla:
   
Option Explicit

   Sub Swap(ByRef X, ByRef Y)
Dim Temp
If TypeOf X Is Object Then _
Set Temp = X: Set X = Y: Set Y = Temp _
Else _
Temp = X: X = Y: Y = Temp
End Sub
Sub testSwap()
Dim X, Y
X = 1: Y = 2: Swap X, Y: Debug.Print X, Y
X = 1.1: Y = 2E+300: Swap X, Y: Debug.Print X, Y
X = "a": Y = "b": Swap X, Y: Debug.Print X, Y
Set X = Sheets(1): Set Y = Sheets(2): Swap X, Y: Debug.Print X.Index, Y.Index
End Sub


9. @Tushar,

   I think you have read the opening paragraph to this thread to quickly…

   “Alfred Thompson posted recently on how to swap integers using only two variables.”

   Note: I tried to make the relavant text bold by putting the word “strong” inside of angle brackets. If that doesn’t work, then the relevant text will still be surrounded by them.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.