How To Start From Scratch With Excel VBA

“The secret of getting ahead is getting started” – Mark Twain.

 

 

 

 

 

 

 

 

Would you like to  learn VBA  but feel it is too complex?

Are you a frequent Excel user who would love to know more about macros?

Have you just started with Excel VBA?

If you answered yet to any of these questions  then this post it for you.  When you have finished you will be able create and run simple macro using VBA.

This posts uses Excel 2013 but is also valid for  Excel 2007/2010.

Step 1 Ensure the Developer Tab is Visible

The Developer Tab is normally visible by default on your ribbon. If not you can make it visible using the following steps

1. Right click on the ribbon. In the list that appears select “Customize the Ribbon”

 

2. In the dialog that appears you will see a list of tabs on the right hand side. Ensure that “Developer” is checked on.

 

Step 2 Ensure Your Security Setting are Correct

The next step is to ensure your Macro settings are correct. On the Developer Ribbon, Click on the “Macro Security” button under the “Code” section.

 

You security settings should match the settings in the screenshot below

 

 

Step 3 Save as a Macro Enabled Workbook

If you workbook has Macros then you must save it as a Macro Enabled Workbook. It is a good idea to do this in the beginning so as to avoid the potential lost of code.

Select File->Save As and choose “Macro Enabled Workbook” from the File type. The click on Save.

 

 

Step 4 Enter the Visual Basic Environment

To enter the Visual Basic editor click on the Visual Basic icon on the Developer tab of the Ribbon. Alternatively you can press Alt + F11 on the keyboard

 

Step 5 Create a Module

Macros are stored in Modules so create a module.

Look in the VBA Project Window. If the window is not visible the select  “View” ->”Project Explorer” from the menu (shortcut Ctrl + R).

Select the workbook where you want to place the code. The workbooks will be in parenthesis beside “VBAProject”. Each open workbook will have an entry like this in the VBA Project window

 

Right click on the workbook where you wish to place the code. Select “Insert” and then “Module”.

a new module  will appear:

When you double click on a Module in the VBA Project window the code of that module is displayed in the main code window. When a new module is created it normally doesn’t contain any code(note it may contain “Option Explicit” if you have this option turned on).

 

Step 6 Create a Macro

Type “Sub MyFirstMacro” in the main code window and press return. You should have the following

 

 

Step 7 Add Some Code to Your Macro

Use MsgBox “Hello World”. This will display a dialog with “Hello World” to the user

 

 

Step 8 Run Your Macro

To run your macro:

Click anywhere inside the Macro
Select Run->Run Sub/UserForm 

Note: If the cursor is not inside a Macro then VBA will display a list of available Macros and ask you to select one to run.

 

You should get a dialog like this

 

 

Conclusion

This post showed you how to get up and running using VBA. It went through the steps for setting up correctly and ended with you running a macro. If you found this post useful the you might like to try our other posts such as The Complete Guide To Workbooks in Excel VBA or The Complete Guide to Ranges and Cells in Excel VBA.