Write your first VBA program in Excel

  1. Start Excel and create a blank workbook
  2. Save the file under “My First VBA” using Macro-Enabled workbook file format.
  3. If the “Developer” menu item is not showing, right click on the ribbon area and select “Customize the Ribbon” and then check the Developer option.
  4. Click on the Developer and click on the Visual Basic.
  5. You can click on the Excel logo and go back to worksheet environment.
  6. On the left hand side you see the Project window and Properties window. You have one project called “My First VBA”.
  7. Right click on the “ThisWorkBook” object then pick Insert and the pick UserForm. Two new windows open and UserForm1 will be added to you My First VBA project.
  8. One Window is the UserForm1 object and the other window is the Toolbox. If the Toolbox do not appear, simply click on the View menu item and click on the Toolbox.
  9. From the Toolbox grab the “TextBox” and drop it in the UserForm1 window.
  10. Resize the TextBox1 and make it bigger. Then type “Hello World” in the box.
  11. In the Properties window for the TextBox1 find Font and change it to 36 points. Find background color and change it to yellow.
  12. Click on the UserForm1 area. The properties switch to UserForm1. Change the background color of the Userform1 to light green.
  13. Select Run from the menu bar and run the macro. If it works, we are almost done.
  14. If we save the file now and run the macro nothing happens because UserForms must be called to show up.
  15. Right click on ThisWorkBook again and insert a module. The object Modeule1 will be added to the project. The window that opens is the code window. We can write codes associated with this module.
  16. In the first line of code window of module1 type

Sub HelloWorld()

  1. The VBA developer program adds an “End Sub” automatically. The name we choose here for the Sub becomes the name of the macro.
  2. Between the Sub and EndSub add the code:

Userform1.show

  1. Ok, we are done. Save the file and close the Excel program.
  2. Reopen this file again. You would get a warning that macros have been disabled. Simply click on EnableContent and the macro should run.
  3. If Excel still refusing to run the program with macros enabled, click on File, Options, Trust Center. Click on Trust Center Settings, and then click on Macro settings. Select the second line option: “Disable all macros with notification”. With this option you are always notified and have an option of not running the macro contents of the file.
  4. Now select View, Macros, View Macros and click on HelloWorld. Congratulations, you just developed your first VBA macro.