Hy all.I've got a problem wit multidimensional array dimensioning.I realized a vba macro which allow me to compare risk-return statistics of all possible baskets of 4 stocks combination extracted from the member of an equity index. Basically the macro computes risk and return of any stock belonging to the index. Then it calculates the statistics of every 4-stocks basket and store the information in arrays as the following: p tf_return(n - 3, n - 2, n - 1, n), with n equal to the number of members of the index selected.While it works when I perform this kind of analysis on the eurostox50 stocks (about 230.000 possible combinations or baskets), I receive the following message when I try to perform the same analysis with the S&P100 index ( 3.921.225 baskets): "Run-time error 7 - out of memory".Is there a limit at the maximum dimension of a multidimensional array? Or does it depend from physical memory on my PC (512mb).can you help me in solving this problem?Thanks in advance

There certainly is some restriction in VB although I don't remember exactly what it is.Anyways, I wouldn't do this kind of task in VBA unless I absolutely had to. To get around the maximum array size you can simply shove old entries somewhere - say write to a file after 300'000 entries and empty the array. Or try doing a database. Although I'd say the best way is to think again about the task at hand imho.

The limit depends on the amount of physical memory AND PageFile Size.However, relying on the page file will greatly reduce performance.Best bet is to redesign the algorithm, and max out memory on the system.If (n-2) *(n-1) * n * (n+1) = 3921225. then I expect that you just need to add memory to the PC.I do not recall how much additional overhead is required by multidimension array instead of single dimension array, but, as I recall, I've used 60 to 70 million elements with Sorting on a system with 768MB. If you do try Sorting, I suggest only using the Quicksort options.

kaikow thank you for yopur suggest but I tried to run it at work with 2gb ram and 2gb pagefile size but the error persists.cbr86, do you mean I should dimension the array up to....say 500.000 values and then write the results and start again from where I stopped? Well, the problem is that I have to sort the array in order to get the maximum sharpe basket while storing the stocks componing it. It would be more complicate and what's more a lot more time consuming to follow the procedure you described.Actually I was thinkig to divide the stocks in to group of fifty,running the routine separately and then comparing the two optimal basket sbut.....I'd lose a lot combinations in this ways as I could just compare 230.000*2 basket instead of 3,9 millions.Any other suggest?Thanks

But sharpe ratio is only one number - no need to be multidimesional.Index the baskets so that in basket_array(i) contains composition of basket i. sharpe_array(i) contains sharpe ratio of the corresponding basket. Then write a sort function which would compare sharpe_array(i) > sharpe_array(j) and in case true switch places not only in sharpe_array but also in basket_array. Complexity n^2 I suppose - VB should take time but do it Sort function can be optimized, but it's way too early in the morning for that now

Sounds like you need to redesign the algorithm.Source of code for very efficient sort functions in VB.

Also, I'd look at the way you are dimensioning the array.ReDim x(N)or Dim x(N)is vastly less efficient thatDim x(N) as double

Two cheeky comments for what they're worth:1)Dim x(N) as double ..would only work is you know up front how many elements you're going to have. I guess in the given example you may not always know this.2)Have you tried using ADO for this? It's much better suited for sets of data this size.I've just run the following test using an ADO recordset, creating 3,921,225 recordsets (cotaining 5 doubles) takes around 1/2GB memory this way. Also, you get quick sorting (sorting that's quick, not a quicksort, that is) for relatively little cost (takes around 2 secs + 50 MB to sort on any given field). Building the data took a minute or so on my box (3GHz x2 Hyperthreaded Xeon w/2 GB RAM)Sub test() Dim a As ADODB.Recordset Set a = New Recordset Dim i,j,arr For i = 1 To 5 a.Fields.Append "Field" & i, adDouble Next a.Open arr = Array("Field1", "Field2", "Field3", "Field4", "Field5") For j = 1 To 3921225 a.AddNew arr, Array(Rnd(), Rnd(), Rnd(), Rnd(), Rnd()) NextEnd Sub

1) Well i can dim n(X) as double as I know how many basket I'm computing (it's just to apply the combination formula). Just need to know how many stocks are you analisying(50) and how many elements constituite the basket (4)2) Actually I don't know ADO: is it easy to learn it (i'm not a programmer, just know vba)? Where can I get some ADo for dummies stuff?Thank you to all for your suggestions!

Since you will not know in advance how many array elements you will need, you will have to use ReDim.ReDim is clearly explained in the VB 6 Help.If you are just doung ordinary mathematical calculations on elements of an array, using ADO, or whatever, database would just add needless overhead.

QuoteOriginally posted by: kaikowIf you are just doung ordinary mathematical calculations on elements of an array, using ADO, or whatever, database would just add needless overhead.The overhead is pretty minimal, and the benefit is that you get much faster sorting, searching and filtering for free, and it's easy. Why make life difficult for yourself? If we wanted to get pernickety about performance or overhead, then we probably shouldn't be doing this in VBA, but in c++. I'm just suggesting this as an option to bismarx - it's what I'd do in his situation, as it offers much more flexibility than arrays. ADO is pretty trivial to learn, just add a reference to 'Microsoft Active Data Objects Library x.x (this is probably 2.5 or something for you)'. You can use the code I've posted as an example of how to load up the data, or ms provides good docs at MSDN ADO docs. Or you can PM me, I may be able to help.

If one needs the searching and filtering, then ADO might be worthwhlile, but if only sorting is required, I suspect that it is relatvely easy to beat the pants off a built-in sorting routine, even using VB.I too felt that built-in sorting would be faster, ny I was awakened by the performance tests in Sorting.Perhaps, later this year, I'll add an ADO sort to the comparison.Not to mention that when sorting multidimensional arrays, one wants to sort pointers to the data.