On a worksheet I have two tables which are populated from two different external sources, one from db2 and the other SQL server. Once the data in the tables are refreshed I trigger a macro which runs VBA code that loops through the cells in both tables and applies business rules.
Testing it locally it works as expected and the macro completes in seconds. When I change the connections to point to db2 and SQL server databases on the network, it refreshes the data in a timely manner, but when I trigger the macro it takes approx. 30 minutes to complete. Only the connections have been changed and I'm using the same workbook for all testing.
First thought was that it is now pulling data from the network and it could be connectivity, but it refreshes the data OK and its only the macro that runs slowly and the VBA code does not use the connections and is just a basic for loop.
I'm lost to what is causing this and wanted to know if anyone has encountered this behaviour before and knows how I could resolve it?
I think I found the issue.
Someone had changed the view that populates the table in one database to use the LEFT function which turned a number into a string in the worksheet. So looked like comparing the string in one table to the number in the other caused the performance issue.
Surprised it caused it to slow so much, but casting it back to an integer makes it work correctly again.
Thanks for helping