Useful VBA Excel codes with Examples

If you work with Microsoft Excel, you can use Visual Basic for Applications (VBA) to automate tasks in Excel. For example, you can write a code that will take a value from a cell and insert it into another cell.

Or, you can write a code that will insert a row after every other row.

You can use VBA to write codes that will make your work in Excel more efficient. In this post, we will share with you some useful VBA codes for Excel with examples.

VBA: the programming language

VBA the Visual Basic for Applications is a language used for applications like Microsoft Excel and Word. It’s a really easy language that you can do many tasks with it especially the Macros Command that will help you a lot in your business. You can find more about VBA and its history with the Microsoft Office Application here VBA: the programming language.
Next, we gonna list some Useful VBA codes for Excel.

Excel and VBA Editor

The VBA Editor in Excel (VBE) it’s a tool to manage and edit codes written with the VBA language. It consists of several viewing windows, the main one is entirely reserved for entering the instructions that make a module. So let’s know how to use this editor:

  1. How do I launch Visual Basic Editor?
    • Excel version 2003 (or earlier):
      -> Using the menu Tools -> Macro then click on the option Visual Basic Editor or type.
      -> Using shortcut key [Alt][F11]

      Visual Basic Editor Excel 2003

      Visual Basic Editor Excel 2003

    • Excel version 2007 or later: In newer versions of Excel, the tools for using VBE is hidden which is the developer tab, you can follow this quick guide to know how to add the developer tab to the ribbon in Excel version 2016 and earlier.
      Excel 2016 Developer Tab

      Excel 2016 Developer Tab

      Once you add the developer tab, In this new tab, click on the Visual Basic icon to launch VBE.

  2. VBE: the interface

    VBA: the interface

    VBA: the interface

  3. Launch a new Module in VBE
    1. Right-click in the project you want to add a new Module on it “VBAProject(VBA Editor.xslm)”
    2. Select Insert -> Module

    Insert VBA Module

    Insert VBA Module

Useful VBA codes for Excel

After we see our VB Editor and how to lunch a new Module, now we gonna make things more pragmatical by listing some useful VBA codes examples in the Microsoft Office Excel 2016 but also for earlier version too.
What you need to do is:

  1. Copy the VBA code examples bellow
  2. Insert a new Module in the VB Editor
  3. Paste the code and close the VBE
  4. Go to the Developer tab and open the Macro EditorMacro Editor
  5. Run the new VBA code you just addRun the Macro

So lets start by adding some easy VBA codes and go further for advanced ones:

1. Copy a value from one cell to another
This code will copy the value from cell A1 to cell B1.

Sub CopyValue()

Range("B1").Value = Range("A1").Value

End Sub
2. Insert a row after every other row
This code will insert a row after every other row in your worksheet.

Sub InsertRow()

Dim i As Long

For i = 1 To ActiveSheet.UsedRange.Rows.Count Step 2

ActiveSheet.Rows(i + 1).Insert

Next i

End Sub
3. Replace all empty Cells with Zero
Sub replaceEmptyWithZero()
 Dim rng As Range
 Selection.Value = Selection.Value
 For Each rng In Selection
  If rng = "" Or rng = " " Then
  rng.Value = "0"
  End If
 Next rng
End Sub
4. VBA Message Box with Yes/No confirmation
Sub vbaMsgBox()

    If MsgBox("Are you sure you want to delete the content
of B2?", vbYesNo, "Confirmation request") = vbYes Then
        MsgBox "The content of B2 has been deleted!"
    End If

End Sub
5. Run a macro when opening the spreadsheet
Sub Auto_Open ()
    MsgBox "To find out all about Excel, go to  "
End Sub
6. Worksheet with current date and time
Sub writeDataTime ()
    Range ("A1") = Now
End Sub
7. Check if there is a formula in the current Cell
Sous checkFormula ()
  Si Range ("A1"). HasFormula = True Alors
    MsgBox "There's a formula"
   MsgBox "It is not a formula"
  Fin si
End Sub

Those are the most Used VBA Codes in Excel if you want to search for more you can try our search that contain hundreds of usuful free Excel Macros, Functions, Templates, Add-ins, VBA and much more.

And dont forget to like and share 🙂

Leave a Comment