Best way of working with a slow VBA function

Rob4077
Rob4077 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial