Macros (VBA for Excel)

Introduction to VBA for Excel

VBA stands for Visual Basic for Applications. It is a custom version of the Visual Basic programming language that has powered Microsoft Excel’s macros since the mid-1990s. The programming interface of MS Excel will let you,

  • Create and Execute Macros (explained in the next section). Anything that the user can do in Excel from the user interface can be done by writing code in VBA for Excel.
  • Create User Defined Functions (UDFs).
  • Integrate Excel with other applications such as Microsoft Word, PowerPoint, Outlook, Notepad, etc.

What is a Macro ?

Macros are codes written in VBA (Visual Basic for Applications), or recorded in MS Excel (or other MS applications) which helps to reduce the manual effort required for a job. Macros save your time and headaches by automating common, repetitive tasks. They reduce the possibility of human error that increases with many, repetitive keystrokes and tasks. The most important feature of an Excel macro is, it can be saved for future use, shared among multiple users.

In a layman’s language, a macro is a recording of your routine steps in Excel that you can replay using a single button

The best part is You don’t have to be a programmer or know Visual Basic Applications (VBA) to use Macros. You just need to copy the codes shared here and hit the run button to execute them. Save the file as a Macro enabled workbook for future use.

How to use the codes shared here?

  • Step 1: Copy the Code shared here.
  • Step 2: Click on Visual Basic in the Developer Tab of Excel Ribbon. ALT + F11 is the Shortcut for Visual Basic Editor.
  • Step 3: Right click on the tree view and Insert a Module.
  • Step 4: Paste the Code given here into a module.
  • Step 5: Close the editor and Click on Macros in Developer Tab. You will get a dialogue box with the list of Macros ready for execution. Click on Run button to execute the macro.

Ready to use Macros

1. Add Multiple WorkSheets in a Workbook

2. Delete Worksheet/Worksheets

3. Insert Rows

4. Delete Rows

5. Macros to Insert and Delete Columns

6. Message Box and Input Box in VBA

7. Import and Export Text Files into Excel

8. Hide and Unhide Worksheets

9. Convert a worksheet into Very Hidden Mode

10. Delete every Worksheet, except Active Sheet

11. Create a PDF file from a worksheet

12. Convert Text into Upper, Lower or Proper Case

13. Join Text in Rows and Columns

14. Hide and UnHide Rows and Columns

15. Select the first 1000 cells of Column A

16. Protect and UnProtect sheets with Password

17. Text to Speech

18. AutoFit Rows and Columns

19. Reverse Text

20. Macro to Convert Formulas into Values

21. Create a List of Worksheets

22. Convert Numbers into Words

Things to remember while using Macros

  • By default, the tab for developers is not displayed in excel. If you can’t see the Developer tab, use the quick access toolbar to activate it.
  • By default, Macros are disabled in excel. To execute macros, you will need to enable running macros.
  • We cannot Undo an action performed using Macro. Make sure that you have a backup of the data before executing the macro.
  • To save macros in a workbook, you must save your workbook in a macro-enabled format *.xlsm
  • Always fill in the description of the macro when creating one. This will help you understand your own work if you have to revisit it weeks or months later.

4 thoughts on “Macros (VBA for Excel)

Leave a Reply

Your email address will not be published.Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.