Additional Information:
The reason Visual Basic is so useful is that it makes programming relatively straightforward. This is because you can record code, find errors and evaluate variables quickly. Unfortunately there is an associated computational cost. Therefore it is normally worth using traditional Excel formulas to carry out simple calculations, as these are built in a far quicker language. This carries the additional benefit that Excel will re-calculate your formulas without the requirement to run any more code.
To enter formulas into cells using a macro, you can use code of the form Cells(1, 1).Formula = "=1+2". This would enter the sum "=1+2" directly into the formula bar for cell A1, whilst displaying 3 in the cell itself.
The main reason for using Visual Basic to enter formulas is to reflect the fact a table might grow as raw data is added. If we have 10 prices and want to find the average, we could use the formula "=AVERAGE(A1:A10)". If we were to add another price, we would have to manually re-write the initial formula as "=AVERAGE(A1:A11)". This step can easily be forgotten and cause raw data to be misinterpreted.
If we are trying to automatically update formulas, we have to refer to variables within our formula text. Variables are defined within Visual Basic so do not require quotation marks. Instead they can be joined to the rest of our formula using ampesands e.g. Cells(1, OutputRow) = "=AVERAGE(A1:A" & LastDataRow & ")".
On occasion, you may wish to write formulas containing symbols that do not appear on a standard keyboard e.g. if you are calling data from another application. You can copy and paste these symbols into Visual Basic Editor from the Excel formula bar.
Note that if we are using a large spreadsheet and inserting formulas in every row, then Excel will re-calculate them each and every time the sheet is changed. Although Excel formulas are fast, in extreme circumstances, your macro may need to change the sheet 1,000 times each and every time the user selects a new cell. This process will make a spreadsheet appear sluggish.
The main reason it will slow your machine down is that Excel will refresh the screen each and every time a change is made. You can turn off screen updating using Application.ScreenUpdating = False. It should be noted that if you stop the screen refreshing whilst running code, you should start it refreshing again at the end of your macro. You can do this by setting ScreenUpdating to True.
Sometimes the macro will run slow simply because of the time associated with updating the many formulas in a sheet. You can stop Excel re-calculating formulas by saying Application.Calculation = xlCalculationManual. Once you turn off sheet calculations, cells will display the old value of any formula they contain, regardless of whether the data has changed. So it is very important you reset it to xlCalculationAutomatic at the end. Depending on how you are analyzing your data, you may need to occasionally re-calculate everything using Application.Calculate.
