Excel 逻辑运算符按位运算

今天看了爱疯的提问,http://excelpx.com/dispbbs.asp?boardID=5&ID=147264&page=1

终于决定到网上查找资料,用代码验证按位运算的说法.下面是我根据网上资料做的验证And和Or两个逻辑运算符按位运算过程的示例，欢迎补充和指正。

运行Main可以在立即窗口中看到过程和结果。

Option Explicit

' 吕布于2010/11/01
Sub Main()
    Const POS_A As Integer = 20
    Const POS_B As Integer = 65
    Dim str1 As String, str2 As String, strTrue As String
    Dim strResult As String
    Dim i As Integer


    str1 = Hex2Bin(Hex(CLng(3)))        ' 00000000000000000000000000000011
    str2 = Hex2Bin(Hex(CLng(7)))        ' 00000000000000000000000000000111
    strTrue = Hex2Bin(Hex(CLng(True)))  ' 11111111111111111111111111111111

    Debug.Print "说明"; Tab(POS_A), "二进制表示"; Tab(POS_B), "值"
    Debug.Print "整数3"; Tab(POS_A), str1; Tab(POS_B), 3
    Debug.Print "整数7", Tab(POS_A), str2; Tab(POS_B), 7
    Debug.Print "逻辑值True"; Tab(POS_A), strTrue; Tab(POS_B), True
    Debug.Print

    ' And按位操作
    strResult = BitOperation(str1, str2, "AND")   ' 3 And 7
    Debug.Print "3 And 7"; Tab(POS_A), strResult; Tab(POS_B), Bin2Dec(strResult)
    strResult = BitOperation(strResult, strTrue, "AND")   ' 3 And 7 and True
    Debug.Print "3 And 7 and True"; Tab(POS_A), strResult; Tab(POS_B), Bin2Dec(strResult)
    Debug.Print

    ' Or按位操作
    strResult = BitOperation(str1, str2, "OR")   ' 3 Or 7
    Debug.Print "3 Or 7"; Tab(POS_A), strResult; Tab(POS_B), Bin2Dec(strResult)
    strResult = BitOperation(strResult, strTrue, "OR")   ' 3 Or 7 Or True
    Debug.Print "3 Or 7 Or True"; Tab(POS_A), strResult; Tab(POS_B), Bin2Dec(strResult)
End Sub

Function BitOperation(binArg1 As String, binArg2 As String, Operation As String) As String
' 把两位二进制字符串逐位进行AND运算,32位
    Dim i As Integer
    Dim str1 As String, str2 As String
    Dim sResult As String

    str1 = Right(String(32, "0") & binArg1, 32)
    str2 = Right(String(32, "0") & binArg2, 32)

    For i = 1 To 32
        If UCase(Operation) = "AND" Then
            sResult = sResult & (Mid(str1, i, 1) And Mid(str2, i, 1))
        ElseIf UCase(Operation) = "OR" Then
            sResult = sResult & (Mid(str1, i, 1) Or Mid(str2, i, 1))
        End If
    Next i
    BitOperation = sResult
End Function

Function Hex2Bin(ByVal Hex As String) As String
' 十六进制转换为二进制,32位
    Dim i As Long
    Dim B As String
    Hex = UCase(Hex)
    For i = 1 To Len(Hex)
        Select Case Mid(Hex, i, 1)
        Case "0": B = B & "0000"
        Case "1": B = B & "0001"
        Case "2": B = B & "0010"
        Case "3": B = B & "0011"
        Case "4": B = B & "0100"
        Case "5": B = B & "0101"
        Case "6": B = B & "0110"
        Case "7": B = B & "0111"
        Case "8": B = B & "1000"
        Case "9": B = B & "1001"
        Case "A": B = B & "1010"
        Case "B": B = B & "1011"
        Case "C": B = B & "1100"
        Case "D": B = B & "1101"
        Case "E": B = B & "1110"
        Case "F": B = B & "1111"
        End Select
    Next i
    While Left(B, 1) = "0"
        B = Right(B, Len(B) - 1)
    Wend
    Hex2Bin = Right(String(32, "0") & B, 32)
End Function

Function Bin2Dec(ByVal Bin As String) As Long
' 二进制数转换为十进制数,32位
    Dim i As Long
    Dim sTmp As String
    Dim bSign As Boolean
    Dim lTmp As Long

    sTmp = Right(String(32, "0") & Bin, 32)
    
    ' 如果是负数(首位为1)先取反
    If Left(sTmp, 1) = "1" Then
        bSign = True
        ' 取反
        For i = 1 To 32
            If Mid(sTmp, i, 1) = "1" Then Mid(sTmp, i, 1) = "0" Else Mid(sTmp, i, 1) = "1"
        Next i
    End If

    ' 正数转换为十进制
    For i = 1 To 32
        lTmp = lTmp * 2 + Val(Mid(sTmp, i, 1))
    Next i
    
    ' 如果是负数先加上负号再减1
    If bSign Then Bin2Dec = -lTmp - 1 Else Bin2Dec = lTmp
End Function