CODEWITHSUNDEEP
vbaexcelinteger-overflow
Divin3
Divin3

Reputation: 548

VBA - how does "Long" work in VBA - playing with owerflow

So I know that in Excel VBA, Long range is -2,147,483,648 to 2,147,483,647

I am experimenting with file size handling and overflow right now and I am wondering what exactly happens when I overflow the range.

So in the code I ask for a file size in bytes with the FileLen function.

If the file is larger than 2,147,483,647 bytes, the messages I will get will be a negative value. I think this is because the function will start using the negative values since it ran out of positive ones.

The interesting thing was when I ran the function on some bigger files: one of them was 4 294 552 366 bytes and the other is 4 298 779 628 bytes
for one I got the expected negative value and for the other I got a positive value again.

Results:
4 043 308 965 bytes - -251658331
4 294 552 366 bytes - -414930
4 298 779 628 bytes - 3812332

I have tried to do some math with this, but I can not figure out its logic.
Could someone explain what happens in the background and why do I get these results?

Upvotes: 1

Views: 296

Answers (1)

KekuSemau
KekuSemau

Reputation: 6856

I have a 7GB file for testing, here is what I get: The actual file size is 7017693184 Bytes, = 

110100010010010011000000000000000

(33 digits in a 64-bit Integer, imagine more zeros to the left to fill up 64 digits).
FileLen obviously just truncates the value and takes the lower 32 bits. It returns -1572241408 Bytes for the file which indeed is

 10100010010010011000000000000000

=almost the same, just one 1 cut off at the beginning.
Since now in this 32-bit Integer the MSB (most significant bit, ie. the left-most '1') is set, this becomes a negative number (if you want to know more about this, read more here)
This is rather odd, the method should better throw an overflow error.

Solution:
It seems the FileSystemObject can handle large files:

Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfile(..filename..)
MsgBox _
    f.Size & " Byte" & _
    vbCrLf & f.Size / 1024 & " KB" & _
    vbCrLf & f.Size / 1024 / 1024 & " MB"

Upvotes: 4

Related Questions