Announcement

Collapse
No announcement yet.

Need help with custom function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with custom function



    So the function needs to calculate a binary non equivalent number. It has 2 arguments - the 2 cells with the numbers in them. For instance =function(1010;1100) = 110 . Like a XOR calculator.
    Thanks
    P.S. if there are any czechs and slovaks on this site please let me know.

  • #2


    If it's an XOR type function you need assuming only binary input then maybe the following will work.

    BinXOR is the function you will call on the spreadsheet eg, =BinXOR(A1,A2)
    The LeftPadZero is required to make the input binary characters the same length for processing.

    Code:
    'performs an XOR type function
    'input variables are assumed to be in binary
    Function BinXOR(Inp1 As Long, Inp2 As Long) As Long
      Dim j As Long, k As Long
      Dim s1 As String, s2 As String, sFrm As String
      
      Application.Volatile
      
      'convert to string
      s1 = CStr(Inp1)
      s2 = CStr(Inp2)
      'work out which is longer
      j = Application.WorksheetFunction.Max(Len(s1), Len(s2))
      
      'leftpad the strings to the same length
      sFrm = Application.WorksheetFunction.Rept("0", j)
      s1 = LeftPadZero(s1, sFrm)
      s2 = LeftPadZero(s2, sFrm)
      
      'loop through each part of the binary characters
      For k = 1 To j
        'check to make sure the digits are binary
        If (Mid(s1, k, 1) = "0" Or Mid(s1, k, 1) = "1") And (Mid(s2, k, 1) = "0" Or Mid(s2, k, 1) = "1") Then
          If Mid(s1, k, 1) = Mid(s2, k, 1) Then
            BinXOR = BinXOR & "0"
          Else
            BinXOR = BinXOR & "1"
          End If
        Else
          'otherwise exit the function
          'because the return type is Long,
          'setting the result to "" will cause a #VALUE error to be returned
          BinXOR = ""
          Exit Function
        End If
      Next
      BinXOR = CLng(BinXOR)
    End Function 'BinXOR
    
    'this function will left pad a string using the passed in ZeroPad as a format
    Private Function LeftPadZero(ByVal Value As String, ByVal ZeroPad As String)
      If Not IsNumeric(Value) Then Value = "0"
      LeftPadZero = Format(Value, ZeroPad)
    End Function 'LeftPadZero

    Comment

    Working...
    X