title: Bitwise And with Large Numbers in VBA
body: <p>I keep getting an Overflow on the bitwise and in this first function. I fixed the other overflows by converting from Long to Currency (still seems weird), but I can't get this And to work.</p>
  
  <p>Any ideas? I'm just trying to convert some IP addresses to CIDRs and calculate some host numbers.</p>
  
  <pre><code>Option Explicit
  
  Public Function ConvertMaskToCIDR(someIP As String, someMask As String)
  
      Dim ipL As Variant
      ipL = iPToNum(someIP)
      Dim maskL As Variant
      maskL = iPToNum(someMask)
      maskL = CDec(maskL)
  
      'Convert  Mask to CIDR(1-30)
      Dim oneBit As Variant
      oneBit = 2147483648#
      oneBit = CDec(oneBit)
      Dim CIDR As Integer
      CIDR = 0
  
      Dim x As Integer
  
      For x = 31 To 0 Step -1
          If (maskL And oneBit) = oneBit Then
              CIDR = CIDR + 1
          Else
              Exit For
          End If
          oneBit = oneBit / 2# 'Shift one bit to the right (&gt;&gt; 1)
      Next
  
      Dim answer As String
  
      answer = numToIp(ipL And maskL) &amp; " /" &amp; CStr(CIDR)
  
  End Function
  
  Public Function NumHostsInCidr(CIDR As Integer) As Currency
  
      Dim mask As Currency
  
      mask = maskFromCidr(CIDR)
  
      NumHostsInCidr = iPnumOfHosts(mask)
  
  End Function
  
  Private Function maskFromCidr(ByVal CIDR As Integer) As Currency
      'x = 32 - CIDR
      'z = (2^x)-1
      'return z xor 255.255.255.255
      maskFromCidr = CLng(2 ^ ((32 - CIDR)) - 1) Xor 4294967295# '255.255.255.255
  End Function
  
  Private Function iPnumOfHosts(ByVal IPmsk As Currency) As Currency 'a mask for the host portion
      '255.255.255.0 XOR 255.255.255.255 = 255 so 0 to 255 is 256 hosts
      iPnumOfHosts = IPmsk Xor 4294967295# '255.255.255.255 , calculate the number of hosts
  End Function
  
  Private Function numToIp(ByVal theIP As Currency) As String 'convert number back to IP
      Dim IPb(3) As Byte '4 octets
      Dim theBit As Integer
      theBit = 31 'work MSb to LSb
      Dim addr As String 'accumulator for address
      Dim x As Integer
      For x = 0 To 3 'four octets
          Dim y As Integer
          For y = 7 To 0 Step -1 '8 bits
              If (theIP And CLng(2 ^ theBit)) = CLng(2 ^ theBit) Then 'if the bit is on
                  IPb(x) = IPb(x) + CByte(2 ^ y) 'accumulate
              End If
              theBit = theBit - 1
          Next
          addr = addr &amp; CStr(IPb(x)) &amp; "." 'add current octet to string
      Next
      numToIp = trimLast(addr, ".")
  End Function
  
  Private Function iPToNum(ByVal ip As String) As Currency
  
      Dim IPpart As Variant
      Dim IPbyte(3) As Byte
  
      IPpart = Split(ip, ".")
      Dim x As Integer
      For x = 0 To 3
          IPbyte(x) = CByte(IPpart(x))
      Next x
  
      iPToNum = (IPbyte(0) * (256 ^ 3)) + (IPbyte(1) * (256 ^ 2)) + (IPbyte(2) * 256#) + IPbyte(3)
  
  End Function
  
  Private Function trimLast(str As String, chr As String)
      '****
      '*  Remove "chr" (if it exists) from end of "str".
      '****
      trimLast = str
      If Right(str, 1) = chr Then trimLast = Left(str, Len(str) - 1)
  End Function
  </code></pre>