VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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.


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.


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.


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"
        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.


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.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA Data TypesLast Updated: September 23, 2023

Leave A Comment