Replicate generating number sequences in Excel VBA
In this tutorial, I will demonstrate how to replicate the generation of a sequence of numbers by writing User Defined Functions (UDFs) in VBA. This is a supporting article to the following related articles using the SEQUENCE function in Excel i.e., repeat numbers in ascending order and repeat a sequence of numbers multiple times.
Table of Contents
· Part I — Replicate the built-in SEQUENCE function in VBA
· Part II — Replicate the “Repeat Numbers in Ascending Order” functionality in VBA
· Part III — Replicate the “Repeat a Sequence of Numbers” functionality in VBA
· Bonus I — Getting familiar with the SEQUENCE function
· Bonus II- Replicate Python’s range() function
· Downloads
Part I — Replicate the built-in SEQUENCE function in VBA
Replicating Excel’s built-in SEQUENCE function =SEQUENCE(rows, [columns], [start], [step]) is quite trivial.
Here is one way of replicating the SEQUENCE logic. Note that if you wanted to allow for decimal numbers, then we would have to declare all variables as Double type instead of Long. For simplicity, we will stick to using Long integers.
The following logic should be fairly straightforward. The generation of the integer sequence gets generated by the for loop by incrementing the starting number by the step size. We use arrays as they are faster than collections or ranges.
Option Explicit
Option Base 1
Private Function IntSequence(N As Long _
, Optional StartInteger As Long = 1 _
, Optional StepSize As Long = 1 _
) As Long()
On Error GoTo error_handler
Dim sequence() As Long
ReDim sequence(1 To N)
Dim counter As Long
For counter = 1 To N
sequence(counter) = StartInteger + StepSize * (counter - 1)
Next counter
IntSequence = sequence
Exit Function
error_handler:
MsgBox "Error: " & Err.Number & " - " & Err.Description, _
Title:="Generate Integers Sequence"
On Error GoTo 0
End FunctionTo set any function as a User Defined Function (UDF), we need to call Application.Volatile inside a Public Function to deem that function as a UDF callable in a spreadsheet cell.
Note that I have chosen to call the IntSequence function separately from inside the UDF. This is for reusability purposes, as we shall see later. Since the IntSequence function returns an array, the Excel returns a horizontal row dynamic array back into the spreadsheet by default. For the UDF to return a column array, we need to make use of Application.Transpose(arr) to literally transpose the row array into a column array.
Public Function UDF_Sequence(N As Long _
, Optional StartInteger As Variant _
, Optional StepSize As Variant _
) As Variant
Application.Volatile
' Check for missing / empty inputs
If IsMissing(StartInteger) Or CStr(StartInteger) = vbNullString Then StartInteger = 1
If IsMissing(StepSize) Or CStr(StepSize) = vbNullString Then StepSize = 1
UDF_Sequence = Application.Transpose( _
IntSequence(N, CLng(StartInteger), CLng(StepSize)))
End FunctionThe optional parameters require a little extra validation as can be seen in the If statements before calling the IntSequence function which should be self explanatory.
Excel’s recent SEQUENCE function has made the above VBA code somewhat redundant since the introduction of Dynamic Array Functions in September 2018. However, you might find situations where writing an UDF is better suited to your business case.
Part II — Replicate the “Repeat Numbers in Ascending Order” functionality in VBA
In the article on repeating numbers in ascending order, we have the following convoluted spreadsheet formula:
=LAMBDA(max_num,rept_each,
CEILING.MATH(SEQUENCE(max_num * rept_each) / rept_each))(10, 3)To replicate the spreadsheet formula we need to reuse the IntSequence function here to generate the basic integer sequence. Then we apply the same logic as we did in the spreadsheet examples in the above article.
We need to make use of the worksheet function for taking the ceiling of the quotient. VBA does not have a built in ceiling function unfortunately.
Private Function RepeatIntAscendOrder(NumbersUpTo As Long _
, NumberOfReps As Long _
) As Long()
On Error GoTo error_handler
Dim N As Long
N = NumbersUpTo * NumberOfReps
Dim sequence() As Long
sequence = IntSequence(N)
Dim i As Long
For i = 1 To N
sequence(i) = WorksheetFunction.Ceiling_Math( _
sequence(i) / NumberOfReps)
Next i
RepeatIntAscendOrder = sequence
Exit Function
error_handler:
MsgBox "Error: " & Err.Number & " - " & Err.Description, _
Title:="Repeat Integers in Ascending Order"
On Error GoTo 0
End FunctionNote that declaring Option Base 1 at the top of our VBA module is very important, as it forces our arrays to start at index 1 and not at index 0 as it is conventional in programming. If we omit this declaration at the top of the code, then we will have to make use of the Lbound and Ubound VBA functions, to ensure that we are using the correct indices for our array.
Next, we turn the custom function into an UDF as follows:
Public Function UDF_RepeatIntAscendOrder(NumbersUpTo As Long _
, NumberOfReps As Long _
) As Variant
Application.Volatile
UDF_RepeatIntAscendOrder = Application.Transpose( _
RepeatIntAscendOrder( _
NumbersUpTo, NumberOfReps))
End FunctionThis UDF does not require any validation as it doesn’t have any optional inputs. If the user enters an argument with the incorrect type, the error handler in IntSequence should handle that exception.
Part III — Replicate the “Repeat a Sequence of Numbers” functionality in VBA
In the tutorial on repeating a sequence of numbers, we have the following convoluted spreadsheet formula:
=LAMBDA(max_num,num_reps, MOD(SEQUENCE(max_num * num_reps, , 0),
max_num) + 1)(4, 6)To replicate the spreadsheet formula we need to reuse the IntSequence function here to generate the basic integer sequence. Then we apply the same logic as we did in the spreadsheet examples in the above article.
Private Function RepeatIntSequence(NumbersUpTo As Long _
, NumberOfReps As Long _
) As Long()
On Error GoTo error_handler
Dim N As Long
N = NumbersUpTo * NumberOfReps
Dim sequence() As Long
sequence = IntSequence(N, 0)
Dim i As Long
For i = 1 To N
sequence(i) = (sequence(i) Mod NumbersUpTo) + 1
Next i
RepeatIntSequence = sequence
Exit Function
error_handler:
MsgBox "Error: " & Err.Number & " - " & Err.Description, _
Title:="Repeat Sequence of Integers"
On Error GoTo 0
End FunctionFirstly, we generate the base integers sequence starting at 0. Secondly, we make use of the built in Mod VBA function to obtain the remainder. lastly, we could add a starting number as an optional argument, which we could add instead of 1.
Note that declaring Option Base 1 at the top of our VBA module is again very important for handling the array’s indices correctly.
Next, we turn the custom function into an UDF as follows:
Public Function UDF_RepeatIntSequence(NumbersUpTo As Long _
, NumberOfReps As Long _
) As Variant
Application.Volatile
UDF_RepeatIntSequence = Application.Transpose( _
RepeatIntSequence(NumbersUpTo, NumberOfReps))
End FunctionAs before, the UDF does not require any validation as it doesn’t have any optional inputs. If the user enters an argument with the incorrect type, the error handler in IntSequence should handle that exception.
Bonus I — Getting familiar with the SEQUENCE function
The syntax for the SEQUENCE function is =SEQUENCE(rows, [columns], [start], [step])where the inputs are:
- rows is the number of rows to generate. If only this input is passed as an argument then it will generate a column of integers 1 up to the number of rows input, for example
=SEQUENCE(10)will return numbers 1 to 10. - [columns] is the optional number of columns to generate. The default is value 1. If we input the number of rows and columns, then it will output a 2D array a.k.a. a matrix containing the numbers 1 up to
rows * columns. To generate a matrix of numbers 1 to 36, try the following formula=SEQUENCE(6, 6). - [start] changes the starting number from the default of 1. For example, if you would like to start your sequence at the number 10, then simply change the third input argument to 10.
- [step] specifies the step size other than the default 1. For example, if you would like to generate the first ten odd numbers, then run the formula
=SEQUENCE(10, , , 2)or if you are not comfortable with passing blank arguments you can run=SEQUENCE(10, 1, 1, 2).
Generating an array of numbers using a function is nothing new. For example, we can generate a list of ten numbers in Python instantly with the range function:
nums = list(range(10))Mind you, this command produces numbers 0 to 9. It can be easily modified to give us the numbers 1 to 10 as follows:
nums = list(range(1, 11))The SEQUENCE function lets us generate a dynamic array of numbers in an Excel sheet effortlessly. Dynamic array functions such as the SEQUENCE function, are simply formulas that “spill” automatically in the neighbouring blank cells. These make the legacy Ctrl+Shift+Enter(CSE) array formulas redundant.
For the purposes of this tutorial, we will make use of the dynamic array functionality which is available for Excel 2021, Excel for Microsoft 365 and Excel for the web subscribers.
Bonus II- Replicate Python’s range() function
Here is how I would replicate python’s range function i.e., Excel’s SEQUENCE function’s older cousin:
from typing import List
def number_generator(start=0, step=1):
num = start
while True:
yield num
num += step
def get_sequence(limit, start=0, step=1) -> List:
generator = number_generator(start, step)
output_sequence = []
number = next(generator)
while number < limit:
output_sequence.append(number)
number = next(generator)
return output_sequence
if __name__ == '__main__':
custom_sequence = get_sequence(11, start=1)
print(custom_sequence)This is purely for the fun of it, and to demonstrate how to use generators in python.
Downloads
You are more than welcome to download the demo Excel workbook here.
Before you open the file downloaded from GitHub for the first time, make sure to right-click on the file and select Properties. In General > Security, select the “Unblock” check-box and then click the “Apply” button.
Otherwise the VBA code will be blocked.
