Make Excel work faster
 

I have a file, that is +- 200MB in size, and has +- 150 worksheets with 700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

Other than re-designing the whole project (use a database?) you could try
not to work with lookup worksheet functions, but get the sheet ranges in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping from
lbound to ubound.

RBS

"Siva" <Siva wrote in message
...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with 700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

<lookups by looping through these arrays in VBA. I find that this is often much faster.

My experience is quite the contrary. It is practically impossible to get even near the speed of Excel's built-in (lookup)
functions.
But using FALSE as 4th argument in VLOOKUP is disastrous for performance with large tables. There are several ways to avoid that.
Even double lookups (to check the values found) for sorted tables with the 4th argument TRUE or omitted can be several hundreds of
times faster than using FALSE. Doing the double lookup or an INDEX/MATCH combination in a VBA function is hardly any faster than a
VLOOKUP with 4th argument FALSE.
Of course Excel uses fast algorithms for searching in sorted tables, that is, 4th argument TRUE.

--
Kind regards,

Niek Otten



"RB Smissaert" wrote in message ...
Other than re-designing the whole project (use a database?) you could try
not to work with lookup worksheet functions, but get the sheet ranges in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping from
lbound to ubound.

RBS

"Siva" <Siva wrote in message ...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with 700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

It is practically impossible to get even near the speed of Excel's
built-in (lookup) functions.

I take you are saying that applies the same for worksheet functions and
lookup in VBA arrays?
Maybe I did the lookup wrong then, but I will see if I can put together an
example that proves
that array loops can be faster.

RBS


"Niek Otten" wrote in message
...
<lookups by looping through these arrays in VBA. I find that this is often
much faster.

My experience is quite the contrary. It is practically impossible to get
even near the speed of Excel's built-in (lookup) functions.
But using FALSE as 4th argument in VLOOKUP is disastrous for performance
with large tables. There are several ways to avoid that.
Even double lookups (to check the values found) for sorted tables with the
4th argument TRUE or omitted can be several hundreds of times faster than
using FALSE. Doing the double lookup or an INDEX/MATCH combination in a
VBA function is hardly any faster than a VLOOKUP with 4th argument FALSE.
Of course Excel uses fast algorithms for searching in sorted tables, that
is, 4th argument TRUE.

--
Kind regards,

Niek Otten



"RB Smissaert" wrote in message
...
Other than re-designing the whole project (use a database?) you could try
not to work with lookup worksheet functions, but get the sheet ranges in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping from
lbound to ubound.

RBS

"Siva" <Siva wrote in message
...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with
700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it
takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

sounds like a classic case of re-design.

Bite the bullet and do it.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=530834

Make Excel work faster
 

Niek,


OK, here is a tester for this:

Option Explicit
Public Declare Function timeGetTime _
Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional strMessage As Variant = "")
MsgBox "Done in " & _
timeGetTime() - lStartTime & _
" msecs", , strMessage
End Sub

Sub LookupTester()

Dim i As Long
Dim c As Byte
Dim vResult As Variant

Dim arr(1 To 10000, 1 To 2) As Long

'populate the array
For i = 1 To 10000
For c = 1 To 2
arr(i, c) = i + c
Next
Next

Select Case MsgBox("Use VLookup?", _
vbQuestion + vbYesNoCancel + _
vbDefaultButton1, _
"array lookup tester")
Case vbYes
'with VLookup
StartSW
vResult = WorksheetFunction.VLookup(5000, _
arr, _
2, _
True)
StopSW "with VLookup"

MsgBox vResult

Case vbNo
'with array loop
StartSW
vResult = LookupArray(arr, 1, 2, 5000)
StopSW "with array loop"

MsgBox vResult
End Select

End Sub

Function LookupArray(arr As Variant, _
lSearchColumn As Long, _
lResultColumn As Long, _
vLookupValue As Variant) As Variant

Dim i As Long

For i = LBound(arr) To UBound(arr)
If arr(i, lSearchColumn) = vLookupValue Then
LookupArray = arr(i, lResultColumn)
Exit Function
End If
Next

End Function

Looks to me looping through the array is faster at least in this scenario,
even although I have taken True for the
fourth argument. Looping seems at least twice as fast.
Let me know if I have not tested this properly.


RBS

"Niek Otten" wrote in message
...
<lookups by looping through these arrays in VBA. I find that this is often
much faster.

My experience is quite the contrary. It is practically impossible to get
even near the speed of Excel's built-in (lookup) functions.
But using FALSE as 4th argument in VLOOKUP is disastrous for performance
with large tables. There are several ways to avoid that.
Even double lookups (to check the values found) for sorted tables with the
4th argument TRUE or omitted can be several hundreds of times faster than
using FALSE. Doing the double lookup or an INDEX/MATCH combination in a
VBA function is hardly any faster than a VLOOKUP with 4th argument FALSE.
Of course Excel uses fast algorithms for searching in sorted tables, that
is, 4th argument TRUE.

--
Kind regards,

Niek Otten



"RB Smissaert" wrote in message
...
Other than re-designing the whole project (use a database?) you could try
not to work with lookup worksheet functions, but get the sheet ranges in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping from
lbound to ubound.

RBS

"Siva" <Siva wrote in message
...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with
700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it
takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

You may find it worthwhile to read what Charles Williams has to say. Then
reorganise your sheets, cells and even the order calculations are done in a
individual formulas. Also try and avoid any volatile functions.

http://www.decisionmodels.com/calcsecrets.htm

See the links on the top of that page, especially Calculation Process and
other good stuff elsewhere on his site.

Regards,
Peter T


arrangement
"Siva" <Siva wrote in message
...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with
700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

Hi,

Turns out we were talking about different things. I meant lookups in a worksheet range.
If the values in the range are stable, reading them into VBA once and interrogate from there is good practice; I use that in my
actuarial function system a lot. But if the values are dynamic, you'll have to pass the range to the function with each call. That
was the situation I was thinking of in my previous posts.

I tested a table of a complete column, 65536 sorted entries. I did 10,000 lookups with random keys, distributed through the entire
table.
VLOOKUPs from a worksheet took 48 msec (using your timer).
I tried 3 VBA variations:

Function LookupFromWorksheet(a As Range, b As Double)
LookupFromWorksheet = Application.VLookup(b, a, 1, True)
End Function

This took 468 msec.

Function lookupVBA(a As Range, b As Double)
Dim i As Long
For i = 1 To a.Count
If a(i, 1) = b Then
lookupVBA = a(i, 1)
Exit Function
End If
Next
End Function

just 100 lookups (not 10,000 as in previous tests) took 29703 msec.

Function LookupVBAWithArray(a As Range, b As Double)
Dim i As Long
Dim aArray
aArray = a
For i = 1 To a.Count
If aArray(i, 1) = b Then
LookupVBAWithArray = aArray(i, 1)
Exit Function
End If
Next
End Function

100 lookups took 2703 msec

So in this situation VLOOKUP from a worksheet was impossible to beat, but your examples give a good demo of what can be achieved
in VBA.

I hope I will remember to be more specific next time, when stating what performs better!

Nice excercise, thanks!

--
Kind regards,

Niek Otten


"RB Smissaert" wrote in message ...
Niek,


OK, here is a tester for this:

Option Explicit
Public Declare Function timeGetTime _
Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional strMessage As Variant = "")
MsgBox "Done in " & _
timeGetTime() - lStartTime & _
" msecs", , strMessage
End Sub

Sub LookupTester()

Dim i As Long
Dim c As Byte
Dim vResult As Variant

Dim arr(1 To 10000, 1 To 2) As Long

'populate the array
For i = 1 To 10000
For c = 1 To 2
arr(i, c) = i + c
Next
Next

Select Case MsgBox("Use VLookup?", _
vbQuestion + vbYesNoCancel + _
vbDefaultButton1, _
"array lookup tester")
Case vbYes
'with VLookup
StartSW
vResult = WorksheetFunction.VLookup(5000, _
arr, _
2, _
True)
StopSW "with VLookup"

MsgBox vResult

Case vbNo
'with array loop
StartSW
vResult = LookupArray(arr, 1, 2, 5000)
StopSW "with array loop"

MsgBox vResult
End Select

End Sub

Function LookupArray(arr As Variant, _
lSearchColumn As Long, _
lResultColumn As Long, _
vLookupValue As Variant) As Variant

Dim i As Long

For i = LBound(arr) To UBound(arr)
If arr(i, lSearchColumn) = vLookupValue Then
LookupArray = arr(i, lResultColumn)
Exit Function
End If
Next

End Function

Looks to me looping through the array is faster at least in this scenario, even although I have taken True for the
fourth argument. Looping seems at least twice as fast.
Let me know if I have not tested this properly.


RBS

"Niek Otten" wrote in message ...
<lookups by looping through these arrays in VBA. I find that this is often much faster.

My experience is quite the contrary. It is practically impossible to get even near the speed of Excel's built-in (lookup)
functions.
But using FALSE as 4th argument in VLOOKUP is disastrous for performance with large tables. There are several ways to avoid
that.
Even double lookups (to check the values found) for sorted tables with the 4th argument TRUE or omitted can be several hundreds
of times faster than using FALSE. Doing the double lookup or an INDEX/MATCH combination in a VBA function is hardly any faster
than a VLOOKUP with 4th argument FALSE.
Of course Excel uses fast algorithms for searching in sorted tables, that is, 4th argument TRUE.

--
Kind regards,

Niek Otten



"RB Smissaert" wrote in message ...
Other than re-designing the whole project (use a database?) you could try
not to work with lookup worksheet functions, but get the sheet ranges in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping from
lbound to ubound.

RBS

"Siva" <Siva wrote in message ...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with 700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?

Make Excel work faster
 

Yes, we were talking about different scenario's.
So, if you need a lookup on a sheet use the VLookup worksheet function.
If you have a VBA array though then looping is faster than using VLookup.
Or would it be worth it to put the array in the sheet, put the functions in
and put back to the array?

RBS


"Niek Otten" wrote in message
...
Hi,

Turns out we were talking about different things. I meant lookups in a
worksheet range.
If the values in the range are stable, reading them into VBA once and
interrogate from there is good practice; I use that in my actuarial
function system a lot. But if the values are dynamic, you'll have to pass
the range to the function with each call. That was the situation I was
thinking of in my previous posts.

I tested a table of a complete column, 65536 sorted entries. I did 10,000
lookups with random keys, distributed through the entire table.
VLOOKUPs from a worksheet took 48 msec (using your timer).
I tried 3 VBA variations:

Function LookupFromWorksheet(a As Range, b As Double)
LookupFromWorksheet = Application.VLookup(b, a, 1, True)
End Function

This took 468 msec.

Function lookupVBA(a As Range, b As Double)
Dim i As Long
For i = 1 To a.Count
If a(i, 1) = b Then
lookupVBA = a(i, 1)
Exit Function
End If
Next
End Function

just 100 lookups (not 10,000 as in previous tests) took 29703 msec.

Function LookupVBAWithArray(a As Range, b As Double)
Dim i As Long
Dim aArray
aArray = a
For i = 1 To a.Count
If aArray(i, 1) = b Then
LookupVBAWithArray = aArray(i, 1)
Exit Function
End If
Next
End Function

100 lookups took 2703 msec

So in this situation VLOOKUP from a worksheet was impossible to beat, but
your examples give a good demo of what can be achieved in VBA.

I hope I will remember to be more specific next time, when stating what
performs better!

Nice excercise, thanks!

--
Kind regards,

Niek Otten


"RB Smissaert" wrote in message
...
Niek,


OK, here is a tester for this:

Option Explicit
Public Declare Function timeGetTime _
Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional strMessage As Variant = "")
MsgBox "Done in " & _
timeGetTime() - lStartTime & _
" msecs", , strMessage
End Sub

Sub LookupTester()

Dim i As Long
Dim c As Byte
Dim vResult As Variant

Dim arr(1 To 10000, 1 To 2) As Long

'populate the array
For i = 1 To 10000
For c = 1 To 2
arr(i, c) = i + c
Next
Next

Select Case MsgBox("Use VLookup?", _
vbQuestion + vbYesNoCancel + _
vbDefaultButton1, _
"array lookup tester")
Case vbYes
'with VLookup
StartSW
vResult = WorksheetFunction.VLookup(5000, _
arr, _
2, _
True)
StopSW "with VLookup"

MsgBox vResult

Case vbNo
'with array loop
StartSW
vResult = LookupArray(arr, 1, 2, 5000)
StopSW "with array loop"

MsgBox vResult
End Select

End Sub

Function LookupArray(arr As Variant, _
lSearchColumn As Long, _
lResultColumn As Long, _
vLookupValue As Variant) As Variant

Dim i As Long

For i = LBound(arr) To UBound(arr)
If arr(i, lSearchColumn) = vLookupValue Then
LookupArray = arr(i, lResultColumn)
Exit Function
End If
Next

End Function

Looks to me looping through the array is faster at least in this
scenario, even although I have taken True for the
fourth argument. Looping seems at least twice as fast.
Let me know if I have not tested this properly.


RBS

"Niek Otten" wrote in message
...
<lookups by looping through these arrays in VBA. I find that this is
often much faster.

My experience is quite the contrary. It is practically impossible to get
even near the speed of Excel's built-in (lookup) functions.
But using FALSE as 4th argument in VLOOKUP is disastrous for performance
with large tables. There are several ways to avoid that.
Even double lookups (to check the values found) for sorted tables with
the 4th argument TRUE or omitted can be several hundreds of times faster
than using FALSE. Doing the double lookup or an INDEX/MATCH combination
in a VBA function is hardly any faster than a VLOOKUP with 4th argument
FALSE.
Of course Excel uses fast algorithms for searching in sorted tables,
that is, 4th argument TRUE.

--
Kind regards,

Niek Otten



"RB Smissaert" wrote in message
...
Other than re-designing the whole project (use a database?) you could
try
not to work with lookup worksheet functions, but get the sheet ranges
in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping
from
lbound to ubound.

RBS

"Siva" <Siva wrote in message
...
I have a file, that is +- 200MB in size, and has +- 150 worksheets with
700
lines of formulaes in each worksheet.

Problem is that when I update the one sheet where the lookup is, it
takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?