How Excel handles Data Types

[johnnyL]

If you are curious about how Excel handles various Data Types, I did some research and created the following code:

Sub RevealExcelDataTypeLengths()
'
'   The debate over how Excel treats Integer vs Long, etc. There is some belief, because of info posted on the internet, that
'   Integer is automatically converted to Long. Run the code below and see what the result is.
'
    Dim Boolean1        As Boolean
    Dim Boolean2        As Boolean
'
    Dim Collection1     As Collection
    Dim Collection2     As Collection
'
    Dim Double1         As Double
    Dim Double2         As Double
'
    Dim Integer1        As Integer
    Dim Integer2        As Integer
'
    Dim Long1           As Long
    Dim Long2           As Long
    Dim StorageBits     As Long
    Dim StorageBytes    As Long
'
    Dim Object1         As Object
    Dim Object2         As Object
'
    Dim Single1         As Single
    Dim Single2         As Single
'
    Dim Variant1        As Variant
    Dim Variant2        As Variant
'
'
'Boolean
    StorageBytes = VarPtr(Boolean1) - VarPtr(Boolean2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Boolean    is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Integer
    StorageBytes = VarPtr(Integer1) - VarPtr(Integer2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Integer    is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Collection
    StorageBytes = VarPtr(Collection1) - VarPtr(Collection2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Collection is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Long
    StorageBytes = VarPtr(Long1) - VarPtr(Long2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Long       is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Object
    StorageBytes = VarPtr(Object1) - VarPtr(Object2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Object     is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Single
    StorageBytes = VarPtr(Single1) - VarPtr(Single2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Single     is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Double
    StorageBytes = VarPtr(Double1) - VarPtr(Double2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Double     is stored as:  " & StorageBytes & " bytes   which = " & StorageBits & " bits."
'
'Variant
    StorageBytes = VarPtr(Variant1) - VarPtr(Variant2)
    StorageBits = StorageBytes * 8
    Debug.Print "In this version of Excel, Variant    is stored as:  " & StorageBytes & " bytes  which = " & StorageBits & " bits."
End Sub

You can check your results [URL='https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary']Here.[/URL]

 

[mrshl9898]

Office 365

 

[Michael M]

Works fine for me in 2016....Thanks @johnnyL ....

 

[johnnyL]

@mrshl9898, from that error I am guessing you are using a 64Bit version of Office365?

 

[mrshl9898]

Correct

 

[johnnyL]

@mrshl9898, you would have to look into 'PtrSafe' to get that code to run on a 64Bit version of Office365. The point of the script is the results that it displays which should be similar to the following:

In this version of Excel, Boolean    is stored as:  2 bytes   which = 16 bits.
In this version of Excel, Integer    is stored as:  2 bytes   which = 16 bits.
In this version of Excel, Collection is stored as:  4 bytes   which = 32 bits.
In this version of Excel, Long       is stored as:  4 bytes   which = 32 bits.
In this version of Excel, Object     is stored as:  4 bytes   which = 32 bits.
In this version of Excel, Single     is stored as:  4 bytes   which = 32 bits.
In this version of Excel, Double     is stored as:  8 bytes   which = 64 bits.
In this version of Excel, Variant    is stored as:  16 bytes  which = 128 bits.

 

[johnnyL]

A post where I did a timing result between using Integer vs Long is located ---> here