I am using a variant of the calcpos application. When I open excel with the VBA code and run the program, I get the lat and long for a specific address. However when I run it again. I get a run-time error 2147467259 80004005 - automation error message. Oddly enough, if I select the code in VBA and copy and paste it back. I can run the program again and it works. It seems that the copy and paste is clearing out the buffer and replacing it with whatever I copy and that causes the program to work again - but only once. Any ideas on how to fix this?

Hi,

I don't know VBA, but I assume you have a debugger. Can you step trough the code to see the offending line ? And can you also see the value assigned to objects and variables ?

I assume something is destroyed and not created again. Or something is not cleared where the values are not right the second time.

Hi,

I don't know VBA, but I assume you have a debugger. Can you step trough the code to see the offending line ? And can you also see the value assigned to objects and variables ?

I assume something is destroyed and not created again. Or something is not cleared where the values are not right the second time.


Thank You Wilfried for replying. I just fixed the problem by adding the following lines at the bottom of the function's code:

Set locNorthPole = Nothing
Set locSantaCruz = Nothing

I had tried this earlier but had not set the right variables to nothing after each iteration.