We use third party addin that downloads market data from the internet into a workbook. The addin works with custom functions that begin with CompanyAddin. For instance, a formula in a cell could look like this:
My macro (code below) looks for all formulas that contains CompanyAddin. The macro then inserts a comment with the formula as the comment text. The cell then becomes the value without the formula. There are two ways I can execute the sub.
1) I can execute the code directly in the module where I write the code by hitting F5. The sub runs in about 1 second.
2) I can execute the code from the ribbon. Developer -> Macros -> CompanyAddinSave -> click Run. For some reason, the code takes 15 seconds to complete.
I don't understand how the execution time can be so different. I have tried a few things. For intance, from the ribbon, I clicked "Step Into" at which point the code pops up. If I hit F5 immediately after, the code runs at the faster execution.
Sub CompanyAddinSave() 'Turn off automatic calculations & screen updating significnatly improves performance Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Dim ws As Worksheet Dim datacell As Range Dim wb As Workbook Set wb = ActiveWorkbook Dim sheetarray As Sheets Set sheetarray = wb.Sheets(Array("Fun Discount", "Multiples", "EV Calc", "Beta", "Bandaid", "Company Reference", "Input")) 'First For Loop goes through all sheets in workbook 'Second For Loop identifies all cells with formulas, tests if cell formula contains "companyaddin" 'If it does contain "companyaddin", add comment with formula text as the comment text, save as values For Each ws In sheetarray For Each datacell In ws.Cells.SpecialCells(xlCellTypeFormulas) If datacell.Formula Like "*""companyaddin*" Then datacell.AddComment datacell.Formula datacell.Value = datacell.Value End If Next datacell Next ws 'clears object variables, restores automatic calculations & screenupdate Set ws = Nothing Set sheetarray = Nothing Set wb = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub