One of the fundamental concepts of VBA is its data types, which basically define the kind of data that can be stored in a variable or used in a VBA program. Most of the commonly used data types in VBA are integers, strings, booleans, and floating-point numbers. However, there is one data type that is often misunderstood and underutilized the LongPtr data type.
VBA LongPtr Data Type
The LongPtr data type is a 64-bit variable type that can hold any valid memory address, including memory addresses greater than 4GB. It was introduced in VBA 7, which is included in Microsoft Office 2010 and all later versions. In this blog post, we will explore the syntax, storage, and range of the LongPtr data type, and also provide top 5 examples of VBA codes that use this data type.
Syntax
The syntax for declaring a variable with the LongPtr data type is as follows:
Dim VariableName as LongPtr
Where “VariableName” is the name of the variable you want to declare. It is worth noting that the LongPtr data type is not available in earlier versions of VBA, so if you try to use it in older versions of Microsoft Office, you will get a compilation error.
Storage
The LongPtr data type stores 8 bytes of memory, which is significantly more than the 4 bytes that are stored by the Long data type. This means that it can hold much larger values, making it ideal for dealing with large arrays or working with memory addresses.
Range
The range of values that can be stored in a LongPtr variable is the same as that of a Long variable. It can hold values from -2^63 to 2^63 – 1. This means that it can hold both positive and negative numbers, making it a versatile data type for various applications.
TVBA LongPtr Data Type – Examples
Example 1: Declaring a LongPtr Variable
Sub Example1()
Dim ptr As LongPtr
ptr = 100
MsgBox "Value of ptr is " & ptr
End Sub
In this example, we declare a LongPtr variable called “ptr” and assign it a value of 100. Then, we display the value of the variable in a message box using the MsgBox function. This code would display a message saying “Value of ptr is 100”.
Example 2: Allocating Memory with LongPtr
Sub Example2()
'Declare a variable of type LongPtr
Dim ptr As LongPtr
'Allocate memory for an array with 100 elements
ptr = LocalAlloc(LPTR, 100)
'Check if memory was successfully allocated
If ptr = 0 Then
MsgBox "Memory allocation failed"
Else
MsgBox "Memory allocated successfully"
End If
End Sub
In this example, we use the LocalAlloc function to allocate memory for an array with 100 elements. The function returns a LongPtr value, which we store in the “ptr” variable. Then, we use a simple if statement to check if the allocation was successful or not.
Example 3: Converting a Long to LongPtr
Sub Example3()
Dim val As Long
Dim ptr As LongPtr
val = 100
ptr = CLngPtr(val)
MsgBox "ptr is now equal to " & ptr
End Sub
Sometimes, it may be necessary to convert a Long value to a LongPtr value. This can be easily done using the function CLngPtr(), as shown in this example.
Example 4: Working with Arrays
Sub Example4()
'Declare an array of type LongPtr
Dim arr(1 To 5) As LongPtr
'Assign values to the array
arr(1) = 10
arr(2) = 20
arr(3) = 30
arr(4) = 40
arr(5) = 50
'Print the values in the immediate window
For i = 1 To 5
Debug.Print arr(i)
Next i
End Sub
The LongPtr data type is particularly useful when dealing with arrays, especially if the array is very large. In this example, we declare an array of type LongPtr and assign values to it. Then, we use a simple for loop to print the values in the immediate window.
Example 5: Using LongPtr to Declare Pointers
Sub Example5()
Dim ptr As LongPtr
Dim str As String
'Allocate memory for a string with 50 characters
ptr = LocalAlloc(LPTR, 50)
'Cast the ptr value to a string pointer
str = StrPtr(CStr(ptr))
'Write the string value in the allocated memory
Call lstrcpy(ptr, "Hello World!")
'Print the value of the string in the immediate window
Debug.Print str
End Sub
In VBA, it is possible to use the LongPtr data type to declare pointers, which can be used to interact with external Windows APIs. In this example, we allocate memory for a string and then use the StrPtr function to cast the LongPtr value to a string pointer. Then, we write a string value in the allocated memory using the lstrcpy function and print the value of the string pointer in the immediate window.
Conclusion
The LongPtr data type may not be as commonly used as other data types in VBA, but it is a powerful tool that can be used in a variety of applications. It is particularly useful for dealing with large amounts of data and memory addresses. In this blog post, we explored the syntax, storage, and range of this data type, and also provided top 5 examples of VBA codes that use it. We hope this post has helped you understand this data type better and how it can be used in your VBA projects.
