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

"big t" wrote in message ...
| 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
|