ValuationMan
New Member
- Joined
- Jun 30, 2017
- Messages
- 49
- Office Version
- 365
- Platform
- Windows
- MacOS
I have a sub that searches for cells with formulas that contain a specific string. When I execute the code directly from the module, it runs in about 1 second. If I execute the macro from the developer ribbon or use a shortcut, the time jumps up to 10 seconds. Has anybody seen that sort of issue before? I have searched for a while and can't find anything.
Code:
Sub savevalues()
'Turn off automatic calculations & screen updating significnatly improves performance
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim datacell As Range
'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 ActiveWorkbook.Worksheets
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
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub