Macro execution: Start from VBA Editor(F5) vs. Start from Custom ribbon

[Vinci2504]

Hi Everyone,

I have prepared some macro saved in local AddIns file. Function is available via button on custom ribbon.

I observed some discrepanciesin code time execution. Between code Option 1 and code Option 2 is small difference in IRibbonControl Sub: MacroTest / Call MacroTest.

But I observed a big discrepanciesin in time between two method on code start :

• Start from level VBA Editor: Sub MacroTest (column Start from VBA Editor (F_5) )
• Start from Custom Ribbon Button

Has anyone observed similar results in their code?
I am really interesting why is that difference between code running dirrectly from VBA Editor vs. code running via button in Custom Ribbon.

I also wonder why sometimes I get times at ~1.3s and ~5.5 and at other times times at ~0.4 and ~1.6.
In all testing no conditions has been changed:

• AddIns installed from local Excel default folder,
• File always opened from local location and the same folder.

I will be grateful for any suggestions.

Code Option 1

Sub MacroCustomRibbon(control As IRibbonControl)
    MacroTest
End Sub

Sub MacroTest()

'Delaration of variables, arrays
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    clock = Timer

    'code: operation on Arrays
    'code: operation on worksheet

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Debug.Print Round(Timer - clock, 3)
End Sub

Code Option 2

Sub MacroCustomRibbon(control As IRibbonControl)
    [B]call [/B]MacroTest
End Sub

Sub MacroTest()

'Delaration of variables, arrays
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
    'code: operation on Arrays
    'code: operation on worksheet

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Debug.Print Round(Timer - clock, 3)
End Sub

Code running first time in day:

Option	Start from VBA Editor (F5)	Start from Custom Ribbon Button	Custom Ribbon Button / VBA Editor (F5)
Option 1	1.305	17.781	13.63
Option 1	1.309	18.26	13.95
Option 1	1.305	18.07	13.85
Option 2	1.311	5.454	4.16
Option 2	1.311	5.562	4.24
Option 2	1.33	5.833	4.39

Code running other time in day (for example after 1 hour):

Option	Start from VBA Editor (F5)	Start from Custom Ribbon Button	Custom Ribbon Button / VBA Editor (F5)
Option 1	0.398	1.621	4.07
Option 1	0.402	1.59	3.96
Option 1	0.398	1.56	3.92
Option 2	0.402	1.625	4.04
Option 2	0.422	1.602	3.80
Option 2	0.48	1.73	3.60