Excel VBATutorial          

             

Excel VBA Macros

Email to excel-vba.com

Excel Tutorial on Macros

 Excel Consulting

Here is a sample of what you will find
in the downloadable 
Tutorial on Excel macros

VBA Code  for Databases

When Excel recognises you set of data as a database it offers you very powerful database functionalities like sorting and filtering.

Deactivating filters

When you work in an Excel database you might want to make sure that all data filters are off. To this end you will start your procedure with two "If"statements. For example with a database starting in cell A1 here are the two sentences:

Range("A1" ).Select
           If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter

           If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

Sorting Data

Here is a simplified Excel macro to sort data using a criteria in one field. The following Excel macro will work with any size database starting in cell A1 and it will work in any version of Excel (1997 to 2010).

Sub proFilter()

Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

End Sub

Try the Excel macro above with a small table like the following (as you have leand how in the basic exercises for beginners):

Name Number
Jones 1
Tom 2
Barry 3
Peter 4

Here is another simplified Excel macro sorting data using criteria in three different fields.

Sub proFilter()

Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
        "B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, _
        Header:=xlYes

End Sub

The code in the two procedures above is much simpler than the following recorded macro in Excel 2007 and 2010. This recorded macro will not work in earlier versions of Excel (1997 to 2006).

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:E7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

In the downloadable tutorial on Excel macros we offer you much more vocabulary to work with Excel databases and also many more simplified macros that can be used in all versions of Excel. You can you can copy/paste any of them into your own workbooks.


We hope you have enjoyed this tip
For more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


To organize your discovery of Excel macros, the downloadable Tutorial on Excel Macros is divided in three sections (all 3  sections part of the single download):

Section 1: Excel Macros Programming (Chapters 1 to 10)
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will also learn about security and discover "events" (an event is what starts the macro).

Section 2: Excel VBA Vocabulary (Chapters 11 to 23)
Developing a macro is communicating with Excel and to do so you need to use a language called Visual Basic for Applications (VBA). In section 2 you will learn all the VBA vocabulary that is essential to work with business data (accounting, sales, production and others).

Section 3: Forms and Controls in VBA for Exce (Chapters 24 to 33)
The userform is a small or large dialog window that you create and allows the user to submit values that will be used by your macros. To these userforms you will add controls (command buttons, text boxes, list boxes and others) and program them.