none
Excel VBA UDF Function XOR Operation. RRS feed

  • 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

    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.html

    Monday, 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

    Monday, December 28, 2015 10:02 PM