3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ What if you add in application.enableevents = false? \$\endgroup\$ – Raystafarian Mar 26 at 3:36
  • \$\begingroup\$ Just tried it. It definitely showed speed improvements in bullet 1), but basically the same results for bullet 2). \$\endgroup\$ – MaineBlackBears Mar 26 at 21:32
  • \$\begingroup\$ 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. \$\endgroup\$ – Raystafarian Mar 27 at 2:30
  • \$\begingroup\$ Thanks for the tips. Different machine has the same result. I'm going to build out a new workbook tomorrow and test that. \$\endgroup\$ – MaineBlackBears Mar 28 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.