How to return data from a complex nested populated VBA Array

[Whisky]

Hi<br>&nbsp;<br>I have some basic knowledge of how to create and store data in a vba array thanks to help on here in the past. However I have now encountered a new challenge/obstacle I've never seen before.<br>&nbsp;<br>Basically I have created an array to store results from a UDF calculation for a range of data variables (Columns) and return the results of the array to a spreadsheet in one go. However it appears excel is creating sub arrays within the main arrays and so I am unable to get the results to a spreadsheet. My code is below and here are the main components:<br>&nbsp;<br>Stage 1) This works and simply loops through columns in the Sheet("Dataraw") and place them into the "calculation" sheet one at a time. The column name in cell u14 followed by the rest of the data in that column<br>&nbsp;<br>Stage 2)&nbsp;&nbsp; Here I create an array [Results(4 To 30, 1 To 2)] to store the results for each variable/column that is looped from Column 4 to 30.&nbsp; The first column in the array stores the column name and this part works fine. The second column of the array stores the results from a UDF that takes each variable as an input and outputs stats - denoted here by custom function: Results(i, 2) = customfunction(x).<br>&nbsp;<br>However the UDF generates 4 output values in one go and because of this, VBA is creating sub arrays to store the results but creates lots of empty arrays in the process. (see attachment)<br>&nbsp;<br>So for example in the VBA locals windows for the first column in the array (i,1),&nbsp; I see as expected:<br><br>(Results 4)<br>(Results 4,1) column name<br>&nbsp;<br>All good. Then I see<br>&nbsp;<br>(Results 4,2) nothing<br>When I click the drill down box relating to (Results 4,2) I see<br>&nbsp;<br>(Results 4,2)(1,1)&nbsp; populated value<br>(Results 4,2)(1,2)&nbsp; populated value<br>(Results 4,2)(1,3)&nbsp; populated value<br>(Results 4,2)(1,4)&nbsp; populated value<br>&nbsp;<br>But it then seems VBA has created empty other sub arrays that are not needed that follow the same structure as above, i.e&nbsp;<br><br>(Results 4,2)(2) null<br>(Results 4,2)(2,1)&nbsp; null<br>(Results 4,2)(2,2)&nbsp; null<br>(Results 4,2)(2,3)&nbsp; null<br>(Results 4,2)(2,4)&nbsp; null<br>&nbsp;<br>And this empty arrays repeat up to Results (5,2). And then (Results 5) are populated the next variable and then the above pattern repeats -&nbsp;&nbsp;See attached image<br>&nbsp;<br>My question is 1) How do I get this data out of the array into a spreadsheet?<br>The following code only returns the column headings from (Results 4,1) but none of the calculation results from<br>(Results 4,2)(1,1) to (Results 4,2)(1,1)?<br>&nbsp;<br>Sheets("calculation").Range("AY2:BB32") = Application.transpose.Results()<br>&nbsp;<br>I've tried using index function but can't get it to work.<br>&nbsp;<br>&nbsp;<br>2) My second question is instead of creating sub arrays is there a way to create just 5 storage points per variable/column&nbsp; or (5*30) total points to capture the data and return it to an Excel sheet?<br><br>For example in the past I would just create an array of results(4 to 30, 1 to 5) to store results for each variablke (i) like this but this doesn't work and just create even more empty arrays following the format above:<br><br>(i, 1) variable name<br>(i, 2) udf output 1<br>(i, 3) udf output 2<br>(i, 4) udf output 3<br>(i, 5) udf output 4<br><br>Would really appreciate some help with this.<br><br>Thanks<br><br>Whisky<br>&nbsp;<br>&nbsp;<br>

STAGE 1<br>Sub Exampleofnestedarray()<br>Dim Startrange As Long, ColNum As Long, lastrow As Long<br>Dim outvariables As Integer<br>Dim x As Range<br>Dim sortcol As Integer<br>Dim sortdirect As String<br>&nbsp;lastrow = Range("B15").End(xlDown).row<br>&nbsp;lastcol = Range("R14").End(xlToLeft).Column 'address bring back the cell address, splits on takes the letter from address<br>&nbsp;Startrange = 14<br>&nbsp;ColNum = Range("R14").End(xlToLeft).Column - 1 'determines start<br>&nbsp;&nbsp;&nbsp;&nbsp; Sheets("Dataraw").Select<br>&nbsp;&nbsp; z = Sheets("Dataraw").Range("C1").End(xlToRight).Column<br>&nbsp;&nbsp; For i = 4 To z 'for each column of data/series<br>&nbsp;&nbsp;&nbsp; Sheets("calculation").Range("U14:U" &amp; lastrow).Value = Sheets("Dataraw").Range(Cells(1, i), Cells(lastrow, i)).Value<br>&nbsp;<br>'===STAGE2<br>'CREATE ARRAY TO Store Results from array function that returns four separate outputs (by column)<br>Dim Results(4 To 30, 1 To 2) As Variant<br>Results(i, 1) = Sheets("calculation").Range("U14").Value<br>Results(i, 2) = customfunction(x)<br>&nbsp;<br>Next i<br>&nbsp;<br>'==STAGE 3 - return results array data to sheet<br>Sheets("calculation").Range("AY2:BB32") = Application.transpose.Results()<br>&nbsp;<br>End Sub<br>

<br><br><br><div><img src="attachment.php?attachmentid=22872&amp;stc=1" attachmentid="22872" alt="" id="vbattach_22872" class="previewthumb" style="cursor: pointer; opacity: 0.5;"><br><br></div><br><br>&nbsp;<br>&nbsp;