PDA

View Full Version : How to return data from a complex nested populated VBA Array



Whisky
09-13-2018, 01:41 PM
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;

Whisky
09-13-2018, 03:14 PM
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



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

p45cal
09-24-2018, 05:33 AM
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:
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).

Whisky
09-25-2018, 03:13 PM
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.



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

p45cal
09-26-2018, 08:06 AM
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:
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.

Whisky
10-03-2018, 04:44 AM
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?



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




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

p45cal
10-03-2018, 05:08 AM
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.

p45cal
10-09-2018, 04:54 PM
Supply a workbook with all relevant code and relevant sheets, …No?
OK.