我想我已经找到了这样做的方法。以下函数< code>DoubleToBin返回表示IEEE-754双浮点数的64位字符串。它通过将< code>LSet与用户定义的相同大小的类型相结合，使用VBA“技巧”来传递原始数据，而不使用API例程(如MemCopy (RtlMoveMemory))。一旦我们有了位串，我们就可以从中提取所有的成分。

Type TDouble
  Value As Double
End Type

Type TArray
  Value(1 To 8) As Byte
End Type

Function DoubleToArray(DPFloat As Double) As Variant
  Dim A As TDouble
  Dim B As TArray
  A.Value = DPFloat 
  LSet B = A
  DoubleToArray = B.Value
End Function

Function DoubleToBin(DPFloat As Double) As String
  Dim ByteArray() As Byte
  Dim BitString As String
  Dim i As Integer
  Dim j As Integer

  ByteArray = DoubleToArray(DPFloat)

  For i = 8 To 1 Step -1
    j = 2 ^ 7
    Do While j >= 1
      If (ByteArray(i) And j) = 0 Then
        BitString = BitString & "0"
      Else
        BitString = BitString & "1"
      End If
      j = j \ 2
    Loop
  Next i

  DoubleToBin = BitString
End Function

它是如何工作的，我现在接受我自己的答案吗？

这是对Con的优秀答案的修改。我修改了他们的函数，使用内置函数Hex而不是逐位操作来获取位模式，使其能够灵活处理单精度和双精度，并以十六进制（默认值）或二进制返回结果：

Type TDouble
  Value As Double
End Type

Type TSingle
  Value As Single
End Type

Type DArray
  Value(1 To 8) As Byte
End Type

Type SArray
  Value(1 To 4) As Byte
End Type

Function DoubleToArray(DPFloat As Double) As Variant
  Dim A As TDouble
  Dim B As DArray
  A.Value = DPFloat
  LSet B = A
  DoubleToArray = B.Value
End Function

Function SingleToArray(SPFloat As Single) As Variant
  Dim A As TSingle
  Dim B As SArray
  A.Value = SPFloat
  LSet B = A
  SingleToArray = B.Value
End Function

Function HexToBin(hDigit As String) As String
    Select Case hDigit
        Case "0": HexToBin = "0000"
        Case "1": HexToBin = "0001"
        Case "2": HexToBin = "0010"
        Case "3": HexToBin = "0011"
        Case "4": HexToBin = "0100"
        Case "5": HexToBin = "0101"
        Case "6": HexToBin = "0110"
        Case "7": HexToBin = "0111"
        Case "8": HexToBin = "1000"
        Case "9": HexToBin = "1001"
        Case "A": HexToBin = "1010"
        Case "B": HexToBin = "1011"
        Case "C": HexToBin = "1100"
        Case "D": HexToBin = "1101"
        Case "E": HexToBin = "1110"
        Case "F": HexToBin = "1111"
    End Select
End Function

Function ByteToString(B As Byte, Optional FullBinary As Boolean = False)
    Dim BitString As String
    BitString = Hex(B)
    If Len(BitString) < 2 Then BitString = "0" & BitString
    If FullBinary Then
        BitString = HexToBin(Mid(BitString, 1, 1)) & HexToBin(Mid(BitString, 2, 1))
    End If
    ByteToString = BitString
End Function

Function FloatToBits(float As Variant, Optional FullBinary As Boolean = False) As String
    Dim ByteArray() As Byte
    Dim BitString As String
    Dim i As Integer, n As Integer
    Dim x As Double, y As Single
    If TypeName(float) = "Double" Then
        n = 8
        x = float
        ByteArray = DoubleToArray(x)
    ElseIf TypeName(float) = "Single" Then
        n = 4
        y = float
        ByteArray = SingleToArray(y)
    Else
        FloatToBits = "Error!"
        Exit Function
    End If

    For i = n To 1 Step -1
        BitString = BitString & ByteToString(ByteArray(i), FullBinary)
    Next i
    FloatToBits = BitString
End Function

这里有一个测试:

Sub test()
    Dim x As Single, y As Double
    x = Application.WorksheetFunction.Pi()
    y = Application.WorksheetFunction.Pi()

    Debug.Print FloatToBits(x)
    Debug.Print FloatToBits(x, True)
    Debug.Print FloatToBits(y)
    Debug.Print FloatToBits(y, True)
End Sub

输出：

40490FDB
01000000010010010000111111011011
400921FB54442D18
0100000000001001001000011111101101010100010001000010110100011000

当我将400921FB54442D18输入这个在线工具时，我得到3.1452653589793，这非常有意义。

有点奇怪，当我把这个应用到10.4时，我得到

0100000000100100110011001100110011001100110011001100110011001101

这与这篇关于 Excel VBA 中浮点数的优秀文章中的示例最终不同。两个版本都四舍五入到 10.4（到很多很多地方）。我不太知道如何看待这种差异。

部分答案：

VBA 按位运算符设计用于处理整数或长整型数据。请考虑以下事项：

Sub test()
    Dim x As Single, y As Single
    x = 1#
    y = Not x
    Debug.Print y
    Debug.Print TypeName(Not x)
End Sub

输出：

-2 
Long

第一行输出是观察到的怪异。第二行是对这种怪异的解释。显然，x在被输入not之前被转换为长。有趣的是，以下C程序也打印-2：

int main(void){
    int x,y;
    x = 1;
    y = ~x;
    printf("%d\n",y);
    return 0;
}

（gcc在我的机器上使用32位int，因此这里的＜code＞int＜/code＞相当于VBA中的＜code＜Long＜/code〕）

应该有可能得到你想要的东西，但是按位操作符不是办法。