VBA offers a range of data types to facilitate efficient and effective coding. One such data type is ‘LongLong’, which is a 64-bit signed integer. This blog post will delve into everything you need to know about the VBA DataType ‘LongLong’, including its syntax, storage, range, and provide an explanation with top 5 Example VBA Codes. So, let’s dive in!
The VBA DataType ‘LongLong’: Everything You Need to Know
Syntax
The ‘LongLong’ data type is declared using the LongLong keyword in VBA. It can hold larger values than the ‘Long’ data type, which is a 32-bit signed integer. The following is an example of declaring a variable as a ‘LongLong’ data type:
Dim myNum As LongLong
Storage
As mentioned earlier, the ‘LongLong’ data type is a 64-bit signed integer. This means it can hold integer values ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. The ‘LongLong’ data type takes up 8 bytes of memory, which is twice the size of the ‘Long’ data type.
Range
The range of values that can be stored in a ‘LongLong’ variable in VBA is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. This is an exceptionally large range, making the ‘LongLong’ data type useful for handling large numbers and calculations that involve such numbers.
VBA LongLong DataType Examples
1. Using the ‘LongLong’ data type in a For loop
One of the most common uses of the ‘LongLong’ data type is to handle large loops and computations. The following code demonstrates how to use the ‘LongLong’ data type in a For loop to loop through a large list of numbers.
Dim i As LongLong
For i = 1 To 1000000000
'Do something
Next i
In this example, the ‘i’ variable is declared as a ‘LongLong’ data type, allowing it to handle the large range of values in the loop without causing an overflow error.
2. Handling large numbers in calculations
The ‘LongLong’ data type is often used to handle large numbers in calculations that may require precision. The following code shows an example of using the ‘LongLong’ data type to calculate the factorial of a large number.
Function factorial(n As LongLong) As LongLong
If n = 1 Then
factorial = 1
Else
factorial = n * factorial(n - 1)
End If
End Function
MsgBox factorial(100)
In this code, the ‘factorial’ function uses recursion to calculate the factorial of the input number. Since the input is declared as a ‘LongLong’ data type, the function can handle larger numbers without causing an overflow error.
3. Using the ‘LongLong’ data type to store file sizes
When dealing with files and folders, it is common to need to display their sizes in bytes. However, for larger files, the size can exceed the range of the ‘Long’ data type. The ‘LongLong’ data type comes in handy in such situations. The following code shows an example of retrieving the size of a file using the ‘LongLong’ data type.
Dim fs As Object 'FileSystemObject
Set fs = CreateObject("Scripting.FileSystemObject")
Dim fileSize As LongLong
fileSize = fs.GetFile("C:\Test\MyFile.xlsx").Size
Debug.Print fileSize & " bytes"
In this example, the ‘fileSize’ variable is declared as a ‘LongLong’ data type to store the size of the file, which can be quite large.
4. Working with 64-bit systems
In some cases, the ‘Long’ data type may not be sufficient to handle large numbers in 64-bit systems, as they have a larger address space. The ‘LongLong’ data type is specifically designed for 64-bit systems, making it the ideal choice in such cases. The following code shows an example of using the ‘LongLong’ data type to handle large numbers in a 64-bit system.
Declare PtrSafe Function GlobalAddAtom Lib "kernel32" Alias "GlobalAddAtomA" (ByVal lpString As String) As LongLong
Dim atomNum As LongLong
atomNum = GlobalAddAtom("Hello World")
MsgBox atomNum
In this code, the ‘GlobalAddAtom’ function is used to generate a unique atom number for a given string, which is then stored in a ‘LongLong’ variable. Since the function is designed for 64-bit systems, the ‘LongLong’ data type is necessary to handle the resulting atom number.
5. Creating large arrays using the ‘LongLong’ data type
Arrays can be a useful tool for storing and manipulating large sets of data in VBA. However, for larger arrays, the ‘Long’ data type may not suffice. The ‘LongLong’ data type comes in handy in such situations. The following code shows an example of creating a large array using the ‘LongLong’ data type.
Dim myArray() As LongLong ReDim myArray(1 To 1000000)
In this code, an array with a million elements is declared using the ‘LongLong’ data type. This allows the array to hold larger values without causing an overflow error.
Conclusion
In conclusion, the ‘LongLong’ data type in VBA is a versatile and essential tool for handling large numbers and calculations. It is especially useful for 64-bit systems and allows for a much larger range of values compared to the ‘Long’ data type. This blog post covered everything you need to know about the ‘LongLong’ data type, including its syntax, storage, range, and provided an explanation with top 5 Example VBA Codes. So, next time you encounter a task that involves handling large numbers in VBA, remember the ‘LongLong’ data type and use it to your advantage.
