In Excel, you have to enable first the macro (and select the developer option). Then you can press ALT + F11 to go into edit macro mode. To learn you can start with “recording macro” to see what excel is recording, but it’s not the most efficient way.

Comment / Uncomment bloc of code

There’s a Comment / Uncomment button that can be toggled. For that right click on the menu bar then click on edit, the edit toolbar will appear (you can place it in your quick access bar). There should be a comment and Uncomment icon. This commands will basically add or remove ' at the beginning of every selected lines.

Calling a Sub

Here are an example on how to call a subroutine: here It can be tricky.

Test "N23:Q23", 1
Call Test("N23:Q23", 1)

Sub Test(xRange As Range, val As Integer)
	'some coding
End Sub

Accelerate Macro

Here are a couple of lines that can greatly improve the efficiency of your VBA macro.

Sub example()
	'Stop automatic calculation of excel cells
	Application.Calculation = xlCalculationManual
	'Stop screen updating
	Application.ScreenUpdating = False

	'Some code

	'Put it back to "normal"
	Application.Calculation = xlCalculationAutomatic
	Application.ScreenUpdating = True
End Sub

Hide “0” value of empty cells

Sometimes there are some 0 that pops up with the below formulas, so here is a trick to hide them through formatting. Available here:

  • Home > Format > Format Cells
  • Number > Custom
  • type : 0;;;@


Some example for the Userform

        Multiline : True
        EnterKeyBehavior = True (sinon ctrl + Enter)

Closing procedure

Procedure to close a file

Sub arret()
	'stop the current sub
    ActiveWorkbook.Close True
End Sub

Close the file after 10 seconds

Private Sub Workbook_Open()
     temp = Now + TimeValue(« 00:00:10 »)
     Application.OnTime temp, « arret »
End Sub