Execution Delays in Deleting Defined Names and Establishing Hyperlinks in VBA

[claywhit]

Hi All,

I'm baffled regarding a peculiar delay when I run an excel VBA macro on a file with some 90 worksheets. The VBA code of interest is embedded with the worksheet itself (not a module). The worksheet having the VBA code is an "Index" sheet that will be populated with hyperlinks to the other worksheets, of which there are about 90 data sheets. Intent is to first clear out existing defined names and establish hyperlinks.

I don't understand it, but I seem to be getting enormous execution delays when I run the code. I put timing variables in the VBA code to measure the delays as it runs through the looping and see if any pattern for delay exists. Running multiple times, while perhaps 75% of the loops involving a defined name deletion or establishing a hyperlink have 0.0 seconds delay, approximately 25% have a 1.0 (+/- .000001) second delay. The particular results are also random, they do not repeat with specific worksheets, nor at the same loop count. I've run on two computers (Excel 2013) and same behavior. Tried looking through previous posts on forum, but didn't see anything matching.

Any thoughts/suggestions/solutions?

Thanks.
Clay

 

[claywhit]

Well all,

Problem resolved when I simply disabled the automatic calculation in the Index sheet macro, i.e. using: Application.Calculation = xlCalculationManual at start of macro and turning back to Automatic mode when finished.

Clay