Consulting

Results 1 to 8 of 8

Thread: How to return data from a complex nested populated VBA Array

  1. #1
    VBAX Regular
    Joined
    May 2017
    Posts
    19
    Location

    How to return data from a complex nested populated VBA Array

    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;
    Attached Images Attached Images

  2. #2
    VBAX Regular
    Joined
    May 2017
    Posts
    19
    Location
    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
    Attached Images Attached Images

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,307
    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).
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    May 2017
    Posts
    19
    Location
    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

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,307
    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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Regular
    Joined
    May 2017
    Posts
    19
    Location
    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

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,307
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,307
    Quote Originally Posted by p45cal View Post
    Supply a workbook with all relevant code and relevant sheets, …
    No?
    OK.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •