How to edit values in the range of cells from User defined Function?

[ps1234]

So, I want to write into range of cells based on information read from the sheet cells. I have uploaded a mini-sheet and code below to show what I have going. The goal is to get below output

startVal = 63 (Value in B3)
length = 4 (Value in B4)

=PrintData(Sheet!$B$3,Sheet1!$B$4,"Sheet1!$B$7") this formula in B7, ideally in B6

output should be :
63
64
65
66

Public Function PrintData(a, b As Range, c As String) As String
StartValue = CInt(a.Value)
Length = CInt(b.Value)
outRange = Range(c)
PrintData = CStr(StartValue) 'this should fill outRange(1,1).Value)

For i = 2 To Length
outRange(i, 1).Value = CStr(StartValue + i) ' some data in place of data from POST RestApi
Next

End Function

 

[JGordon11]

For V2016 and UDF in 2016 formula needs to be entered with Ctrl-Shift-Enter not just Enter.

delme1.xlsx
	A	B	C	D	E
1
2
3	Start	64
4	Length	4
5
6		V365	V2016	UDF
7	Output	64	64	64
8		65	65	65
9		66	66	66
10		67	67	67
11
Sheet1

Cell Formulas
Range	Formula
B7:B10	B7	=SEQUENCE(B4,1,B3,1)
C7:C10	C7	=ROW(INDIRECT("1:" & B4))+B3-1
D7:D10	D7	=SequenceVBA(B3,B4)
Dynamic array formulas.

Function SequenceVBA(Start As Long, Count As Long)
    Dim i As Long
    ReDim A(1 To Count, 1 To 1)
    For i = 1 To Count
        A(i, 1) = Start + i - 1
    Next
    SequenceVBA = A
End Function

 

[ps1234]

Thanks for the reply. Is it possible to not select the output cells range manually, instead the vba code calculate the range internally based on length specified in B4.
So, say if I make the length 10 in B4, vba prints number from 64 to 73 in B6-B15.

In real project, I am getting an array of data from a Rest-Api, I need to dynamically display the data starting from StartCellAddress which in this case would be B6.

Thank you so much for your help!

 

[JGordon11]

The Function SequenceVBA above does that. It returns an array that can be used in another VBA code or entered onto the worksheet. Since it returns an array, if it is enetered into the worksheet in Excel 2016 it needs to be entered as Ctrl-Shift-Enter not just Enter. If you change B4 to 10 in the sheet I posted above you will see the arrays adjust themselves to be length 10.

 

[ps1234]

Ok I see it work now. I have excel 365 version, I guess it formula directly. If I do ctrl-Shift-Enter, I only get one cell populated, if however I enter formula using just enter, it works as expected. thanks so much!

 

[JGordon11]

Your profile indicates that you have 2016 version. You can update it to reflect the correct version.

 

[ps1234]

One more question, when I write this formula into a cell from vba I get an '@' before SequenceVBA and that is causing to print only one row, If I manually delete '@' everything works. Any idea where am I going wrong?
outputcell = Sheet1!$D$25
Range(outputcell).Formula = "=SequenceVBA(B3,B4)"

In cell D25 I see formula
=@SequenceVba(B3,B4)

If I click in the cell D25, delete '@' and press enter all the 6 rows with accending order numbers show up.

 

[Fluff]

Please update your Account details to show that you are using 365, rather than 2016.

You need to use Formula2, rather than Formula
But as you have 365, why are you not using the formula JGordon11 showed in cell B7 on post#2

 

[ps1234]

Formula2 did the trick! Reason not using Cell B7 solution because eventually the sequential output data will be replaced by some random data received from an http request. So far I have tested the below code and it all holds good. The next step is to add http-request, parsed-json data in to string data array and then replace it by DataStr() below.

Thanks all for your help, I think I got it from here. Yo guys Rock!

Function SequenceVBA(StartTime As String, EndTime As String)
Dim i, count As Long
Dim DataStr() As String

' StartTime and EndTime to be used in http request body
count = 10
ReDim DataStr(count)

For i = 0 To Count - 1
DataStr(i) = CStr(Rnd())
Next

ReDim a(1 To count, 1 To 1)
For i = 1 To count
a(i, 1) = DataStr(i - 1)
Next
SequenceVBA = a
End Function

 

[Fluff]

Glad we could help & thanks for the feedback.