Best way of working with a slow VBA function

I have a custom VBA function that performs a number of functions, including a Google address lookup on the net that makes the function slow. To avoid running it continuously I have installed it in a Worksheet Selection_Change event so that it runs only after I have moved to a new row. However I am looking for a better way of managing it, especially when I paste a whole range of new cells in my spreadsheet, as well as still running when I make a change to nominated columns. Any suggestions?
Who is Participating?
Ryan ChongCommented:
it depends on how often/ quickly that info need to be reflected on your worksheet.

if you want a "real time" effect, then you probably can use Worksheet Selection_Change event, but to not slow down the whole performance, you can detect if a row is changed, and only look up with Google address if there's any changes.

OR you probably can have a Button to bulk update the list of cells that lookup with Google address. a Userform with a proper progress bar can be designed in case you want to make it more user friendly.
Rob4077Author Commented:
Thanks Ryan. Your comments made me realise that a Worksheet Change event could be used to trap when a change is made to a key column in a range and use that to trigger a lookup. If there's no change to a key column then there's no need to run a complex lookup but if there's a change to a key column then I just loop through the range and refresh the lookups. I need to test a little more but it seems to be working Ok.

Thanks for leading me to the solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.