Excel VBA

In excel, you have to enable first the macro (and select the developper 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.

You can copy / paste the files .bas in the src folder inside the macro editor (VBS) to execute them.

Sources

• Microsoft Excel 2010: getting started

French Tips

• Excel plus: VBA tips
• Excel pratique VBA tips
• Developpez.com outlook vba

English Tips

• Ozgrid excel vb tutorial
• Excel easy: vba macro
• Tutorialspoint VBA
• Rondebruin mail with outlook
• Extend Office: AutoComplete
• Convert data type vb

Excel autofind drop down menu

Find out the detail tutorial on how to do it here:

• How to create a dropdown search menu from an excel spreadsheet

Other tips

You can find everything on my blog at:

• Excel Macro tips

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 tool bar 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 ligns.

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

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

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

Userform

Some example for the Userform

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

Closing procedure

Procedure to close a file

Sub arret()
	'stop the current sub
    ActiveWorkbook.Save
    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