Lisa,
Sorry I've been delayed in responding to your sorting question.
I see that others have stepped up to provide assistance.
I may be able to help clear up some of the confusion.
There was a question with regard to preference for sorting from Least Significant to Most Significant.
Let's say you had records with two fields:
Z : 1
A : 3
C : 2
When sorting on the first (most significant) column:
Z is compared to A, and the records are reversed, resulting in:
A: 3
Z : 1
C : 2
Z is compared to C and the records are reversed, resulting in:
A: 3
C : 2
Z : 1
An additional iteration would show that the Most Significant field is now sorted.
Sorting the Next field:
3 is compared to 2, the records are reversed, resulting in:
C : 2
A : 3
Z : 1
You can immediately see that the sort order has been invalidated.
But, if you were to have sorted the SECOND field (least significant)
Then, by the time you get to the MOST significant criteria, then changes made to the lesser significant fields would be irrelevant.
Now,
I understand that you have data stored in an array.
The source of the array is irrelevant.
You COULD extract this data and store it in an Excel worksheet, sort it, then re-load the array.
But you SHOULD be able to process the array without loading another application.
I have created two separate macro scenarios.
In one scenario, I sort the array elements themselves. (it requires nested If statements, since you will only sort secondary criteria if the previous criteria is the same)
I also created a single-dimension array in which I concatenated the array records IN THE ORDER OF SORT CRITERIA. Then, I reloaded the array.
I was able to get both scenarios to work to get the sort order as described in your file (I think)
The difference is that: sorting the original array (388 records) took 32 seconds.
Sorting the Concatenated data took only 13 seconds.
This may not be significant NOW, but perhaps if the array gets much larger.
I also think that it may be possible to create a more "flexible" function with the second option.
That is, it might be able to pass an array to the function that will control the order in which the array elements are concatenated.
I'd like to send you the file so that you can look at it… if either approach looks like it might be interesting, you can post it as a solution.
Can I send you the file?
Paul
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> Lisa,
> The current data is over 300 lines...
> In the future, how much data are we talking about?
> Could it conceivable exceed 1000? 10,000?
> Only curious because it may change what approach I'd take.
>
> as for "taking advantage of being a moderator":
> I'm sure the Company Car, the Condo on the Beach (with free maid service and
> stocked mini-bar)Â and the use of the company jet for vacations are sufficient
> perks for your job as moderator... posting your own attachments would be WAY
> over the top! (lol)
>
> Unless the other moderators would be jealous that you're actually getting some
> WORK done!
> Then, I suppose the slight delay in rendering assistance can be tolerated... ;)
> Â
> Paul
> -----------------------------------------
> “Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.†- John Wesley
> -----------------------------------------
>
>
>
>
> ________________________________
> From: "1z@..." <1z@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Fri, January 6, 2012 12:44:52 PM
> Subject: Re: [ExcelVBA] Re: Sorting in VBA
>
> Â
>
>
> Paul,
>
> Sure... Makes a *lot* of sense.
> "Fresh" is Good.
> I've "started anew" a number of times now and am still not sure about the sort
> creating the the same as Excel.
>
> I'm going to ask for a workbook to be posted. not going to do it myself because
> that may be seen as taking advanage of being a moderator... :-).
>
> The data is over 300 lines.. It's a dump of the array I want to sort on
> "columns" / elements 1 then 0 then 6 then 4.
>
> As extra information those are.. Project, Called Procedure, Calling
> Module,Calling Procedure respectively... by which you may have guessed it's to
> do with the VBE. I'm trying to populate a treeview with Called procedures,
> number of times called and where they are called from.
>
> The sheet SORT has all of the data in ... Raw format, Excel Sorted format, Space
> for Proc sorted format - which is currently a copy of the excel sorted data, and
> lastly a space for results which is just =If(Exec Sorted<>Proc Sorted,"No","")
>
> I've included a proc to dump the arrays to a sheet and the "ordinary" sort proc
> I use.
>
> And Thanks
> Lisa
>
>
>
> Subject: [ExcelVBA] Re: Sorting in VBA
>
> Lisa,
> an you post some sample data? just a few records.
> oth Unsorted and Sorted?
> I think I'd be better off trying to look at it "fresh" rather
> han trying to work through code that ISN'T working and try to
> igure out what it was INTENDED to do that it's not...
> does that make sense?
> Paul S
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar