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?

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 Govender@discussions.microsoft.com> wrote in message
news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
>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?

<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" <bartsmissaert@blueyonder.co.uk> wrote in message news:ezuwW8hWGHA.1348@TK2MSFTNGP05.phx.gbl...
> 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 Govender@discussions.microsoft.com> wrote in message news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
>>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?
>

> 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" <nicolaus@xs4all.nl> wrote in message
news:%23CTw5jiWGHA.1564@TK2MSFTNGP03.phx.gbl...
> <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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:ezuwW8hWGHA.1348@TK2MSFTNGP05.phx.gbl...
>> 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 Govender@discussions.microsoft.com> wrote in message
>> news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
>>>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?
>>
>
>

sounds like a classic case of re-design.

Bite the bullet and do it.

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" <nicolaus@xs4all.nl> wrote in message
news:%23CTw5jiWGHA.1564@TK2MSFTNGP03.phx.gbl...
> <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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:ezuwW8hWGHA.1348@TK2MSFTNGP05.phx.gbl...
>> 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 Govender@discussions.microsoft.com> wrote in message
>> news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
>>>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?
>>
>
>

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 Govender@discussions.microsoft.com> wrote in message
news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
> 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?

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" <bartsmissaert@blueyonder.co.uk> wrote in message news:OULflFpWGHA.3492@TK2MSFTNGP05.phx.gbl...
> 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" <nicolaus@xs4all.nl> wrote in message news:%23CTw5jiWGHA.1564@TK2MSFTNGP03.phx.gbl...
>> <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" <bartsmissaert@blueyonder.co.uk> wrote in message news:ezuwW8hWGHA.1348@TK2MSFTNGP05.phx.gbl...
>>> 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 Govender@discussions.microsoft.com> wrote in message news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
>>>>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?
>>>
>>
>>
>

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" <nicolaus@xs4all.nl> wrote in message
news:eY2fdv9WGHA.3496@TK2MSFTNGP05.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:OULflFpWGHA.3492@TK2MSFTNGP05.phx.gbl...
>> 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" <nicolaus@xs4all.nl> wrote in message
>> news:%23CTw5jiWGHA.1564@TK2MSFTNGP03.phx.gbl...
>>> <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" <bartsmissaert@blueyonder.co.uk> wrote in message
>>> news:ezuwW8hWGHA.1348@TK2MSFTNGP05.phx.gbl...
>>>> 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 Govender@discussions.microsoft.com> wrote in message
>>>> news:3306E169-01A5-4269-9165-3292C2AA9149@microsoft.com...
>>>>>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?
>>>>
>>>
>>>
>>
>
>