How to record macro and then how to modify the code

In this article, we will learn how to record macro and then how to modify it and make it easier to learn.

How to write macro for Sum formula in VBE

We have an Excel data in range A1:C14, in which column A contains names, Column B having the marks of subject 1 and column C having the marks of subject 2. In column D, I want to return the total marks.

To insert sum formula using VBA in Microsoft Excel, follow the below-mentioned steps:-

• Open Excel data.
• First you record the macro for returning total marks in cell D2.
• In the “Developer” tab, select “Record Macro” from the group of “Code”.
• The dialog box of “Record Macro” will appear.
• In the “Macro Name” box write the name of macro without using any space and special character.
• Type the short cut key whichever you want.
• And write the description about the macro in description box.
• Recording will start now.
• Write the sum formula in cell D2.
• Paste the formula in range D3:D14.
• Click on “Stop Recording”
• To see the recorded macros, press key “ALT+F11”.
• Recorded code would be like this:-

Sub SUM()
'
'SUM Macro
'Total marks obtain
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Selection.Copy
Range("C2").Select
Selection.End(xlDown).Select
Range("D14").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("D2").Select
End Sub

As you can see that the above-mentioned VBA code is too lengthy to return total marks, and also it is not easy to understand. Thus, we modify the coding.

Sub SUM()
Dim X As Integer
X = Application.WorksheetFunction.CountA(Range("A:A"))
Range("D2:D" & X).Value = "=Sum(B2:C2)"
End Sub

How to write macro for Average formula in VBE

We have an Excel data in range A1:C14, in which column A contains names, Column B having the marks of subject 1 and column C having the marks of subject 2. In column D, I want to return the average marks.

To insert average formula using VBA in Microsoft Excel 2010 and 2013, follow below-mentioned steps:-

• Open Excel data.
• First you record the macro for returning average marks in the cell D2.
• In the “Developer” tab, select “Record Macro” from the group of “Code”.
• The dialog box of “Record Macro” will appear.
• In the “Macro Name” box, write the name of macro without using any space and special character.
• Type the short cut key –whichever you want.
• And write the description about the macro in description box.
• Recording will start now.
• Write the “AVERAGE” formula in cell D2.
• Paste the formula in range D3:D14.
• Click on “Stop Recording”.
• To see the recorded macros, press key “ALT+F11”.
• Recorded code would be like this:-

Sub AVERAGE()
'
'AVERAGE Macro
'Total marks obtain
'
' Keyboard Shortcut: Ctrl+Shift+A
'ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:RC[-2])"
Selection.Copy
Range("E2").Select
Selection.Copy
Range("D2").Select
Selection.End(xlDown).Select
Range("E14").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("E13").Select
Selection.End(xlUp).Select
End Sub

As you can see that the above-mentioned VBA code is too lengthy for returning total marks and also it is not easy to understand; thus, we modify the code.

Sub Average()
Dim X As Integer
X = Application.WorksheetFunction.CountA(Range("A:A"))
Range("E2:E" & X).Value = "=Average(B2:C2)"
End Sub

In this  way, we can learn how to record macro and then how to modify it.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com