Hence my confusion. Here is the code I am currently using. Sounds
like you are saying the SET should
cause it to fail.
But it only works if I use the SET. Otherwise it will not compile.
' First let's copy everything we need into an array for efficiency
With Sheets(RawData)
Set tmprange = .Range(.Cells(2, BarOpen),
.Cells(.UsedRange.Rows.count, EContango))
End With
On Sun, Apr 15, 2012 at 1:02 PM, Asa Rossoff <a...@lovetour.info> wrote:
>> 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
--
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
