I wonder if Excel is using up more and more memory and eventually failing
because it can't allocate any more.
You'd possibly be able to see this in the processes screen of the Windows
task manager.
By running the sort > 1,000 times, you are doing something EXTREMELY
unusual, which there would be no reason for the developers to test. Why do
you need to do it?
Regards, Dave S
----- Original Message -----
From: "mickey11030" mickey11030@yahoo.com>
To: ExcelVBA@yahoogroups.com>
Sent: Sunday, January 13, 2013 1:34 PM
Subject: [ExcelVBA] Puzzling Behavior
>I am running a program that pulls selected fields from several worksheets
>(all in one workbook) to produce new worksheet with the combined data. At
>one point I sort a sheet with a few (< 15) rows. The program runs and
>performs the sort many times, but then halts in the sort routine. I created
>the sort routine by recording a macro.
>
> Sub SortSizes(SizeCounter)
> Sheets("Sizes").Activate
> Sheets("Sizes").Range("A1").Select
> ActiveWorkbook.Worksheets("Sizes").Sort.SortFields.Clear
> ActiveWorkbook.Worksheets("Sizes").Sort.SortFields.Add
> Key:=Range("A1:A" & SizeCounter), _
> SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
> xlSortTextAsNumbers
> With ActiveWorkbook.Worksheets("Sizes").Sort
> .SetRange Range("A1:A10")
> .Header = xlNo
> .MatchCase = False
> .Orientation = xlTopToBottom
> .SortMethod = xlPinYin
> .Apply
> End With
> End Sub
>
> The first time it halted after about 1,400 executions on the statement
> Sheets("Sizes").Activate. The second time it halted after about 1,700
> executions on the .Apply statement. In both cases the error message was:
> Run-time error '50290'/Application-defined or object-defined error. The
> Sizes sheet is there, with about 10 sizes to be sorted.
>
> Note: I just noticed I forgot to change the .SetRange Range("A1:A10) to
> Range("A1:A" & SizeCounter), but I can't see how that would cause an error
> on the Activate or Apply statements
>
> Can anyone offer any explanation or even some experiments I could run to
> get to the bottom of this mystery?
>
> I'm running Excel 2007 on Windows 7 system.
>
> Thanks for any help
> Michael Trombetta
>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1430 / Virus Database: 2638/5527 - Release Date: 01/12/13
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
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