VBA

You can do a lot with Excel simply using formulas but there comes a time when you notice that you are doing a standard job involving moving or sorting data and you can't find an easy way of reducing it to a set of formulas. Sometimes it is just easier to write a program to automate the task and Excel has an easy-to-use programming language built into all versions, VBA - Visual Basic for Applications.

VBA is derived from Visual Basic 6 which was the most used programming language in the world until it was phased out when Microsoft introduced .NET and its associated languages.

Today VBA is the last outpost of classic Visual Basic and it is very suited to getting jobs done quickly and easily. What is isn't so good at is building big and sophisticated applications, but as a way of automating Excel and building Excel-based applications it is very good indeed. You will also find VBA within other Office applications and the details of the language hardly differ.

The same is true for different versions of Excel - but what does differ is how you gain access to the facility. Over time VBA has been removed from the main menus and been converted into an option to save beginners from being confused or intimidated by it.

Data transfer between worksheet cells and VBA variables is an expensive operation that should be kept to a minimum. You can considerably increase the performance of your Excel application by passing arrays of data to the worksheet, and vice versa, in a single operation rather than one cell at a time. If you need to do extensive calculations on data in VBA, you should transfer all the values from the worksheet to an array, do the calculations on the array, and then, possibly, write the array back to the worksheet. This keeps the number of times data is transferred between the worksheet and VBA to a minimum. It is far more efficient to transfer one array of 100 values to the worksheet than to transfer 100 items at a time.

http://www.cpearson.com/excel/ArraysAndRanges.aspx

Simple Tricks to Speed up Excel VBA Code

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False/True

Application.DisplayStatusBar = False/True

Application.EnableEvents = False/True

Activesheet.DisplayPageBreaks = False/True

Use the With Statement

Don’t Explicitly Select Objects

Avoid Excessive Trips to the Worksheet

http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/

https://sites.google.com/site/fumioandostockyards/stock/vba

https://sites.google.com/site/fumioandostockyards/stock/mytoolsaddins