UDF for sequence function

[dave2018]

can anyone come up with a replica for the sequence function for Excel 2016?

not the type which spills over, but rather one you can use in 1 cell.


Lets say MyCustomNumber = 4

for example, instead of doing =ROWS(INDIRECT("A1:A"&MyCustomNumber)) to generate ={1;2;3;4} when evaluating [F9] in edit mode, i can use =SEQUENCE(MyCustomNumber,1) -i think that's the syntax- to generate ={1,2,3,4}.

I hope i'm clear enough .
thx

 

[Rick Rothstein]

I think this UDF (named ArrSeq) will do what you asked for. It has the same syntax as Excel's SEQUENCE function...

=ArrSeq(Rows, Columns, Start, Step)

It will recreate an array of rows and columns starting with the indicated number and incrementing by the Step value. Note the last three arguments are optional and each one defaults to 1.

[table="width: 500"]
[tr]
	[td]Function ArrSeq(lRows As Long, Optional lCols As Long = 1, Optional lStart As Long = 1, Optional lStep As Long = 1) As Variant
  Dim R As Long, C As Long, Nums As Long
  Nums = lStart
  For R = 1 To lRows
    For C = 1 To lCols
      ArrSeq = ArrSeq & "," & Nums
      Nums = Nums + lStep
    Next
    ArrSeq = ArrSeq & ";"
  Next
  ArrSeq = Evaluate("{" & Replace(Mid(Left(ArrSeq, Len(ArrSeq) - 1), 2), ";,", ";") & "}")
End Function[/td]
[/tr]
[/table]

 

[dave2018]

Note the last three arguments are optional and each one defaults to 1.

. that was the first thing I looked for LOL!

AWESOME!!!!

i love it!

thank you,thank you,thank you!

 

[dave2018]

but it looks like the most it can do is 87? if i put 88 in the first argument, it returns #VALUE .

any idea? maybe a constraining limit in the Variant?

 

[Rick Rothstein]

but it looks like the most it can do is 87? if i put 88 in the first argument, it returns #VALUE .

any idea? maybe a constraining limit in the Variant?

The constraining limit was in the Evaluate function (its text argument can only be a maximum of 255 characters). Here is a replacement that will handle a larger range of values; however, Excel has a limit for the length of its formulas of 8192 characters total which, as far as I know, cannot be worked around, so you will have to watch out for that if you start to specify large numbers of rows and columns.

[table="width: 500"]
[tr]
	[td]Function ArrSeq(lRows As Long, Optional lCols As Long = 1, Optional lStart As Long = 1, Optional lStep As Long = 1) As Variant
  Dim R As Long, C As Long, SeqNum As Long, Nums As Variant
  ReDim Nums(1 To lRows, 1 To lCols)
  SeqNum = lStart
  For R = 1 To lRows
    For C = 1 To lCols
      Nums(R, C) = SeqNum
      SeqNum = SeqNum + lStep
    Next
  Next
  ArrSeq = Nums
End Function[/td]
[/tr]
[/table]

.