We earlier covered an an article on creating an Excel Macro using the macro recorder.This article is on creating an Excel Macro using VBA (Visual Basic).
How to create an Excel macro using VBA?
To create an Excel Macro, navigate to the Developer tab.If the Developer tab is not enabled, you must first enable it as follows.
- Click the Microsoft Office Button , and then click Excel Options.
- Click Popular category and under Top options for working with Excel, select the Show Developer tab in the Ribbon check box
- Click OK and the Developer tab will be visible in Excel 2007
Next, set the security level to Enable all macros. To do this, navigate to Developer tab, and in the Code group, click Macro Security.Under Macro Settings, click Enable all macros (not recommended;potentially dangerous code can run) , and then click OK. However it is not recommended to enable all macros, always, as there is high risk of potentially dangerous code getting executed.Hence it is advisable to return to any one of the settings that disable all macros, after you finish working with Excel macros.
- On the Developer tab, in the Code group, click Visual Basic.
- If needed, in the Visual Basic Editor, on the Insert menu, click Module. Note that modules are automatically created for all sheets in the workbook.
- In the code window of the module, type or copy the macro code that you want to use.
- To run the macro from the module window, press F5.
- Navigate to file menu in the Visual Basic Editor, click Close and return to Microsoft Excel when you are done with the macro.
How to assign an Excel Macro to an object, graphic, or control?
- On the Excel worksheet, right-click the object, graphic, or control to which you want to assign an existing Excel macro, and click Assign Macro.
- In the Macro name box, click the macro that you want to assign.
That is all about creating Excel Macros using VBA.