Opinions Wanted - Which is Faster???

[Guest]

Hi Everyone,

Using VBA I need to loop through a lot of records, pick up an account name,
find the account name in a big table and return the account balance.

In terms of speed which is quicker, option 1 or 2:

Option 1 : Vlookup

myBalance = Application.WorksheetFunction.VLookup(myAccount, myBigTable,
iBalanceColumn, False)

Option 2 : Loop through the big table

dim r as range

For Each r in myBigTable
If r.Cells(1, iAccountColumn) = myAccount then
myBalance = r.Cells(1, iBalanceColumn)
Exit For
End if
Next

I had heard that using WorksheetFunctions in VBA was quite slow. What do you
think? Or is there an alternative even quicker way to do this???

TIA
big t

 

[Niek Otten]

Worksheet functions in VBA are slower than when called from a worksheet, but often much faster than anything you write yourself.
But you can easily test that by timing the cycle in a loop of 100 or 1000 times.
If you choose to cycle through the data yourself, first read the whole range into an array in VBA, and loop through that array.
If possible, sort your data and use VLOOKUP with the 4th argument set to true (or omitted). Then you'll have to check for equality
yourself, but it will probably tens of times faster.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Everyone,
|
| Using VBA I need to loop through a lot of records, pick up an account name,
| find the account name in a big table and return the account balance.
|
| In terms of speed which is quicker, option 1 or 2:
|
| Option 1 : Vlookup
|
| myBalance = Application.WorksheetFunction.VLookup(myAccount, myBigTable,
| iBalanceColumn, False)
|
| Option 2 : Loop through the big table
|
| dim r as range
|
| For Each r in myBigTable
| If r.Cells(1, iAccountColumn) = myAccount then
| myBalance = r.Cells(1, iBalanceColumn)
| Exit For
| End if
| Next
|
| I had heard that using WorksheetFunctions in VBA was quite slow. What do you
| think? Or is there an alternative even quicker way to do this???
|
| TIA
| big t
|

 

[Bob Phillips]

The worksheet function itself will be quick, it is invoking it from within
VBA that is slow. If you have a large list, and the match is some way down
it, it will still probably be quicker than looping through each row.

But ... VBA has a Find method which would probably be quicker than either in
this case. Check it out in VBA help.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

thanks guys, that's all useful info.

cheers
big t