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

With its various data types, VBA allows you to store and manipulate different types of data, such as numbers, dates, and text. One such data type is ‘Long’, which comes in handy when dealing with larger numbers. In this blog post, we will explore the ins and outs of using the ‘Long’ data type in VBA, including its syntax, storage, range, and top 5 examples of VBA codes.

VBA DataType ‘Long’: Store whole numbers


In VBA, the ‘Long’ data type is used to store whole numbers ranging from -2,147,483,648 to 2,147,483,647. Its syntax is as follows:

 Dim variableName As Long

Here, ‘Dim’ stands for declare, and ‘As’ is used to specify the data type. The term ‘variableName’ can be replaced with any name of your choice, making it easier to identify and use the variable in your code.


The ‘Long’ data type takes up 4 bytes of memory and is stored as a signed integer, which means it can hold both positive and negative values. It is slightly larger than the ‘Integer’ data type, which can only store numbers ranging from -32,768 to 32,767. Since ‘Long’ can hold larger numbers, it is more suitable for calculations and operations that involve bigger values.


As mentioned earlier, the ‘Long’ data type can store numbers ranging from -2,147,483,648 to 2,147,483,647, giving it a much wider range compared to other data types in VBA. Its range makes it ideal for dealing with large datasets and performing mathematical calculations.

Example of VBA Long Data Type

Example 1: Checking Prime Numbers

The following code checks if a given number is prime or not using a ‘Long’ data type:

        Dim num As Long
        Dim i As Long
        Dim flag As Boolean
        flag = True
        For i = 2 To (num / 2)
            If num Mod i = 0 Then
                flag = False
                Exit For
            End If
        Next i
        If flag = True Then
            MsgBox num & " is a prime number."
            MsgBox num & " is not a prime number."
        End If

The code declares three variables: ‘num’ to store the input number, ‘i’ to use as a counter in the for loop, and ‘flag’ to indicate whether the number is prime or not. Using the ‘Mod’ function, the code divides the given number by all numbers from 2 to half of the number, checking for any remainder. If the remainder is zero, the ‘flag’ variable is set to ‘False’, and the loop is exited. Finally, a message is displayed depending on the value of ‘flag’.

Example 2: Finding the factorial of a number

The next code finds the factorial of a given number using the ‘Long’ data type:

        Function factorial(n As Long) As Long
            If n = 0 Then
                factorial = 1
                factorial = n * factorial(n - 1)
            End If
        End Function
        MsgBox factorial(5)

The code uses a recursive function to calculate the factorial of a given number. Since the ‘Long’ data type can store larger numbers, it is suitable for calculating factorials of bigger numbers. In the example, we have used the ‘MsgBox’ function to display the factorial of 5, which is 120.

Example 3: Using ‘Long’ with arrays

Arrays are a way to store multiple values in a single variable. The following code uses the ‘Long’ data type in conjunction with an array:

        Dim numberArray(5) As Long
        numberArray(0) = 10
        numberArray(1) = 20
        numberArray(2) = 30
        numberArray(3) = 40
        numberArray(4) = 50
        numberArray(5) = 60
        MsgBox numberArray(3)

The code declares an array with the size of 5 and populates it with different numbers. The ‘MsgBox’ function displays the value in the fourth element of the array, which is 40. This example showcases how we can use ‘Long’ data type with arrays to store and manipulate a bulk amount of data.

Example 4: Calculating profit percentage

In this example, we will calculate the profit percentage for a given amount using the ‘Long’ data type:

        Dim cost As Long
        Dim revenue As Long
        Dim profit As Long
        Dim percentage As Long
        cost = 10000
        revenue = 15000
        profit = revenue - cost
        percentage = (profit / cost) * 100
        MsgBox percentage & "%"

The code first declares four variables and assigns values to the ‘cost’ and ‘revenue’ variables. The ‘profit’ variable is then calculated by subtracting the cost from the revenue. Lastly, the profit percentage is calculated and displayed using the ‘MsgBox’ function.

Example 5: Generating random numbers

You can also use the ‘Long’ data type to generate random numbers within a specific range. This can come in handy when creating simulations or games. The following code generates a random number between 1 and 100:

        Dim randomNumber As Long
        randomNumber = Int((100 * Rnd) + 1)
        MsgBox "Random number: " & randomNumber

The ‘Randomize’ function ensures that a new set of random numbers is generated each time the code is run. The ‘Rnd’ function generates a random number between 0 and 1, which is then multiplied by 100 and added with 1 to create a number between 1 and 100. This number is then assigned to the ‘randomNumber’ variable and is displayed using the ‘MsgBox’ function.


The ‘Long’ data type in VBA is a powerful tool that allows you to store and manipulate larger numbers efficiently. Its wider range and higher storage capacity make it suitable for handling complex calculations and operations. In this blog post, we went through the syntax, storage, range, and top 5 examples of VBA codes using the ‘Long’ data type. Understanding how to use this data type will surely improve your efficiency in creating Excel macros.

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