Function SEQUENCE(Optional ByVal rows As Long = 1, Optional ByVal columns As Long = 1, Optional ByVal start As Long = 1, Optional ByVal step As Long = 1) As Variant ' Excel 2021
' This custom VBA function takes up to four parameters:
'
' rows - the number of rows in the output array (default value is 1)
' columns - the number of columns in the output array (default value is 1)
' start - the starting value for the sequence (default value is 1)
' step - the step value for the sequence (default value is 1)
'
' At a minimum, you have to include a rows, or a columns
'
' To use it in a foumula on the worksheet:
' Highlight the cells that will be populated by the formula
' Put the formula in the top cell of the highlighted range ex. =SEQUENCE(10,1)
' Then do Ctrl+Shift+Enter to enter the formula as an array formula
'
Dim ArrayColumn As Long, ArrayRow As Long
Dim SequenceValue As Long
Dim SequenceArray As Variant
'
ReDim SequenceArray(1 To rows, 1 To columns)
SequenceValue = start
For ArrayRow = 1 To rows
For ArrayColumn = 1 To columns
SequenceArray(ArrayRow, ArrayColumn) = SequenceValue
SequenceValue = SequenceValue + step
Next
Next
SEQUENCE = SequenceArray
End Function
Function TEXTJOIN(delimiter As Variant, ignore_empty As Boolean, ParamArray TextArray() As Variant) As String ' Excel 2019
'
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove
' 2021, Eric W, Added multiple delimiter support
'
Dim Flag As Boolean
Dim ArrayRow As Long
Dim TextArrayRow As Long, LastRowDelimiterArray As Long
Dim DelimiterArray() As String
Dim CellToJoin As Variant
Dim DelimiterValue As Variant
'
TEXTJOIN = ""
'
If TypeOf delimiter Is range Then
ReDim DelimiterArray(0 To delimiter.Cells.Count - 1)
ArrayRow = 0
'
For Each DelimiterValue In delimiter
DelimiterArray(ArrayRow) = DelimiterValue.value
ArrayRow = ArrayRow + 1
Next
ElseIf IsArray(delimiter) Then
'' ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter) + 1)
ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter))
ArrayRow = 0
'
For Each DelimiterValue In delimiter
DelimiterArray(ArrayRow) = DelimiterValue
ArrayRow = ArrayRow + 1
Next
Else
ReDim DelimiterArray(0 To 0)
'' DelimiterArray(0) = delimiter
'
If IsMissing(delimiter) Then
DelimiterArray(0) = ""
Else
DelimiterArray(0) = delimiter
End If
End If
'
ArrayRow = 0
LastRowDelimiterArray = UBound(DelimiterArray) + 1
Flag = False
For TextArrayRow = LBound(TextArray) To UBound(TextArray)
If TypeOf TextArray(TextArrayRow) Is range Then
For Each CellToJoin In TextArray(TextArrayRow).Cells
If CellToJoin = "" And ignore_empty Then
Else
TEXTJOIN = TEXTJOIN & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin.value
ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
Flag = True
End If
Next
ElseIf IsArray(TextArray(TextArrayRow)) Then
For Each CellToJoin In TextArray(TextArrayRow)
If CellToJoin = "" And ignore_empty Then
Else
TEXTJOIN = TEXTJOIN & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin
ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
Flag = True
End If
Next
Else
If TextArray(TextArrayRow) = "" And ignore_empty Then
Else
TEXTJOIN = TEXTJOIN & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & TextArray(TextArrayRow)
ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
Flag = True
End If
End If
Next
End Function
