Excel Macros

Excel Macros

Excel Macros can be coded up and can extent the usability of Excel, i use some fairly often.

I have a central place to contain them (a .xlam file) and then include them as an external library so that is available to all excels i open in my computer.

Include a common collection of Excel Macros

1. New Excel File
2. Developer Mode (Alt+F11)
3. Add Module
4. Put code
5. Save as .xlam
6. Open a new excel file: Plugins import: .xlam

After step 6 is now included for all files you open.

Reference:

• http://www.ozgrid.com/VBA/excel-add-in-create.htm
• http://www.ozgrid.com/forum/showthread.php?t=79163

Macro: (nicer) Numbers Formatter

Transforms 12351231124 into: 12,351,231,124

' Formats Numbers
' ctrl e
Sub FormatRange()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each c In Selection.Cells
        Select Case c.Value
            Case Is > 1000
                c.NumberFormat = "0,000"
            Case Is <> Round(c.Value, 2)
                c.NumberFormat = "0.00"
        End Select
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I have it associated to a key shortcut, but also possible and usefull to have it as a toolbar icon: http://www.excel-easy.com/vba/examples/add-a-macro-to-the-toolbar.html