Hi all,

I know everyone is looking for latest version, but I work as a developer and
my customer use Excel 2000. He asked me to evaluate the cost of an upgrade to
Excel 2003, so here I am.

My program must fill 5000*255 cells.
I use the VBA method "Resize" to select all the range, then "Value" and set
them with an array containing all the datas.
AFAIK, this is the most effective way to do this.


The problem I have is about the performances :
this code works both on Excel 2000 and on Excel 2003 but on the same
computer respectively need 3 sec and 9 sec.
This difference is not acceptable by my customer.


Could anyone tell me if this lack of perf is a known issue when upgrading
and if there is a workaround ?
I see on the net that this is a computer problem, but I'm afraid that they
wouldn't change their hardware for more competitive ones.....


Thanks for giving me any pointers and have a nice day !


Manu, hope I made myself clear enough, I'm not a good english speaker !

If you were talking about the difference between XL2007 and earlier
versions, then you would probably see marked reduction in speed as numerous
posts here have related.
Without knowing exactly what you are doing, the test code below does run
faster in XL2K:
You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
overhead, but with the code below, this reduced all results only by about 1
second.

XL2K XL2002
38 sec 53sec

Private Sub CommandButton1_Click()
Dim arr(1 To 5001, 1 To 256) As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim OutRange As Range
Dim StartTime As Date

Const RUNS As Long = 10

StartTime = Now()

For i = 1 To RUNS
For j = LBound(arr, 1) To UBound(arr, 1)
For k = LBound(arr, 2) To UBound(arr, 2)
arr(j, k) = Rnd()
Next
Next

Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
UBound(arr, 2) - LBound(arr, 2))
With OutRange
.ClearContents
.Value = arr()
End With

Next

Debug.Print "**** With Excel " & Application.Version
Debug.Print , "Execution of " & RUNS & " runs of " & OutRange.Cells.Count &
" cells"
Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & " seconds"
Debug.Print "****"
End Sub

NickHK

"ecanet" <(E-Mail Removed)> wrote in message
news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
> Hi all,
>
> I know everyone is looking for latest version, but I work as a developer
and
> my customer use Excel 2000. He asked me to evaluate the cost of an upgrade
to
> Excel 2003, so here I am.
>
> My program must fill 5000*255 cells.
> I use the VBA method "Resize" to select all the range, then "Value" and
set
> them with an array containing all the datas.
> AFAIK, this is the most effective way to do this.
>
>
> The problem I have is about the performances :
> this code works both on Excel 2000 and on Excel 2003 but on the same
> computer respectively need 3 sec and 9 sec.
> This difference is not acceptable by my customer.
>
>
> Could anyone tell me if this lack of perf is a known issue when upgrading
> and if there is a workaround ?
> I see on the net that this is a computer problem, but I'm afraid that they
> wouldn't change their hardware for more competitive ones.....
>
>
> Thanks for giving me any pointers and have a nice day !
>
>
> Manu, hope I made myself clear enough, I'm not a good english speaker !

That's odd Nick.

I have just run your tests and I got

2K: 23 secs
XP/2002: 16 secs

AT first I thought you had said 2003 so I was going to post that oddly 2002
was faster, but when I saw that you said 2002 I repeated it, and got 17 secs
this time.

?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you were talking about the difference between XL2007 and earlier
> versions, then you would probably see marked reduction in speed as
> numerous
> posts here have related.
> Without knowing exactly what you are doing, the test code below does run
> faster in XL2K:
> You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
> overhead, but with the code below, this reduced all results only by about
> 1
> second.
>
> XL2K XL2002
> 38 sec 53sec
>
> Private Sub CommandButton1_Click()
> Dim arr(1 To 5001, 1 To 256) As Variant
> Dim i As Long
> Dim j As Long
> Dim k As Long
> Dim OutRange As Range
> Dim StartTime As Date
>
> Const RUNS As Long = 10
>
> StartTime = Now()
>
> For i = 1 To RUNS
> For j = LBound(arr, 1) To UBound(arr, 1)
> For k = LBound(arr, 2) To UBound(arr, 2)
> arr(j, k) = Rnd()
> Next
> Next
>
> Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
> UBound(arr, 2) - LBound(arr, 2))
> With OutRange
> .ClearContents
> .Value = arr()
> End With
>
> Next
>
> Debug.Print "**** With Excel " & Application.Version
> Debug.Print , "Execution of " & RUNS & " runs of " & OutRange.Cells.Count
> &
> " cells"
> Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & " seconds"
> Debug.Print "****"
> End Sub
>
> NickHK
>
> "ecanet" <(E-Mail Removed)> wrote in message
> news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
>> Hi all,
>>
>> I know everyone is looking for latest version, but I work as a developer
> and
>> my customer use Excel 2000. He asked me to evaluate the cost of an
>> upgrade
> to
>> Excel 2003, so here I am.
>>
>> My program must fill 5000*255 cells.
>> I use the VBA method "Resize" to select all the range, then "Value" and
> set
>> them with an array containing all the datas.
>> AFAIK, this is the most effective way to do this.
>>
>>
>> The problem I have is about the performances :
>> this code works both on Excel 2000 and on Excel 2003 but on the same
>> computer respectively need 3 sec and 9 sec.
>> This difference is not acceptable by my customer.
>>
>>
>> Could anyone tell me if this lack of perf is a known issue when upgrading
>> and if there is a workaround ?
>> I see on the net that this is a computer problem, but I'm afraid that
>> they
>> wouldn't change their hardware for more competitive ones.....
>>
>>
>> Thanks for giving me any pointers and have a nice day !
>>
>>
>> Manu, hope I made myself clear enough, I'm not a good english speaker !
>
>

Bob,
That is strange, unless it has something to do with the maths processor/CPU
stuff.
As you see, my system is relatively old and slow, compared to yours, but
(this code anyway) certainly runs faster in XL2K compared to XLXP/2002.
Don't have higher versions to test.

So it would seems XL version is not the only consideration.

NickHK

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> That's odd Nick.
>
> I have just run your tests and I got
>
> 2K: 23 secs
> XP/2002: 16 secs
>
> AT first I thought you had said 2003 so I was going to post that oddly
2002
> was faster, but when I saw that you said 2002 I repeated it, and got 17
secs
> this time.
>
> ?
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
addy)
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > If you were talking about the difference between XL2007 and earlier
> > versions, then you would probably see marked reduction in speed as
> > numerous
> > posts here have related.
> > Without knowing exactly what you are doing, the test code below does run
> > faster in XL2K:
> > You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
> > overhead, but with the code below, this reduced all results only by
about
> > 1
> > second.
> >
> > XL2K XL2002
> > 38 sec 53sec
> >
> > Private Sub CommandButton1_Click()
> > Dim arr(1 To 5001, 1 To 256) As Variant
> > Dim i As Long
> > Dim j As Long
> > Dim k As Long
> > Dim OutRange As Range
> > Dim StartTime As Date
> >
> > Const RUNS As Long = 10
> >
> > StartTime = Now()
> >
> > For i = 1 To RUNS
> > For j = LBound(arr, 1) To UBound(arr, 1)
> > For k = LBound(arr, 2) To UBound(arr, 2)
> > arr(j, k) = Rnd()
> > Next
> > Next
> >
> > Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
> > UBound(arr, 2) - LBound(arr, 2))
> > With OutRange
> > .ClearContents
> > .Value = arr()
> > End With
> >
> > Next
> >
> > Debug.Print "**** With Excel " & Application.Version
> > Debug.Print , "Execution of " & RUNS & " runs of " &
OutRange.Cells.Count
> > &
> > " cells"
> > Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
seconds"
> > Debug.Print "****"
> > End Sub
> >
> > NickHK
> >
> > "ecanet" <(E-Mail Removed)> wrote in message
> > news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
> >> Hi all,
> >>
> >> I know everyone is looking for latest version, but I work as a
developer
> > and
> >> my customer use Excel 2000. He asked me to evaluate the cost of an
> >> upgrade
> > to
> >> Excel 2003, so here I am.
> >>
> >> My program must fill 5000*255 cells.
> >> I use the VBA method "Resize" to select all the range, then "Value" and
> > set
> >> them with an array containing all the datas.
> >> AFAIK, this is the most effective way to do this.
> >>
> >>
> >> The problem I have is about the performances :
> >> this code works both on Excel 2000 and on Excel 2003 but on the same
> >> computer respectively need 3 sec and 9 sec.
> >> This difference is not acceptable by my customer.
> >>
> >>
> >> Could anyone tell me if this lack of perf is a known issue when
upgrading
> >> and if there is a workaround ?
> >> I see on the net that this is a computer problem, but I'm afraid that
> >> they
> >> wouldn't change their hardware for more competitive ones.....
> >>
> >>
> >> Thanks for giving me any pointers and have a nice day !
> >>
> >>
> >> Manu, hope I made myself clear enough, I'm not a good english speaker !
> >
> >
>
>

Thanks for replying so fast !
I tested your code (on excel2003).
I'm afraid I don't understand, the printed time is very good (<3s), but the
real time (after refresh of the grid) is much different (>24s).
I noticed that you also used the Value property, as I do.

The calculation time isn't the problem, but the populating of each cell is.






"NickHK" wrote:

> If you were talking about the difference between XL2007 and earlier
> versions, then you would probably see marked reduction in speed as numerous
> posts here have related.
> Without knowing exactly what you are doing, the test code below does run
> faster in XL2K:
> You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
> overhead, but with the code below, this reduced all results only by about 1
> second.
>
> XL2K XL2002
> 38 sec 53sec
>
> Private Sub CommandButton1_Click()
> Dim arr(1 To 5001, 1 To 256) As Variant
> Dim i As Long
> Dim j As Long
> Dim k As Long
> Dim OutRange As Range
> Dim StartTime As Date
>
> Const RUNS As Long = 10
>
> StartTime = Now()
>
> For i = 1 To RUNS
> For j = LBound(arr, 1) To UBound(arr, 1)
> For k = LBound(arr, 2) To UBound(arr, 2)
> arr(j, k) = Rnd()
> Next
> Next
>
> Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
> UBound(arr, 2) - LBound(arr, 2))
> With OutRange
> .ClearContents
> .Value = arr()
> End With
>
> Next
>
> Debug.Print "**** With Excel " & Application.Version
> Debug.Print , "Execution of " & RUNS & " runs of " & OutRange.Cells.Count &
> " cells"
> Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & " seconds"
> Debug.Print "****"
> End Sub
>
> NickHK
>
> "ecanet" <(E-Mail Removed)> wrote in message
> news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
> > Hi all,
> >
> > I know everyone is looking for latest version, but I work as a developer
> and
> > my customer use Excel 2000. He asked me to evaluate the cost of an upgrade
> to
> > Excel 2003, so here I am.
> >
> > My program must fill 5000*255 cells.
> > I use the VBA method "Resize" to select all the range, then "Value" and
> set
> > them with an array containing all the datas.
> > AFAIK, this is the most effective way to do this.
> >
> >
> > The problem I have is about the performances :
> > this code works both on Excel 2000 and on Excel 2003 but on the same
> > computer respectively need 3 sec and 9 sec.
> > This difference is not acceptable by my customer.
> >
> >
> > Could anyone tell me if this lack of perf is a known issue when upgrading
> > and if there is a workaround ?
> > I see on the net that this is a computer problem, but I'm afraid that they
> > wouldn't change their hardware for more competitive ones.....
> >
> >
> > Thanks for giving me any pointers and have a nice day !
> >
> >
> > Manu, hope I made myself clear enough, I'm not a good english speaker !
>
>
>

Not sure what you mean by the difference between the 3 sec and 24 sec, using
that code I posted.
The printed time will when everything has finished, assuming you do not have
anything else running.
Note that there is no calculation of dependent cells on the XL sheet
involved in this, which may not be the your case.

Yes, the time consuming part is dumping the large array to the WS. If you
can avoid using such a large array, by splitting it up and only dumping what
is need/changed, you will speed up the routine.

NickHK

"ecanet" <(E-Mail Removed)> wrote in message
news:34C258C2-C26C-427C-8ADD-(E-Mail Removed)...
> Thanks for replying so fast !
> I tested your code (on excel2003).
> I'm afraid I don't understand, the printed time is very good (<3s), but
the
> real time (after refresh of the grid) is much different (>24s).
> I noticed that you also used the Value property, as I do.
>
> The calculation time isn't the problem, but the populating of each cell
is.
>
>
>
>
>
>
> "NickHK" wrote:
>
> > If you were talking about the difference between XL2007 and earlier
> > versions, then you would probably see marked reduction in speed as
numerous
> > posts here have related.
> > Without knowing exactly what you are doing, the test code below does run
> > faster in XL2K:
> > You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
> > overhead, but with the code below, this reduced all results only by
about 1
> > second.
> >
> > XL2K XL2002
> > 38 sec 53sec
> >
> > Private Sub CommandButton1_Click()
> > Dim arr(1 To 5001, 1 To 256) As Variant
> > Dim i As Long
> > Dim j As Long
> > Dim k As Long
> > Dim OutRange As Range
> > Dim StartTime As Date
> >
> > Const RUNS As Long = 10
> >
> > StartTime = Now()
> >
> > For i = 1 To RUNS
> > For j = LBound(arr, 1) To UBound(arr, 1)
> > For k = LBound(arr, 2) To UBound(arr, 2)
> > arr(j, k) = Rnd()
> > Next
> > Next
> >
> > Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
> > UBound(arr, 2) - LBound(arr, 2))
> > With OutRange
> > .ClearContents
> > .Value = arr()
> > End With
> >
> > Next
> >
> > Debug.Print "**** With Excel " & Application.Version
> > Debug.Print , "Execution of " & RUNS & " runs of " &
OutRange.Cells.Count &
> > " cells"
> > Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
seconds"
> > Debug.Print "****"
> > End Sub
> >
> > NickHK
> >
> > "ecanet" <(E-Mail Removed)> wrote in message
> > news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
> > > Hi all,
> > >
> > > I know everyone is looking for latest version, but I work as a
developer
> > and
> > > my customer use Excel 2000. He asked me to evaluate the cost of an
upgrade
> > to
> > > Excel 2003, so here I am.
> > >
> > > My program must fill 5000*255 cells.
> > > I use the VBA method "Resize" to select all the range, then "Value"
and
> > set
> > > them with an array containing all the datas.
> > > AFAIK, this is the most effective way to do this.
> > >
> > >
> > > The problem I have is about the performances :
> > > this code works both on Excel 2000 and on Excel 2003 but on the same
> > > computer respectively need 3 sec and 9 sec.
> > > This difference is not acceptable by my customer.
> > >
> > >
> > > Could anyone tell me if this lack of perf is a known issue when
upgrading
> > > and if there is a workaround ?
> > > I see on the net that this is a computer problem, but I'm afraid that
they
> > > wouldn't change their hardware for more competitive ones.....
> > >
> > >
> > > Thanks for giving me any pointers and have a nice day !
> > >
> > >
> > > Manu, hope I made myself clear enough, I'm not a good english speaker
!
> >
> >
> >

> As you see, my system is relatively old and slow, compared to yours

Or maybe (?) Bob's is slower than yours with one or both tests stretching
into minutes (like mine). If so would need to change -
Format "s" to Format "hh:mm:ss"

Regards,
Peter T


"NickHK" <(E-Mail Removed)> wrote in message
news:uyZqK$(E-Mail Removed)...
> Bob,
> That is strange, unless it has something to do with the maths
processor/CPU
> stuff.
> As you see, my system is relatively old and slow, compared to yours, but
> (this code anyway) certainly runs faster in XL2K compared to XLXP/2002.
> Don't have higher versions to test.
>
> So it would seems XL version is not the only consideration.
>
> NickHK
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > That's odd Nick.
> >
> > I have just run your tests and I got
> >
> > 2K: 23 secs
> > XP/2002: 16 secs
> >
> > AT first I thought you had said 2003 so I was going to post that oddly
> 2002
> > was faster, but when I saw that you said 2002 I repeated it, and got 17
> secs
> > this time.
> >
> > ?
> >
> > --
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
> >
> > "NickHK" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > If you were talking about the difference between XL2007 and earlier
> > > versions, then you would probably see marked reduction in speed as
> > > numerous
> > > posts here have related.
> > > Without knowing exactly what you are doing, the test code below does
run
> > > faster in XL2K:
> > > You can use Application.ScreenUpdating/Calculation etc to reduce
Excel's
> > > overhead, but with the code below, this reduced all results only by
> about
> > > 1
> > > second.
> > >
> > > XL2K XL2002
> > > 38 sec 53sec
> > >
> > > Private Sub CommandButton1_Click()
> > > Dim arr(1 To 5001, 1 To 256) As Variant
> > > Dim i As Long
> > > Dim j As Long
> > > Dim k As Long
> > > Dim OutRange As Range
> > > Dim StartTime As Date
> > >
> > > Const RUNS As Long = 10
> > >
> > > StartTime = Now()
> > >
> > > For i = 1 To RUNS
> > > For j = LBound(arr, 1) To UBound(arr, 1)
> > > For k = LBound(arr, 2) To UBound(arr, 2)
> > > arr(j, k) = Rnd()
> > > Next
> > > Next
> > >
> > > Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
> > > UBound(arr, 2) - LBound(arr, 2))
> > > With OutRange
> > > .ClearContents
> > > .Value = arr()
> > > End With
> > >
> > > Next
> > >
> > > Debug.Print "**** With Excel " & Application.Version
> > > Debug.Print , "Execution of " & RUNS & " runs of " &
> OutRange.Cells.Count
> > > &
> > > " cells"
> > > Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
> seconds"
> > > Debug.Print "****"
> > > End Sub
> > >
> > > NickHK
> > >
> > > "ecanet" <(E-Mail Removed)> wrote in message
> > > news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
> > >> Hi all,
> > >>
> > >> I know everyone is looking for latest version, but I work as a
> developer
> > > and
> > >> my customer use Excel 2000. He asked me to evaluate the cost of an
> > >> upgrade
> > > to
> > >> Excel 2003, so here I am.
> > >>
> > >> My program must fill 5000*255 cells.
> > >> I use the VBA method "Resize" to select all the range, then "Value"
and
> > > set
> > >> them with an array containing all the datas.
> > >> AFAIK, this is the most effective way to do this.
> > >>
> > >>
> > >> The problem I have is about the performances :
> > >> this code works both on Excel 2000 and on Excel 2003 but on the same
> > >> computer respectively need 3 sec and 9 sec.
> > >> This difference is not acceptable by my customer.
> > >>
> > >>
> > >> Could anyone tell me if this lack of perf is a known issue when
> upgrading
> > >> and if there is a workaround ?
> > >> I see on the net that this is a computer problem, but I'm afraid that
> > >> they
> > >> wouldn't change their hardware for more competitive ones.....
> > >>
> > >>
> > >> Thanks for giving me any pointers and have a nice day !
> > >>
> > >>
> > >> Manu, hope I made myself clear enough, I'm not a good english speaker
!
> > >
> > >
> >
> >
>
>

I just tried it with 2003 and 2007 as well. Unfortunately these are both on
my laptop, so I repeated the 2K test as well. I don't have XP on there, so I
was unable to make that comparison (which is probably the most interesting).

I ran each three times and got these results in secs

2K: 11, 12, 11
2003: 18, 18, 18
2007: 20, 20, 20

It would seem that 2003 is considerably slower than 2K, and 2007 is slower
again.

Maybe the OP should at least check-out 2002/XP.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"NickHK" <(E-Mail Removed)> wrote in message
news:uyZqK$(E-Mail Removed)...
> Bob,
> That is strange, unless it has something to do with the maths
> processor/CPU
> stuff.
> As you see, my system is relatively old and slow, compared to yours, but
> (this code anyway) certainly runs faster in XL2K compared to XLXP/2002.
> Don't have higher versions to test.
>
> So it would seems XL version is not the only consideration.
>
> NickHK
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> That's odd Nick.
>>
>> I have just run your tests and I got
>>
>> 2K: 23 secs
>> XP/2002: 16 secs
>>
>> AT first I thought you had said 2003 so I was going to post that oddly
> 2002
>> was faster, but when I saw that you said 2002 I repeated it, and got 17
> secs
>> this time.
>>
>> ?
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>>
>> "NickHK" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > If you were talking about the difference between XL2007 and earlier
>> > versions, then you would probably see marked reduction in speed as
>> > numerous
>> > posts here have related.
>> > Without knowing exactly what you are doing, the test code below does
>> > run
>> > faster in XL2K:
>> > You can use Application.ScreenUpdating/Calculation etc to reduce
>> > Excel's
>> > overhead, but with the code below, this reduced all results only by
> about
>> > 1
>> > second.
>> >
>> > XL2K XL2002
>> > 38 sec 53sec
>> >
>> > Private Sub CommandButton1_Click()
>> > Dim arr(1 To 5001, 1 To 256) As Variant
>> > Dim i As Long
>> > Dim j As Long
>> > Dim k As Long
>> > Dim OutRange As Range
>> > Dim StartTime As Date
>> >
>> > Const RUNS As Long = 10
>> >
>> > StartTime = Now()
>> >
>> > For i = 1 To RUNS
>> > For j = LBound(arr, 1) To UBound(arr, 1)
>> > For k = LBound(arr, 2) To UBound(arr, 2)
>> > arr(j, k) = Rnd()
>> > Next
>> > Next
>> >
>> > Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
>> > UBound(arr, 2) - LBound(arr, 2))
>> > With OutRange
>> > .ClearContents
>> > .Value = arr()
>> > End With
>> >
>> > Next
>> >
>> > Debug.Print "**** With Excel " & Application.Version
>> > Debug.Print , "Execution of " & RUNS & " runs of " &
> OutRange.Cells.Count
>> > &
>> > " cells"
>> > Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
> seconds"
>> > Debug.Print "****"
>> > End Sub
>> >
>> > NickHK
>> >
>> > "ecanet" <(E-Mail Removed)> wrote in message
>> > news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
>> >> Hi all,
>> >>
>> >> I know everyone is looking for latest version, but I work as a
> developer
>> > and
>> >> my customer use Excel 2000. He asked me to evaluate the cost of an
>> >> upgrade
>> > to
>> >> Excel 2003, so here I am.
>> >>
>> >> My program must fill 5000*255 cells.
>> >> I use the VBA method "Resize" to select all the range, then "Value"
>> >> and
>> > set
>> >> them with an array containing all the datas.
>> >> AFAIK, this is the most effective way to do this.
>> >>
>> >>
>> >> The problem I have is about the performances :
>> >> this code works both on Excel 2000 and on Excel 2003 but on the same
>> >> computer respectively need 3 sec and 9 sec.
>> >> This difference is not acceptable by my customer.
>> >>
>> >>
>> >> Could anyone tell me if this lack of perf is a known issue when
> upgrading
>> >> and if there is a workaround ?
>> >> I see on the net that this is a computer problem, but I'm afraid that
>> >> they
>> >> wouldn't change their hardware for more competitive ones.....
>> >>
>> >>
>> >> Thanks for giving me any pointers and have a nice day !
>> >>
>> >>
>> >> Manu, hope I made myself clear enough, I'm not a good english speaker
>> >> !
>> >
>> >
>>
>>
>
>

LOL! I couldn't sit and wait that long Peter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
>> As you see, my system is relatively old and slow, compared to yours
>
> Or maybe (?) Bob's is slower than yours with one or both tests stretching
> into minutes (like mine). If so would need to change -
> Format "s" to Format "hh:mm:ss"
>
> Regards,
> Peter T
>
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:uyZqK$(E-Mail Removed)...
>> Bob,
>> That is strange, unless it has something to do with the maths
> processor/CPU
>> stuff.
>> As you see, my system is relatively old and slow, compared to yours, but
>> (this code anyway) certainly runs faster in XL2K compared to XLXP/2002.
>> Don't have higher versions to test.
>>
>> So it would seems XL version is not the only consideration.
>>
>> NickHK
>>
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > That's odd Nick.
>> >
>> > I have just run your tests and I got
>> >
>> > 2K: 23 secs
>> > XP/2002: 16 secs
>> >
>> > AT first I thought you had said 2003 so I was going to post that oddly
>> 2002
>> > was faster, but when I saw that you said 2002 I repeated it, and got 17
>> secs
>> > this time.
>> >
>> > ?
>> >
>> > --
>> > HTH
>> >
>> > Bob
>> >
>> > (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>> >
>> > "NickHK" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > If you were talking about the difference between XL2007 and earlier
>> > > versions, then you would probably see marked reduction in speed as
>> > > numerous
>> > > posts here have related.
>> > > Without knowing exactly what you are doing, the test code below does
> run
>> > > faster in XL2K:
>> > > You can use Application.ScreenUpdating/Calculation etc to reduce
> Excel's
>> > > overhead, but with the code below, this reduced all results only by
>> about
>> > > 1
>> > > second.
>> > >
>> > > XL2K XL2002
>> > > 38 sec 53sec
>> > >
>> > > Private Sub CommandButton1_Click()
>> > > Dim arr(1 To 5001, 1 To 256) As Variant
>> > > Dim i As Long
>> > > Dim j As Long
>> > > Dim k As Long
>> > > Dim OutRange As Range
>> > > Dim StartTime As Date
>> > >
>> > > Const RUNS As Long = 10
>> > >
>> > > StartTime = Now()
>> > >
>> > > For i = 1 To RUNS
>> > > For j = LBound(arr, 1) To UBound(arr, 1)
>> > > For k = LBound(arr, 2) To UBound(arr, 2)
>> > > arr(j, k) = Rnd()
>> > > Next
>> > > Next
>> > >
>> > > Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
>> > > UBound(arr, 2) - LBound(arr, 2))
>> > > With OutRange
>> > > .ClearContents
>> > > .Value = arr()
>> > > End With
>> > >
>> > > Next
>> > >
>> > > Debug.Print "**** With Excel " & Application.Version
>> > > Debug.Print , "Execution of " & RUNS & " runs of " &
>> OutRange.Cells.Count
>> > > &
>> > > " cells"
>> > > Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
>> seconds"
>> > > Debug.Print "****"
>> > > End Sub
>> > >
>> > > NickHK
>> > >
>> > > "ecanet" <(E-Mail Removed)> wrote in message
>> > > news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
>> > >> Hi all,
>> > >>
>> > >> I know everyone is looking for latest version, but I work as a
>> developer
>> > > and
>> > >> my customer use Excel 2000. He asked me to evaluate the cost of an
>> > >> upgrade
>> > > to
>> > >> Excel 2003, so here I am.
>> > >>
>> > >> My program must fill 5000*255 cells.
>> > >> I use the VBA method "Resize" to select all the range, then "Value"
> and
>> > > set
>> > >> them with an array containing all the datas.
>> > >> AFAIK, this is the most effective way to do this.
>> > >>
>> > >>
>> > >> The problem I have is about the performances :
>> > >> this code works both on Excel 2000 and on Excel 2003 but on the same
>> > >> computer respectively need 3 sec and 9 sec.
>> > >> This difference is not acceptable by my customer.
>> > >>
>> > >>
>> > >> Could anyone tell me if this lack of perf is a known issue when
>> upgrading
>> > >> and if there is a workaround ?
>> > >> I see on the net that this is a computer problem, but I'm afraid
>> > >> that
>> > >> they
>> > >> wouldn't change their hardware for more competitive ones.....
>> > >>
>> > >>
>> > >> Thanks for giving me any pointers and have a nice day !
>> > >>
>> > >>
>> > >> Manu, hope I made myself clear enough, I'm not a good english
>> > >> speaker
> !
>> > >
>> > >
>> >
>> >
>>
>>
>
>

I just add some Win32GetTickCounter to evaluate the
With OutRange
.ClearContents
.Value = arr()
End With
instruction

And where the Now() method tells 3sec, my GetTickCounter says 24s.....


Manu.



"NickHK" wrote:

> Not sure what you mean by the difference between the 3 sec and 24 sec, using
> that code I posted.
> The printed time will when everything has finished, assuming you do not have
> anything else running.
> Note that there is no calculation of dependent cells on the XL sheet
> involved in this, which may not be the your case.
>
> Yes, the time consuming part is dumping the large array to the WS. If you
> can avoid using such a large array, by splitting it up and only dumping what
> is need/changed, you will speed up the routine.
>
> NickHK
>
> "ecanet" <(E-Mail Removed)> wrote in message
> news:34C258C2-C26C-427C-8ADD-(E-Mail Removed)...
> > Thanks for replying so fast !
> > I tested your code (on excel2003).
> > I'm afraid I don't understand, the printed time is very good (<3s), but
> the
> > real time (after refresh of the grid) is much different (>24s).
> > I noticed that you also used the Value property, as I do.
> >
> > The calculation time isn't the problem, but the populating of each cell
> is.
> >
> >
> >
> >
> >
> >
> > "NickHK" wrote:
> >
> > > If you were talking about the difference between XL2007 and earlier
> > > versions, then you would probably see marked reduction in speed as
> numerous
> > > posts here have related.
> > > Without knowing exactly what you are doing, the test code below does run
> > > faster in XL2K:
> > > You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
> > > overhead, but with the code below, this reduced all results only by
> about 1
> > > second.
> > >
> > > XL2K XL2002
> > > 38 sec 53sec
> > >
> > > Private Sub CommandButton1_Click()
> > > Dim arr(1 To 5001, 1 To 256) As Variant
> > > Dim i As Long
> > > Dim j As Long
> > > Dim k As Long
> > > Dim OutRange As Range
> > > Dim StartTime As Date
> > >
> > > Const RUNS As Long = 10
> > >
> > > StartTime = Now()
> > >
> > > For i = 1 To RUNS
> > > For j = LBound(arr, 1) To UBound(arr, 1)
> > > For k = LBound(arr, 2) To UBound(arr, 2)
> > > arr(j, k) = Rnd()
> > > Next
> > > Next
> > >
> > > Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
> > > UBound(arr, 2) - LBound(arr, 2))
> > > With OutRange
> > > .ClearContents
> > > .Value = arr()
> > > End With
> > >
> > > Next
> > >
> > > Debug.Print "**** With Excel " & Application.Version
> > > Debug.Print , "Execution of " & RUNS & " runs of " &
> OutRange.Cells.Count &
> > > " cells"
> > > Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
> seconds"
> > > Debug.Print "****"
> > > End Sub
> > >
> > > NickHK
> > >
> > > "ecanet" <(E-Mail Removed)> wrote in message
> > > news:46649B7E-C9ED-4DC4-9BCF-(E-Mail Removed)...
> > > > Hi all,
> > > >
> > > > I know everyone is looking for latest version, but I work as a
> developer
> > > and
> > > > my customer use Excel 2000. He asked me to evaluate the cost of an
> upgrade
> > > to
> > > > Excel 2003, so here I am.
> > > >
> > > > My program must fill 5000*255 cells.
> > > > I use the VBA method "Resize" to select all the range, then "Value"
> and
> > > set
> > > > them with an array containing all the datas.
> > > > AFAIK, this is the most effective way to do this.
> > > >
> > > >
> > > > The problem I have is about the performances :
> > > > this code works both on Excel 2000 and on Excel 2003 but on the same
> > > > computer respectively need 3 sec and 9 sec.
> > > > This difference is not acceptable by my customer.
> > > >
> > > >
> > > > Could anyone tell me if this lack of perf is a known issue when
> upgrading
> > > > and if there is a workaround ?
> > > > I see on the net that this is a computer problem, but I'm afraid that
> they
> > > > wouldn't change their hardware for more competitive ones.....
> > > >
> > > >
> > > > Thanks for giving me any pointers and have a nice day !
> > > >
> > > >
> > > > Manu, hope I made myself clear enough, I'm not a good english speaker
> !
> > >
> > >
> > >
>
>
>