> Well on the read side we definitely match except he makes the point > of saying he is not using SET. > Why is that? What is the difference? I assume he's contrasting with the "Use a range object" routine where DataRange was a Range object, and thus needed Set for it's assignment. In the "Use a variant type variable" routine, DataRange is as you know being used to store an array of values, thus Set wouldn't work. -- Set is for objects only.
Agreed, don't worry if you're satisfied with the performance. You can always massage things later, at your leisure, if desired. Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 4:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff <a...@lovetour.info> wrote: > Just came across a simple example using an array to manipulate cell values, > then put only values that got changed back to the worksheet. It also has a > range/index equivalent sample, and a range/selection/offset example (this is > often the sort of thing first-time macro writers end up with after modifying > recorded macros). The read and write speed of the three methods are > compared. > > > > Excel Blog: What is the fastest way to scan a large range in Excel? > > > > It doesn't cover use of Evaluate to perform operations on a range without a > VBA loop, but it's a good article with straightforward examples. > > > > Asa > > > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] > On Behalf Of Domain Admin > Sent: Wednesday, April 11, 2012 10:49 AM > To: excel-macros@googlegroups.com > Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire > spreadsheet? > > > > The msgbox example seems to have an error but with other reading I > > think maybe but not certain I got it. > > > > I got the array version of the rounding to work. The evaluate version > > is slightly faster but the array version > > way faster than the for each or for index versions. > > > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will > not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > ---------------------------------------------------------------------------- -------------------------- > To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
