No announcement yet.

Need help with custom function

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

    '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
      '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"
            BinXOR = BinXOR & "1"
          End If
          '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
      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