Forum Home
Press F1
Thread ID: 40806	2003-12-19 01:51:00	How fast can you go? (Excel relistings)	rugila (214)	Press F1

Post ID	Timestamp	Content	User
201688	2003-12-22 07:14:00	> The way I see it is > > For i = 1 To blk > should read > > For i = 1 To blk - 1 > because I think you will find that I is > starting at zero and not 1 Hi MB, no thats not correct. The variable i has not been initialized until this line and as it has for i = 1 to blk then i begins at one because the starting number for the For/Next loop has been explicitly declared. You would be correct if it was For i = i To blk Compare the i variable to the j variable in this to see what I mean... Sub test() Dim i As Integer, j As Integer For i = 1 To 3 MsgBox " The value of i is... " & i Exit For Next i For j = j To 3 MsgBox " The value of j is... " & j Exit For Next j End Sub cheers, parry	parry (27)
201689	2003-12-22 08:00:00	I'm sorry about that one. I have been a few months without programming and when I saw it I thought it was the old trick of starting from 0 hence requiring the - 1 but as soon as I had posted I realised my mistake.	mikebartnz (21)
201690	2003-12-22 08:31:00	No worries. :-)	parry (27)
201691	2003-12-27 13:46:00	Hi Parry (and any others (????) interested in such geeky thread, I had another look at this stuff and here is my view. >Hi Rugila, beat this then - 4 secs!!! The best I could get on your one was 12 secs >As you can see this (Tushar Mehta function approach) uses Excels built in functionality so doesn’t require VBA and is the quickest solution. Parry, try the following submerge sub on your machine. 1. Use a list of 65,340 filenames from A1 down on sheet 1 (so as to provide fair comparison with the Tushar Mehta function approach). 2. I've put in a timer for how long this takes. Since Excel can't record times more accurately than the nearest second, the code to relists the same column 100 or whatever times (enter 100, or however many runs you want in B1). 3. I've also given below a tushar sub to do exactly the same thing for comparison purposes. i.e. both subs relist 65,340*100 (some 6 and a half million) filenames and record the minutes and seconds taken in C1. If you think my rendition of tushar's method is unfair to him or you in speed, convenience sense or any other way, then you will no doubt say so. 4. The tushar sub relists on sheet 2, doesn't include page numbers and is somewhat inflexible about non-exact pages. Submerge relists on sheet1, does include page numbers (thus taking more time) and works fine with non-exact page numbers. 5. Also run your normal2 sub thru 100 times to see how long. **************************** Sub merge() 'coz maybe I'm sinking under all this weighty stuff Dim tt As Date, a As Integer tt = Time For a = 1 To [b1] [b2] = " run " & a Dim nv, nc, nr, j, p, k, g, h As Integer Dim x(1250) As Variant nv = 0 nr = 55: nc = 9 [c:m].ClearContents Do While IsEmpty(Cells(nv * nr + 1, 1)) = False x(nv + 1) = Range(Cells(nr * nv + 1, 1), Cells(nr * (nv + 1), 1)) nv = nv + 1 Loop k = WorksheetFunction.RoundUp(nv / nc, 0) For j = 0 To k - 1 Cells(2 + j * (nr + 1), 3) = " page " & (j + 1) & " of " & k For p = 1 To nc g = 3 + j * (nr + 1): h = p + 2 Range(Cells(g, h), Cells(g + nr - 1, h)) = x(p + j * nc) Next p, j Next a [c1] = Time - tt: [c1].NumberFormat = " mm:ss " : [d1] = " mm:secs taken - merge " End Sub ****************************************** Sub tushar() Dim tt As Date, a As Integer tt = Time For a = 1 To Sheet1.[b1] Sheet1.[b2] = " run " & a Sheet3.[c:m].ClearContents Dim q As String q = " =INDIRECT( " " 'Sheet1'!R " " & ((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT( (ROW()-1)/55)*(55*9))) & " " C1 " " ,FALSE) " Sheet3.[a1:i7260] = q Next a Sheet1.[c1] = Time - tt: Sheet1.[c1].NumberFormat = " mm:ss " : [d1] = " mm:secs taken - tushar " End Sub ********************************************** Interested in any comments you may have. Cheers, Rugila.	rugila (214)
201692	2003-12-28 03:02:00	Very cool, I guess you win then. :-) The times/op I get seemed to reduce when # iterations got larger for the Merge one but the opposite for Tushars. This is what I get for 100... Merge = 1.25 secs (1.5 secs for 10) Tushar = 4.35 secs (4.0 secs for 10) I think its a pretty fair conversion of Tushars method into code. I also tried the Merge operation into sheet3 just in case, but if anything it was quicker again (no screen updating). I see you are putting the values into a one dimensional array then putting the values in a column at a time. I suppose it can be quicker to do little chunks from an array than one big dump. This line intrigues me. It seems to me as though a range of several cells is getting values from just one array element - ie the size of the range doesnt match the array elements your pointing to. Range(Cells(g, h), Cells(g + nr - 1, h)) = x(p + j * nc) I tried to replicate it with this, but it puts the same value in the cells as I expected it would... Sub testarray() Dim i As Integer, MyArray(10) For i = 1 To 10 MyArray(i) = i Next i 'This only puts the value of element #6 into all the 'cells rather than values from element #6-#10 Range("F1:F5") = MyArray(6) End Sub Please tell me what Im missing here. PS: On Tushars formula I think its good to have a formula option as not everyone can write code so thats why I liked it. As its a lookup formula perhaps the calculation of cells is causing it to take a performance hit. I dont see page numbers as a biggie unless you are viewing on screen as page footers will take care of the numbering. All the same, it does look nicer.	parry (27)
201693	2003-12-28 13:34:00	Parry (Mike and any others), Thanks for the concession as to speed, although I was rather hoping the competition would get tougher, as I had several ideas as to how to do this even faster. Probably never do anything further about it now though. >The times/op I get seemed to reduce when # iterations got larger for the Merge one but >the opposite for Tushars. This is what I get for 100... >Merge = 1.25 secs (1.5 secs for 10) >Tushar = 4.35 secs (4.0 secs for 10) On my machine (athlon xp2000+ with 512 ram) 100 runs of tushar took 64 seconds (0.64 secs per run) whereas merge took 59 secs (0.59 per run). Tushar is 8.5% slower, not nearly as bad as your figures indicate. Tried 600 runs, Tushar took 386 seconds (0.643 secs per run - same really), merge took 354 secs (=0.590 per run) again 8.5% difference. The normal2 version in which you improved my earlier code took 153 secs for 100 runs, about 2.56 times or 156% slower than merge. Regarding your try at replication, I think this was difficult partly coz I didn't notate my code. Try the following modified version, and I hope it might be clearer what I did. *********************************** Sub testarray() Dim i As Integer, MyArray(10), x(2, 5, 1) For i = 1 To 10 MyArray(i) = i Cells(i, 5) = i Next i For r = 1 To 2 x(r, 5, 1) = Range(Cells(5 * (r - 1) + 1, 5), Cells(5 * r, 5)) Range(Cells(1, 6 + r), Cells(5, 6 + r)) = x(r, 5, 1) Next r 'This only puts the value of element #6 into all the 'cells rather than values from element #6-#10 Range("F1:F5") = MyArray(6) End Sub ***************************************** You can get x(r,5,1) directly from MyArray without reading it from the Excel cells, but the way the problem was set up the 5,1 part was fixed so is omitted for shorthand. With Mike's filenames, taking columns 55 long was actually x(r,55,1) where r = 1 to nv, so I just called them x(r). I think your difficulty with this was because x(r) looks like a one dimensional array, but actually I used it in the problem as a three dimensional array (or 2d depending on how you interpret the 1 in x(r,55,1)). If you replace x(r,5,1) by x(r) in the above then it should make no difference, and this is how the Excel VBA does it. I agree it can be confusing if not explained, but I was only trying to write a code to beat Tushar, not to explain it. If you like Tushar's contribution as a function rather than a macro, why not just do ****************************** Function tushfun() 'or whatever you want to call it Dim q As String q = "=INDIRECT(""'Sheet1'!R""&((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT( (ROW()-1)/55)*(55*9)))&""C1"",FALSE)" Sheet3.[a1:i7260] = q End Function ***************************************** and it will appear in your list of user functions and be available just like any other function. Saves keying in that horrible formula in the formula bar. On the other hand, why not do this for merge also, or instead of, since it's faster and has some other advantages. Such as page numbers if you want them - they can easily be omitted if you don't. I agree they are no big deal, but just an example. Either approach can be used a a sub and either can be used as a function. Do you really think the Tushar approach has any advantage?	rugila (214)
201694	2003-12-28 22:15:00	> If you like Tushar's contribution as a function > rather than a macro, why not just do > ****************************** > Function tushfun() 'or whatever you want to call it > Dim q As String > q = > "=INDIRECT(""'Sheet1'!R""&((COLUMN()-1)*55+MOD((ROW()- > ),55)+1+(INT( (ROW()-1)/55)*(55*9)))&""C1"",FALSE)" > Sheet3.[a1:i7260] = q > End Function > ***************************************** > and it will appear in your list of user functions and > be available just like any other function. > Saves keying in that horrible formula in the formula > bar. Yup I could do, although I would create arguments for the variables bits in the formula like the column & row numbers. I am slowly copying Excel info I have gathered to my web site so I would have this formula broken down and explained there so if I needed it I could grab it easily. Same goes for your procedure. > On the other hand, why not do this for merge also, or > instead of, since it's faster and has some other > advantages. > Such as page numbers if you want them - they can > easily be omitted if you don't. I agree they are no > big deal, but just an example. Either approach can be > used a a sub and either can be used as a function. Do > you really think the Tushar approach has any > advantage? To me this is not Tushar vs Merge procedure, but rather a general 'when would I use formulas vs code' discussion. Both have their pros and cons. I believe if you can do something with a formula that meets your needs then this is the way to go. Excel is designed for formulas, so if your not using them then arent you only using a small percentage of its benefits? However, formulas can only do so much so if I wanted page #'s and to colour every alternate page and do this and that then code is the way to go, provided writing it is worth the time and effort. All depends on the frequency too - If Im only going to do this as a one off then I might not bother with code. Trouble is I just like code so I guess I need to get better at writing complex formulas to appreciate their benefit more as I tend to think 'how can I do this in code' rather than 'whats the formula I need'. Because of this I would probably use the code as it will do as expected and theres only a couple of variables needing to be changed to adjust the size of the pages. If Im going to use it often I would create an Add-In. Users can see a formula, but with code they invariably dont have a clue what it means. If users can do this themselves with a formula then why not let them? Of course this isnt your every day formula so it would need to be explained properly. I would say users would prefer the code because they dont have to think. Just click this button and it will do everything for you vs I have to think how many cells I need to select and what bits I need to change in the formula to indicate the #rows and #columns. And of course theres what happens when the data doesnt fill up a whole page. I wouldnt be surprised if Tushar could fix that though. I will need to look at your code to understand the arrays bit so I might need to ask you a couple of questions later. cheers :-)	parry (27)
201695	2003-12-28 22:38:00	Noted your comments, Parry. Maybe a remark about my own background might help you better assess my coding. I don't really know much about programming or coding, even in VB, and the elementary nature of some of my stuff should be pretty obvious. I have learned some things from you about this, which is partly why I have continued with the thread. My backround, at least as far as this sort of stuff is concerned, is very strongly in mathematics and logic. I understand the meaning and structure of arrays (matrices) very well, and how they might be used to solve elementary problems such as the one put up by Mike and also much more complex problems. My problem is how to get Excel and VB to do the things I want done with arrays, sometimes Google is of use, sometimes it runs out of ideas pretty fast. I suppose my other problem, if you call it that, is that I don't do much of this sort of stuff now anyway, and basically prefer to spend my time on other things. However, a challenge is always interesting. Cheers, Rugila	rugila (214)
201696	2003-12-29 07:55:00	> used to solve elementary problems such as the one put > up by Mike and also much more complex problems. My problem was elementary??? ?:| :D LOL Mike.	Mike (15)
201697	2003-12-29 08:44:00	Well :| ? Matter of perspective I suppose :D But I hope you regard your problem, however categorised, as having been fairly, adequately and compehensively solved! B-)	rugila (214)
1 2 3 4