Is it possible to store calculation results in VBA Memory?

[LucasLondon]

I have developed the code below which calculates some outputs based on looping through several test parameters (a series of numbers) and copies the outputs/calculation results for each different parameter into a summary sheet. However, instead of recording the results for all the parameters (lets assume there's 100) is it possible for VBA to store all the outputs in memory and once it's looped through all the parameters for the formula, only paste the results of the best parameter (as denoted as the maximum of one of the output cells - N2)? I read somewhere this was possible but don't know if it's true or how to do it.

The basic structure of the macro is:

1) A source sheet with the raw parameters - sheet called "Potential" from where each parameter is lifted
2) Calculation sheet where parameter is copied into to be used in a calculation of the output
3) Copy results for each parameter and paste into a summary sheet called results


On the current version, I get all results in the summary sheet for each parameter, but I only want to paste the result where N2 is maximised. Is there a way to do this without going into the result sheet and deleting all the rows that does not meet the criteria (i.e that does not have the maximum value in N2 ((the calculation sheet)), stored in column B in the summary sheet.

Thanks,

Lucas



Sub Parameters()
Application.ScreenUpdating = False
Dim x As Integer

Sheets("Potential").Select

For i = 2 To 100
Cells(i, 1).Copy
Sheets("Calculation").Select
Range("J2").PasteSpecial Paste:=xlPasteValues 'paste here for find function
'Run macro code
Range("M1:M5").Copy
Sheets("Results").Select
Range("A5000").Select
ActiveCell.End(xlUp).Select
ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Sheets("Potential").Select

Next i
Application.ScreenUpdating = True
End Sub

[mdmackillop]

If you just need the maximum, then store it in a separate variable

Sub Parameters()
Dim x As Single, y As Single
For i = 2 To 100
x = i * Rnd
If x > y Then y = x
Next i
MsgBox y
End Sub

[Bob Phillips]

You could also store them all in an array, and select the Application.Max value of the array, saves the check in the loop.

[p45cal]

A lot of unknowns in this question:
What's in N2 and what changes it?
What's 'Run macro code'? Does it change the active sheet?
How are the values in M1:M5 calculated?

I'd say it was very likely you don't need the Calculation and/or Results sheet(s) at all.

Ideally, post a workbook so that we can get the answers to these.

As an aside, if the 'run macro code' doesn't change the active sheet, you can use the following code to do the equivalent of what you pasted without all the select statements which also means you don't need to turn off/on ScreenUpdating:Sub Parameters()
Dim x As Integer
For i = 2 To 100
Sheets("Calculation").Range("J2") = Sheets("Potential").Cells(i, 1).Value 'paste here for find function
'Run macro code
Sheets("Results").Range("A5000").End(xlUp).Offset(1).Resize(, 5) = Application.Transpose(Sheets("Calculation").Range("M1:M5").Value)
Next i
End Sub

[LucasLondon]

Thanks very much Gents for your suggestions.

P45Cal, Thanks for your code, I ran it (without running the macro) and worked fine! To start with your questions first. Sorry N2 was a typo, it was suppose to be M2.

I have separate statistical macro (someone elses code) that uses the information on the Calculation sheet to run some econometric tests, the output of which is placed in cells m1 to M5. So yes when you run the statistical macro, it does change the activesheet (i.e the calculation sheet), but only the cells M1 to M5.

I have attached a copy of the setup of the workbook containing dummy data. I've included formula to illustrate the principle of how the process works from start to finish but the outputs are meaningless.

XLD/Mdmackillop - Now that I know this can be done, I've thought about it a bit more. I'd like to go down the route of storing the results in an array and then pasting them into the results sheet. It would be ideal If I could paste the results for 5 parameters that generate the top highest five values for M2 instead of just the single highest one.

However, it would be great if you can give me a bit guidance on adopting the current macro to do this (Mdmackillop - wasn't sure how to integrate your code!).

Also it would also be good to know how to store and paste the results of all the parameters for future reference. I'm assuming this solution is much faster than copying and pasting each result line seperately?

Many Thanks,

Lucas

[Bob Phillips]

I can't speak for the other guys, but I am not sure how to calculate those values, what the rule(s) is/are, and what exactly you want to do with them.

Can you clarify with reference to that workbook?

[p45cal]

I've had a stab. It ignores any formulae on the sheets as it calculates those values for itself (cells M1:M2 on the calculation sheet). I've used random numbers for the other 3.
I've used variable names similar to the cell addresses you used, just to confuse :rotlaugh:
Sub Parameters()
Dim myOutput(1 To 5, 1 To 5)
For i = 1 To UBound(myOutput)
myOutput(i, 2) = -99999 'initialises M2 to most negative
Next i
For i = 2 To 100
'For i = 1 To 99 'perhaps use instead of line above as nos. are in sequence on Potential sheet.
'Sheets("Calculation").Range("J2") = Sheets("Potential").Cells(i, 1).Value 'paste here for find function 'no longer required.
'Run/CALL macro:
VerySecretMacro Sheets("Potential").Cells(i, 1).Value, Rnd, Rnd, Rnd, M1, M2, M3, M4, M5 'it doesn't matter what values are in M1 to M5 when this macr is called.
'VerySecretMacro i, Rnd, Rnd, Rnd, M1, M2, M3, M4, M5 'perhaps use instead of line above as nos. are in sequence on Potential sheet.
For j = 1 To UBound(myOutput)
If M2 >= myOutput(j, 2) Then 'move everything below down one:
For k = UBound(myOutput) To j + 1 Step -1
For m = 1 To 5: myOutput(k, m) = myOutput(k - 1, m): Next m
Next k
'insert the new values:
myOutput(j, 1) = M1: myOutput(j, 2) = M2: myOutput(j, 3) = M3: myOutput(j, 4) = M4: myOutput(j, 5) = M5
Exit For
End If
Next j
'Sheets("Results").Range("A5000").End(xlUp).Offset(1).Resize(, 5) = Application.Transpose(Sheets("Calculation").Range("M1:M5").Value)
Next i
Sheets("Results").Range("A2").Resize(5, 5) = myOutput
End Sub
Sub VerySecretMacro(Arg1, Arg2, Arg3, Arg4, M1, M2, M3, M4, M5)
With ThisWorkbook.Sheets("Calculation")
M1 = Application.WorksheetFunction.Slope(.Range("E7:E688"), .Range("F7:F688"))
M2 = Application.WorksheetFunction.RSq(.Range("E7:E688"), .Range("F7:F688")) * Arg1
M3 = Arg2 * 9 'dummy value
M4 = Arg3 * 17 'dummy value
M5 = Arg4 * 27 'dummy value
End With
End Sub It's not been tested fully 'cos I'm tired (I haven't checked that the results are sorted correctly if the values in column A of the Potential sheet are not in numerical order). Note that because these increment by one in each loop then necessarily the results contain only the last 5 iterations of the loop.

[LucasLondon]

P45Cal,

Wow - Really appreciate your efforts in writing the code.

I have to be honest, this code much more sophisticated and complicated than I expected (although I'm sure it's very efficient and professional!). I guess I was looking for a more simpler dummies solution that utilises the results from the output cells rather than that does the calculations in memory as this format would be easier for me to understand and learn from in case I need to change, adapt and develop the code further (very likely).

So the key thing for me that would be useful from a learning perspective is:

1) With the existing setup (my orginal code or the code below) how would you store the output results from all the parameters (M1:M5) in memory and then paste all in one go into the summary sheet instead of pasting each line one by one via the loop?


Sub Parameters_P45calorginal()

For i = 2 To 100
Sheets("Calculation").Range("J2") = Sheets("Potential").Cells(i, 1).Value 'paste here for find function
'Run macro code
Sheets("Results").Range("A5000").End(xlUp).Offset(1).Resize(, 5) _
= Application.Transpose(Sheets("Calculation").Range("M1:M5").Value)
Next i
End Sub


2) And then it would be useful to see how you only paste a subset of these (that meet a certain criteria, e.g top 5).

I think I need to understand the first element before I can move onto working with the second.

Apologies for the slow learning, I'm not a programmer and no training in VBA, I'm just learning bit by bit, to be honest I don't understand most of the new code at all and so will be difficult for me to work with!

Incidently, just one comment on the new code, I set the value of M1 as J2 in the verysecretmacro sub so that I could record the values associated with each parameter in the results sheet in column A. But column A appears as blank. (See attached updated spreadsheet) so this does not appear to be working - evidence of my lack of understanding I suppose!

Thanks,

Lucas

[p45cal]

Starting with the last first.
Re:"I set the value of M1 as J2 in the verysecretmacro sub so that I could record the values associated with each parameter in the results sheet in
column A. But column A appears as blank."
Quick answer: replace your
M1 = J2withM1 = Arg1
But more importantly your 'M1 = J2' implies that you think that J2 refers to cell J2, which it doesn't. It's a
variable which hasn't had any value assigned to it, so you see blank cells.
I did say "I've used variable names similar to the cell addresses you used, just to confuse" where I was referring to M1
to M5. In the code they're just variable names, and in the code I assign values to them. It was easier for
me to call them that, so I remembered what they were about. I should have called them something different
so as not to confuse, ZZ1 to ZZ5 for example.
You'll see that
M1 = Arg1
works because Arg1 contains what would have been put in cell J2.
When VerySecretMacro is called in the Parameters procedure, observe that
Sheets("Potential").Cells(i, 1).Value
is the the first argument to be passed (the same thing that you put in cell J2 on the sheet). Because in the
VerySecretMacro procedure itself, Arg1 is the first argument, Arg1 is passed (assigned) that value by the call.


re:"With the existing setup (my orginal code or the code below) how would you store the output results from all the parameters (M1:M5) in memory
and then paste all in one go into the summary sheet instead of pasting each line one by one via the loop? "
Taking the second bit first, about pasting in one go: that's happening in the code with the line:
Sheets("Results").Range("A2").Resize(5, 5) = myOutput The myOutput is where the output results
are stored in memory. It is an array, 5x5. Each member of the array contains 5 members destined to hold the
values M1 to M5

To better visualise what's going on you can take advantage of a couple of things in the VBE:
Put your editing cursor somewhere in the Parameters procedure and press the F8 key. Repeated pressing of that
key will take you step by step through the code. While doing that, you can hover the mouse over bits of the
code and values of variables should pop up in a little box. At the same time you can bring up the Locals pane in
the VBE with the Alt + V, S shortcut (same as View|Locals window from the dropdown menus at the top) and you
should be able to see the likes of myOutput there, and if you click on the small plus sign to expand them you
should be able to see their values changing.

To answer your "how would you store the output results from all the parameters (M1:M5) in memory"
This could be one way of doing it (but it's far from my preference):Sub Parameters()
Dim myOutput(2 To 20, 1 To 5) 'make array the size it needs to be.
For i = 2 To 20 'this 2 to 20 should match the 2 to 20 in the line above to do that you could use the next commented
out line:
'For i = LBound(myOutput) To UBound(myOutput)
Sheets("Calculation").Range("J2") = Sheets("Potential").Cells(i, 1).Value 'paste here for find function
'Run macro code
For j = 1 To 5
myOutput(i, j) = Sheets("Calculation").Range("M" & j).Value
Next j
Next i
Sheets("Results").Range("A2").Resize(UBound(myOutput) - LBound(myOutput) + 1, 5) = myOutput
End Sub

Your strategy was to put all the results in memory and then find the 5 largest and delete all the rest. Mine was
a little different; each time a new set of 5 values was returned by the VerySecretMacro I compared what you
call M2 with each of the previous M2s in the array to see if it was bigger, and if it was bigger, I moved all the
values below down one (with the smallest dropping off the bottom into the ether) and put the new values in the
array in the right place, so the array always contains the 5 largest found so far.

So to answer "And then it would be useful to see how you only paste a subset of these (that meet a certain criteria, e.g top 5)."
This is the bit I don't relish coding for, which is why I put the values in the right place as they came in. There
is no built-in sorting of arrays in VBA as there is on a worksheet. There may be a solution using the Large function
with/or the SumProduct function in VBA; the likes of XLD, Bob Umlas or John Walkenbach would be able to help
you better then I could (That should put a smile on XLD's face, being named alongside those two!).

Finally the amended code as I prefer it (sorting on the fly) and the M variable names converted to ZZ (It also
includes a new variable TopHowMany which in this example is set to 5 to give you the top 5 largest - adjust
to suit.):

Sub Parameters()
Dim myOutPut()
TopHowMany = 5 'adjust to suit how many results you want.
ReDim myOutPut(1 To TopHowMany, 1 To 5)
For i = 1 To TopHowMany
myOutPut(i, 2) = -99999 'initialises ZZ2 to most negative
Next i
For i = 2 To 100
'For i = 1 To 99 'perhaps use instead of line above as nos. are in sequence on Potential sheet.
'Sheets("Calculation").Range("J2") = Sheets("Potential").Cells(i, 1).Value 'paste here for find function no longer required.
'Run/CALL macro:
VerySecretMacro Sheets("Potential").Cells(i, 1).Value, Rnd, Rnd, Rnd, ZZ1, ZZ2, ZZ3, ZZ4, ZZ5 'it doesn't matter
what values are in ZZ1 to ZZ5 when this macro is called.
'VerySecretMacro i, Rnd, Rnd, Rnd, ZZ1, ZZ2, ZZ3, ZZ4, ZZ5 'perhaps use instead of line above as nos.
'are in sequence on Potential sheet.
For j = 1 To TopHowMany
If ZZ2 >= myOutPut(j, 2) Then 'move everything below down one:
For k = TopHowMany To j + 1 Step -1
For m = 1 To 5: myOutPut(k, m) = myOutPut(k - 1, m): Next m
Next k
'insert the new values:
myOutPut(j, 1) = ZZ1: myOutPut(j, 2) = ZZ2: myOutPut(j, 3) = ZZ3: myOutPut(j, 4) = ZZ4: _
myOutPut(j, 5) = ZZ5
Exit For
End If
Next j
Next i
Sheets("Results").Range("A2").Resize(TopHowMany, 5) = myOutPut
End Sub
Sub VerySecretMacro(Arg1, Arg2, Arg3, Arg4, ZZ1, ZZ2, ZZ3, ZZ4, ZZ5)
With ThisWorkbook.Sheets("Calculation")
ZZ1 = Arg1
ZZ2 = Application.WorksheetFunction.RSq(.Range("E7:E688"), .Range("F7:F688")) * Arg1
ZZ3 = Application.WorksheetFunction.Slope(.Range("E7:E688"), .Range("F7:F688"))
ZZ4 = Arg3 * 17 'dummy value
ZZ5 = Arg4 * 27 'dummy value
End With
End Sub

[LucasLondon]

Hi P45Cal,

Thanks very much for the additional code and taking time to explain it. I'm a lot clearer now in terms of the key elements of how it works but will need to study it in a lot more detail and read up on Arrays to understand it better so thanks!

I like the short version of the code (that stores the complete array of results) because all inputs and outputs are referenced from underlying spreadsheets, this makes it much easlier for me to understand what's going on and how to modify and change it without breaking the code.

In terms of returning only a subset of the total results (based on the second column of myOutPut array). Would it be possible to apply the max function to the array (to column 2), (M2 in calculation sheet), extract the line into the result sheet and then delete that line from the array and apply the max value again and keep repeating the process until you have extracted the line with the top five values?

Alternatively, I have found a UDF for sorting values within an array on the internet although I don't know how useful it would be in this instance:


Option Explicit

Public Function SortV(ByRef SortRange As Variant, Optional SortBy As Long = 1, Optional Order As Long = 1) As Variant
Dim iSpacing As Long
Dim iOuter As Long
Dim iInner As Long
Dim iLBound As Long
Dim iUBound As Long
Dim iFinished As Long
Dim numcols As Long, Swap As Long, Swaprtn As Long

If TypeName(SortRange) = "Range" Then SortRange = SortRange.Value2

iLBound = LBound(SortRange)
iUBound = UBound(SortRange)
numcols = UBound(SortRange, 2) - LBound(SortRange, 2) + 1

'Initialise comb width
iSpacing = iUBound - iLBound

Do
If iSpacing > 1 Then
iSpacing = Int(iSpacing / 1.3)

If iSpacing = 0 Then
iSpacing = 1 'Dont go lower than 1
ElseIf iSpacing > 8 And iSpacing < 11 Then
iSpacing = 11 'This is a special number, goes faster than 9 and 10
End If
End If

'Always go down to 1 before attempting to exit
If iSpacing = 1 Then iFinished = 1

'Combing pass
For iOuter = iLBound To iUBound - iSpacing
iInner = iOuter + iSpacing

If Order = 1 Then
If SortRange(iOuter, SortBy) > SortRange(iInner, SortBy) Then Swap = 1
Else
If SortRange(iOuter, SortBy) < SortRange(iInner, SortBy) Then Swap = 1
End If
If Swap = 1 Then
Swaprtn = SwapRows(SortRange, iOuter, iInner, numcols)
Swap = 0
'Not finished
iFinished = 0
End If
Next iOuter

Loop Until iFinished
SortV = SortRange
End Function
Function SwapRows(SwapArray As Variant, Row1 As Long, Row2 As Long, numcols As Long) As Long
Dim i As Long, Temp As Variant
For i = 1 To numcols
Temp = SwapArray(Row1, i)
SwapArray(Row1, i) = SwapArray(Row2, i)
SwapArray(Row2, i) = Temp
Next i
If i = numcols Then SwapRows = 0 Else SwapRows = i
End Function


http://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/

I also found some different code to do the same thing here:

http://www.cpearson.com/excel/SortingArrays.aspx

Could either of these be used to sort by the highest values in column 2 in myoutput array (M2 in calculation sheet) and then to bring back say the top 5 rows from the array?

Thanks

Lucas

[p45cal]

I'm sure either of these could be adapted, however, that's more than double the amount of code so far suggested and you've already got a sorting technique in the code I provided. If you set TopHowMany to 99 you'll have the whole result set sorted. Now you can just take the topmost 5.
Which is what I've done anyway, without holding the the lesser values.

Maybe start a new thread about sorting and taking only the top n of an array so that others can contibute. They will be more knowledgeable than I am on that.

[LucasLondon]

Hi P45Cal,

For now I think I've got what I need but as I develop the code further I'm sure my needs will change at which point I'll post again.

Thanks,

Lucas

Maybe I start a thread sometimeYep I will

[LucasLondon]

Hello

I have changed the layout of the calculation sheet and now instead of having the output results appear vertically in column M (M1 to M5) , I have re-arranged them to be horizontal in row 4 (F4 to J4).

I tried playing around with the macro to accommodate this change but because of my limited knowledge/understanding of using Arrays, I'm struggling to make it work.

Would appreciate if someone can help.

Here is was I have done so far. Any changed lines in the Old code are marked as "original".

Thanks,

Lucas


Sub Backtest_Parameters_latest_test()
Dim x As Integer
x = Sheets("Test Parameters").Cells(500, 1).End(xlUp).Row

Dim myOutput
'Dim myOutput(2 To 20, 1 To 5) 'ORGINAL
ReDim myOutput(2 To x, 1 To 5)
For i = 2 To x
Sheets("Calculation").Range("I2") = Sheets("Test Parameters").Cells(i, 1).Value 'ORGINAL
Sheets("Calculation").Range("R2") = Sheets("Test Parameters").Cells(i, 1).Value
'Run macro code
For j = 1 To 5
' myOutput(i, j) = Sheets("Calculation").Range("M" & j).Value 'ORGINAL
myOutput(j, i) = Sheets("Calculation").Range(j & 4).Value
Next j
Next i
Sheets("Results").Range("A2").Resize(UBound(myOutput) - LBound(myOutput) + 1, 5) = myOutput
End Sub

[LucasLondon]

Hi,

Did anyone get a chance to look at my last post regarding switching the array input from rows to columns?

Thanks,

Lucas