When starting to write a VBA program, it is sometimes useful to first using the VBA record to translate the keystrokes into VBA code and to edit the code to make it more concise. It is useful to use the VBA recorder when the actions consists  of sequence of menu commands, as for example creating a chart or sorting the data. If the actions consist mostly of cursor movements, then the code generated by the recorder tends to be very verbose.

The VBA recorder is accessed by clicking the record macro button under the developer tab. After recording the code, the macro can be run by pressing alt-F8.

For instance, if you use the VBA recorder and create a table like the one shown below then the VBA code will look similar to this.


Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Jake"
Range("B1").Select
ActiveCell.FormulaR1C1 = "400"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Amy"
Range("B2").Select
ActiveCell.FormulaR1C1 = "1000"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("B4").Select
End Sub

 

The code can be more concise by editing out the select operations and removing the formula property since it is not unnecessary for a single range select object:

 


Sub Macroexample()

Range("A1") = "Jake"
Range("B1") = 400
Range("A2") = "Amy"
Range("B2") = 1000

Range("B3") = Application.Sum(Range("B1:B2")) 'Need to use application.sum because sum is an excel function.
End Sub