Answered by:
Excel VBA UDF Function XOR Operation.

Question
-
Hi there,
I need to perform XOR operation on two hex values in excel.
Value 1:-
0000198000010048
Value 2:-
04546E7FFFFEFFB7
Output:-
045477FFFFFFFFFF
Can somebody please help me to create a function in VBA to calculate this value performing XOR operation on value 1 and value 2. Any help would be appreciable. Thanks
- Edited by Maulik90 Monday, December 28, 2015 5:14 PM
Monday, December 28, 2015 5:13 PM
Answers
-
If your hex values are in the form of character strings of hexadecimal digits, here's a quickie function to XOR them and return the result as a hex string:
Function XorHexStrings(ByVal V1 As String, ByVal V2 As String, Optional PadRight As Boolean) As String ' XOR two hexadecimal strings, returning the result as a hexadecimal string. ' If one of the argument strings is shorter than the other, that string ' will be padded with zeros; by default, padded on the left, but the ' optional argument PadRight may be used to specify padding on the right. Dim L1 As Long ' length of first hex string Dim L2 As Long ' length of second hex string Dim D1 As String ' hex digit extracted from first string Dim D2 As String ' hex digit extracted from second string Dim R As String ' result string Dim I As Long ' digit iterator If V1 Like "*[!0-9A-F]*" Then Err.Raise 5 End If If V2 Like "*[!0-9A-F]*" Then Err.Raise 5 End If L1 = Len(V1) L2 = Len(V2) If L1 < L2 Then If PadRight Then V1 = V1 & String(L2 - L1, "0") Else V1 = String(L2 - L1, "0") & V1 End If L1 = L2 ElseIf L2 < L1 Then If PadRight Then V2 = V2 & String(L1 - L2, "0") Else V2 = String(L1 - L2, "0") & V2 End If L2 = L1 End If For I = 1 To L1 D1 = Mid$(V1, I, 1) D2 = Mid$(V2, I, 1) R = R & Hex(Val("&H" & D1) Xor Val("&H" & D2)) Next I XorHexStrings = R End Function
I make no promises as to performance, but it seems to work.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html- Proposed as answer by Fei XueMicrosoft employee Tuesday, December 29, 2015 5:56 AM
- Marked as answer by Fei XueMicrosoft employee Monday, January 4, 2016 6:19 AM
Monday, December 28, 2015 10:02 PM
All replies
-
You're posting in the forum for Microsoft Access, not Excel, but the VBA for this sort of thing should be the same. But what sort of values do you intend to XOR? Are they numeric values -- and if so, of what type and size? Strings of hexadecimal characters intended as representations of bit strings? Bit values represented as characters?
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.htmlMonday, December 28, 2015 8:52 PM -
If your hex values are in the form of character strings of hexadecimal digits, here's a quickie function to XOR them and return the result as a hex string:
Function XorHexStrings(ByVal V1 As String, ByVal V2 As String, Optional PadRight As Boolean) As String ' XOR two hexadecimal strings, returning the result as a hexadecimal string. ' If one of the argument strings is shorter than the other, that string ' will be padded with zeros; by default, padded on the left, but the ' optional argument PadRight may be used to specify padding on the right. Dim L1 As Long ' length of first hex string Dim L2 As Long ' length of second hex string Dim D1 As String ' hex digit extracted from first string Dim D2 As String ' hex digit extracted from second string Dim R As String ' result string Dim I As Long ' digit iterator If V1 Like "*[!0-9A-F]*" Then Err.Raise 5 End If If V2 Like "*[!0-9A-F]*" Then Err.Raise 5 End If L1 = Len(V1) L2 = Len(V2) If L1 < L2 Then If PadRight Then V1 = V1 & String(L2 - L1, "0") Else V1 = String(L2 - L1, "0") & V1 End If L1 = L2 ElseIf L2 < L1 Then If PadRight Then V2 = V2 & String(L1 - L2, "0") Else V2 = String(L1 - L2, "0") & V2 End If L2 = L1 End If For I = 1 To L1 D1 = Mid$(V1, I, 1) D2 = Mid$(V2, I, 1) R = R & Hex(Val("&H" & D1) Xor Val("&H" & D2)) Next I XorHexStrings = R End Function
I make no promises as to performance, but it seems to work.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html- Proposed as answer by Fei XueMicrosoft employee Tuesday, December 29, 2015 5:56 AM
- Marked as answer by Fei XueMicrosoft employee Monday, January 4, 2016 6:19 AM
Monday, December 28, 2015 10:02 PM