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

The VBA Partition function segments numbers into defined ranges, aiding in data categorization, insightful analysis, and structured reporting.

VBA Partition Function – Purpose, Syntax and Arguments

Purpose of the Partition Function

The VBA Partition function is designed to determine where a particular number falls within a series of defined ranges. It returns a string indicating the range in which the number resides. This function can be particularly useful in data analysis and reporting tasks where data needs to be grouped into specific numeric ranges.

Syntax and Arguments

The syntax for the Partition function is as follows:

Partition(number, start, stop, interval)


  • number: The numeric value you want to locate within the defined ranges.
  • start: The starting value of the entire range.
  • stop: The ending value of the entire range.
  • interval: The size of each subrange or partition.

3. Remarks and Notes

  • String Output: The Partition function returns a string that indicates the range in which the number falls. For instance, if you have ranges like 0-10, 10-20, and so on, a number like 15 would result in the string “10:20”.
  • Use in Databases: While the Partition function can be used in VBA, it’s also commonly used in Microsoft Access queries to group data into specific numeric ranges, as demonstrated in the provided example.
  • Non-Overlapping Ranges: The ranges defined by the `start`, `stop`, and `interval` arguments are non-overlapping. The function calculates the ranges based on the provided interval.

The VBA Partition function, with its ability to segment numbers into specific ranges, offers developers a unique tool for data analysis and reporting. Whether you’re categorizing sales figures, analyzing frequency distributions, or just exploring data patterns, the Partition function provides a straightforward way to understand data distributions across defined numeric intervals. By mastering this function, you can add another powerful tool to your data analysis toolkit, ensuring more insightful and organized reports and analyses.

Walking Through the Examples of the VBA Partition Function

Example 1: Grouping Ages into Ranges

Grouping a list of ages into predefined age brackets for demographic analysis.

Dim AgeBracket As String
AgeBracket = Partition(Age, 0, 100, 10)

The Partition function groups the `Age` variable into decade brackets, such as “0:10”, “10:20”, and so on. For an age of 15, `AgeBracket` would be “10:20”.

Example 2: Categorizing Sales Figures

Segmenting sales figures into revenue brackets to understand sales performance.

Dim SalesBracket As String
SalesBracket = Partition(Sales, 0, 10000, 1000)

Sales figures are grouped into brackets of $1000, starting from $0 up to $10000. For a sales figure of $4500, `SalesBracket` would return “4000:5000”.

Example 3: Analyzing Test Scores

Dividing test scores into grade bands to categorize student performance.

Dim ScoreBand As String
ScoreBand = Partition(Score, 0, 100, 10)

The function divides the `Score` variable into bands of 10 points, such as “0:10”, “10:20”, etc. A score of 87 would place a student in the “80:90” band.

Example 4: Segmenting Customer Loyalty Points

Grouping customers based on their loyalty points to offer tier-based rewards.

Dim LoyaltyTier As String
LoyaltyTier = Partition(Points, 0, 500, 50)

Customers’ loyalty points are divided into tiers of 50 points, starting from 0 up to 500. A customer with 320 points would fall into the “300:350” tier.

Example 5: Categorizing Product Weights for Shipping

Segmenting products based on their weight to determine shipping costs.

Dim WeightCategory As String
WeightCategory = Partition(Weight, 0, 50, 5)

The function categorizes the `Weight` variable into bands of 5kg, such as “0:5”, “5:10”, and so on. A product weighing 8kg would be categorized under “5:10”.

These examples showcase the versatility of the Partition function in VBA. By understanding its application across various scenarios, developers can effectively segment and categorize data, leading to more insightful analyses and decision-making.


The VBA Partition function is a hidden gem, adeptly segmenting numbers into specific ranges. It’s invaluable for structured data analysis, offering clarity in categorizing diverse datasets. Whether analyzing sales, scores, or demographics, Partition ensures data is neatly bracketed, paving the way for insightful conclusions. A must-know for those keen on organized, precise data reporting.

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 FunctionsTags: , , , Last Updated: October 1, 2023

Leave A Comment