How to return data from a complex nested populated VBA Array

Printable View

09-13-2018, 01:41 PM
Whisky

1 Attachment(s)

How to return data from a complex nested populated VBA Array

Hi   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.   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:   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   Stage 2)   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.  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).   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)   So for example in the VBA locals windows for the first column in the array (i,1),  I see as expected: (Results 4) (Results 4,1) column name   All good. Then I see   (Results 4,2) nothing When I click the drill down box relating to (Results 4,2) I see   (Results 4,2)(1,1)  populated value (Results 4,2)(1,2)  populated value (Results 4,2)(1,3)  populated value (Results 4,2)(1,4)  populated value   But it then seems VBA has created empty other sub arrays that are not needed that follow the same structure as above, i.e  (Results 4,2)(2) null (Results 4,2)(2,1)  null (Results 4,2)(2,2)  null (Results 4,2)(2,3)  null (Results 4,2)(2,4)  null   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 -  See attached image   My question is 1) How do I get this data out of the array into a spreadsheet? The following code only returns the column headings from (Results 4,1) but none of the calculation results from (Results 4,2)(1,1) to (Results 4,2)(1,1)?   Sheets("calculation").Range("AY2:BB32") = Application.transpose.Results()   I've tried using index function but can't get it to work.     2) My second question is instead of creating sub arrays is there a way to create just 5 storage points per variable/column  or (5*30) total points to capture the data and return it to an Excel sheet? 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: (i, 1) variable name (i, 2) udf output 1 (i, 3) udf output 2 (i, 4) udf output 3 (i, 5) udf output 4 Would really appreciate some help with this. Thanks Whisky     

Code:

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

 <div><img src="attachment.php?attachmentid=22872&stc=1" attachmentid="22872" alt="" id="vbattach_22872" class="previewthumb" style="cursor: pointer; opacity: 0.5;"> </div>
09-13-2018, 03:14 PM
Whisky

1 Attachment(s)

Hi. Sorry just re-read and looks like my post got all jumbled up so here it is again but this in English!

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. 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 summary array to a spreadsheet in one go. However it appears Excel is creating tens of sub arrays within the main arrays and so I am unable to get the results out to a spreadsheet. My code is below and here are the main components are:

Stage 1) This works and simply loops through columns of data in the Sheet("Dataraw") and place them into the "calculation" sheet one at a time. The column name is in cell U14 followed by the rest of the data in that column.

Stage 2) 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. 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)

However the UDF generates 4 output values for each variable in one go and because of this (I think), VBA is creating sub arrays to store the results but creates lots of empty arrays in the process. (See attachment). So for example in the VBA locals windows for the first column in the array (i,1), I see as expected:

(Results 4)
(Results 4,1) column name. 'All good. Then I see;
(Results 4,2) nothing

When I click the drill down box relating to (Results 4,2) I see:
(Results 4,2)(1,1) populated value
(Results 4,2)(1,2) populated value
(Results 4,2)(1,3) populated value
(Results 4,2)(1,4) populated value

But it then seems VBA creates other empty sub arrays that are not needed that follow the same structure as above, i.e
(Results 4,2)(2) null
(Results 4,2)(2,1) null
(Results 4,2)(2,2) null
(Results 4,2)(2,3) null
(Results 4,2)(2,4) null

And this empty arrays repeat up to Results (5,2). And then (Results 5) are populated with the next variable and the above pattern repeats itself - See attached image

My question is: 1) How do I get this data out of the array into a spreadsheet? The following code only returns the column headings from (Results 4,1) to (Results 30,1) but none of the calculation results from (Results 4,2)(1,1) to (Results 4,2)(1,1)?
Sheets("calculation").Range("AY2:BB32") = Application.transpose.Results()

I've tried using index function but can't get it to work.

2) My second question is instead of unintentionally creating all these empty sub arrays is there a way to create just 5 storage points per variable/column or (5*30) total points to capture the data and return it to an Excel sheet?
For example in the past I would just create an array of results (4 to 30, 1 to 5) to store results for each variable (i) like the following but this doesn't work and it just creates even more empty arrays:

Results (i, 1) = variable name (i, 1)
Results (i, 2) output 1 (i, 2)
Results (i, 3) output 2 (i, 3)
Results (i, 4) output 3 (i, 4)
Results (i, 5) output 4 (I,5)

Would really appreciate some help with this as it really puzzling!

Thanks

Whisky

Code:

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

I realise, this is about 10 days late, but have you sorted it?
Your custom function seems to be reuring an array 5 rows by 4 columns, but only the first row is populated by the UDF. We'd need to see the UDF and ask you if that's the intention or does the UDF need tweking to produce only a 1 row by 4 column array rather than a 5 by 4 array.
But even without alteration to the UDF you could populate the array destined for the sheet in a different way.

But there's something I don't understand, you create an array for the results (Dim Results(4 To 30, 1 To 2)) which initially seems tobe a 27 row by 2 column array,but when written to the sheet you transpose it, implying a destination range of 27 columns by 2 rows, but your destination range (AY2:BB32) is 4 columns wide by 31 rows deep.

Now this next is a guess, but how about something along the lines of:

Code:

Dim Results(4 To 30, 1 To 5) As Variant For i = 4 To Z 'for each column of data/series Sheets("calculation").Range("U14:U" & lastrow).Value = Sheets("Dataraw").Range(Cells(1, i), Cells(lastrow, i)).Value Results(i, 1) = Sheets("calculation").Range("U14").Value For j = 2 To 5 Results(i, j) = customfunction(x)(1, j - 1) Next j Next i

(untested) which is meant to produce a 27 row by 5 column array which I'm guessing is the sort of size array you're wanting to put on the sheet. It can be made more efficient (it calls the custom function 4 times when it need only be called once, but this can be tweaked later if I'm on the right track).
09-25-2018, 03:13 PM
Whisky

Hi P45Cal,

Thanks for your reply. Yes and No, I still don't understand why it created so many sub arrays but I managed to make it work in a slightly different way using the index function. I will test out the code you kindly provided as mine is probably not be the most efficient. My latest working version of the code is below. Sorry for any confusion with my original code, may have got some cell references wrong.

Code:

Sub code 2() Dim Startrange As Long, ColNum As Long, lastrow As Long Dim outvariables As Integer Dim rCalc As Range Application.ScreenUpdating = False Application.DisplayAlerts = False lastcol = Range("R14").End(xlToLeft).Column Startrange = 15 ColNum = Range("R14").End(xlToLeft).Column - 1 'determines start outvariables = ColNum - 2 'the number of variables lastvar = Split(Range("R14").End(xlToLeft).Address, "$")(1) Sheets("X Variables").Select z = Sheets("X Variables").Range("C1").End(xlToRight).Column For i = 4 To z 'variables begins with column 3 Application.Calculation = xlManual Sheets("Calcullation").Range("U14:U" & lastrow).Value = Sheets("X Variables").Range(Cells(1, i), Cells(lastrow, i)).Value Set rCalc = Sheets("Calculation").Range("U" & Startrange & ":U" & lastrow) Dim Results(4 To 30, 1 To 5) As Variant 'Dim Results(4 To 30, 1 To 1, 1 To 1) As Variant '3 Dimensional Array Results(i, 1) = Sheets("Calculation").Range("U14").Value 'name 'Results(i, 1, 2) = Application.Index(UDF), 0, 1) 'Stat 1 Results(i, 2) = Application.Index(UDF 1), 1, 1) ' Stat2 Results(i, 3) = Application.Index(UDF), 1, 2) Results(i, 4) = Application.Index(UDF, 1), 1, 3) Results(i, 5) = Application.Index(UDF), 1, 4) Next i Sheets("Calculation").Select Sheets("Calculation").Range("AY2:BB40") = Results() End Sub
09-26-2018, 08:06 AM
p45cal

I'm gobsmacked that this code is a 'working version'.
You really have a sheet called Calculation and another one called Calcullation?
What are UDF and UDF 1 in lines such as:

Code:

Results(i, 2) = Application.Index(UDF 1), 1, 1) ' Stat2 Results(i, 3) = Application.Index(UDF), 1, 2) Results(i, 4) = Application.Index(UDF, 1), 1, 3)

which, by the way, have an unusual/impossible parenthesis pairing?

customfunction(x) has disappeared too.
lastrow is Dimmed and used but it never has a value assigned to it.
LastVar is assigned a value but never used.
The name of the sub is impossible.

There is just too little cogent information to work with.
10-03-2018, 04:44 AM
Whisky

Hi

Lol!!!!

I tested your code p45cal and got it to work. Sorry I have now cleaned up both pieces of code as there was bunch of stuff I was experimenting with but it should make sense now. They return identical results. However my code takes 10.9 seconds to run (Version 2) versus 8.8 for the other (Version1) So it seems using the index function makes it run slightly slower. Not a huge time difference here but I only tested it on a small dataset, the difference could be much bigger on a larger one. So it seems Version1 maybe the best way to go. How would I modify Version1 to only call the custom function once instead of 4 times?

One other question is would reading all the data from Sheet ("X Variables") in one go into a big data array and than iterating each col of data from that array into the UDF (instead of copying and pasting one at a time in column U as currently) speed or slow down the macro? I'm assuming it would speed it up as Excel would only have to read the raw data in once and don't need to copy and paste multiple times but then again maybe reading in a big array into memory may have the opposite effect?

Code:

Sub Version1() StartTime = Timer Dim Startrange As Long, ColNum As Long, lastrow As Long Dim outvariables As Integer Dim rCalc As Range Application.ScreenUpdating = False Application.DisplayAlerts = False lastrow = Range("B15").End(xlDown).row lastcol = Range("R14").End(xlToLeft).Column 'address bring back the cell address, splits on takes the letter from address Startrange = 15 ColNum = Range("R14").End(xlToLeft).Column - 1 'determines start outvariables = ColNum - 2 'the number of variables lastvar = Split(Range("R14").End(xlToLeft).Address, "$")(1) 'address bring back the cell address, splits on takes the letter from address Sheets("X Variables").Select z = Sheets("X Variables").Range("C1").End(xlToRight).Column For i = 4 To z 'assumes x variables begins with column 3 Sheets("Calculation").Range("U14:U" & lastrow).Value = Sheets("X Variables").Range(Cells(1, i), Cells(lastrow, i)).Value Set rCalc = Sheets("Calculation").Range("U" & Startrange & ":U" & lastrow) Dim Results(4 To 33, 1 To 4) As Variant 'for each column of data/series Results(i, 1) = Sheets("Calculation").Range("U14").Value For j = 2 To 4 Results(i, j) = UDF(rCalc, 1)(1, j - 1) Next j Next i Sheets("Calculation").Select Sheets("Calculation").Range("AY2:BB40") = Results() 'Stop Application.ScreenUpdating = True MsgBox Timer - StartTime & " seconds" End Sub

Code:

Sub Version2() StartTime = Timer Dim Startrange As Long, ColNum As Long, lastrow As Long Dim outvariables As Integer Dim rCalc As Range Application.ScreenUpdating = False Application.DisplayAlerts = False lastrow = Range("B15").End(xlDown).row lastcol = Range("R14").End(xlToLeft).Column 'address bring back the cell address, splits on takes the letter from address Startrange = 15 ColNum = Range("R14").End(xlToLeft).Column - 1 'determines start outvariables = ColNum - 2 'the number of variables lastvar = Split(Range("R14").End(xlToLeft).Address, "$")(1) 'address bring back the cell address, splits on takes the letter from address Sheets("X Variables").Select z = Sheets("X Variables").Range("C1").End(xlToRight).Column For i = 4 To z 'assumes x variables begins with column 3 Sheets("Calculation").Range("U14:U" & lastrow).Value = Sheets("X Variables").Range(Cells(1, i), Cells(lastrow, i)).Value Set rCalc = Sheets("Calculation").Range("U" & Startrange & ":U" & lastrow) Dim Results(4 To 33, 1 To 4) As Variant Results(i, 1) = Sheets("Calculation").Range("U14").Value Results(i, 2) = Application.Index(UDF(rCalc, 1), 1, 1) Results(i, 3) = Application.Index(UDF(rCalc, 1), 1, 2) Results(i, 4) = Application.Index(UDF(rCalc, 1), 1, 3) Results(i, 5) = Application.Index(UDF(rCalc, 1), 1, 4) Sheets("Calculation").Select Sheets("Calculation").Range("AY2:BB40") = Results() 'Stop Application.ScreenUpdating = True MsgBox Timer - StartTime & " seconds" End Sub
10-03-2018, 05:08 AM
p45cal

It's very, very difficult to work blind; impossible to say what's taking the time without knowing what's happening in the UDF function and the kind of data it's working on.
Supply a workbook with all relevant code and relevant sheets, and if there's sensitive data, desensitise it without rendering the file unrealistic. If there's masses of code/data, help us by telling us where to look.
10-09-2018, 04:54 PM
p45cal

Quote:

Originally Posted by p45cal

Supply a workbook with all relevant code and relevant sheets, …

No?
OK.