Here is a sample of what you will find in lesson 18 of the downloadable Tutorial on Excel macros
Lesson 18 on Excel Macros (VBA):
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.
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:
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
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).
Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
Try the Excel macro above with a small table like the following (as you have leand how in the basic exercises for beginners):
Here is another simplified Excel macro sorting data using criteria in three different fields.
Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
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).
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 introduction to lesson 18
Next Lesson: VBA Code for Variables