# Code runs slower when executed from ribbon compared to directly in module

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:

=A1+CompanyAddin(formulaparameters)


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

• What if you add in application.enableevents = false? – Raystafarian Mar 26 at 3:36
• Just tried it. It definitely showed speed improvements in bullet 1), but basically the same results for bullet 2). – MaineBlackBears Mar 26 at 21:32
• Some quick standard troubleshooting - is the code in its own module, not on a sheet? Try creating a new workbook, copying/pasting the data from the old to the new, copying/pasting the relevant VBA and see if the same thing happens. Alternatively, try it on another machine. – Raystafarian Mar 27 at 2:30
• Thanks for the tips. Different machine has the same result. I'm going to build out a new workbook tomorrow and test that. – MaineBlackBears Mar 28 at 21:29