Microsoft Excel is a powerful tool for data analysis and reporting. However, you can supercharge your Excel experience by using VBA (Visual Basic for Applications) to automate tasks and extend Excel’s capabilities. In this blog post, we’ll explore the top 10 VBA code snippets for Excel that can boost your productivity.

If you do not know – how to use these snippets in your excel sheet, refer to my beginner’s tutorial – How to create a simple excel macro in Excel

1. Creating a Simple Message Box

A message box is a common way to display information or collect user input. This snippet shows you how to create a basic message box.

Sub ShowMessageBox()
    MsgBox "Hello, World!", vbInformation, "Cool Box Title"
End Sub

Here, we use the MsgBox function to display a message with a title and icon. You can customize the message and appearance to your needs.

2. Looping Through Worksheets and Cells

Excel workbooks often contain multiple worksheets with various data. This code snippet helps you loop through all worksheets and cells to perform operations.

Sub LoopThroughWorksheetsAndCells()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Dim cell As Range
        For Each cell In ws.UsedRange
            ' Your code here
        Next cell
    Next ws
End Sub

This code snippet allows you to access and process data in each cell of every worksheet in your workbook.

3. Automatically Resizing Columns and Rows

Fitting your content neatly in Excel is essential. This code snippet auto-adjusts columns and rows to fit your data.

Sub AutoResizeColumnsAndRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your sheet name
    ws.Cells.EntireColumn.AutoFit
    ws.Cells.EntireRow.AutoFit
End Sub

Running this code will make sure your data is easily readable without manual adjustments.

4. Opening a File Dialog and Importing Data

Sometimes, you need to import external data into Excel. This code lets you open a file dialog for the user to select a file to import.

Sub ImportDataFromFileDialog()
    Dim FileDialog As FileDialog
    Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)
    If FileDialog.Show = -1 Then
        Workbooks.Open FileDialog.SelectedItems(1)
    End If
End Sub

This code makes it easy to bring data from external sources into your Excel workbook.

5. Creating a UserForm for Data Input

UserForms are a fantastic way to collect structured data. This snippet opens a UserForm you’ve created in the VBA editor.

Sub ShowDataEntryForm()
    UserForm1.Show
End Sub

In the VBA editor, you can design the UserForm with various input controls like text boxes, combo boxes, and buttons. This form makes data entry more user-friendly.

6. Copying and Pasting Data

Copying and pasting data in Excel is a common operation. This code automates the process.

Sub CopyAndPasteData()
    Sheets("Sheet1").Range("A1:A10").Copy
    Sheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub

This code snippet shows how to copy data from one location and paste it to another with options like xlPasteValues to paste just the values.

7. Conditional Formatting

Conditional formatting is a powerful tool to highlight specific data based on certain conditions. VBA can help you apply conditional formatting rules programmatically.

Sub ApplyConditionalFormatting()
    Dim rng As Range
    Set rng = Sheets("Sheet1").Range("B2:B10")
    With rng.FormatConditions.Add(xlCellValue, xlBetween, "10", "20")
        .Interior.Color = RGB(255, 0, 0) ' Red
    End With
End Sub

In this example, we apply conditional formatting to cells within a specified range that fall between 10 and 20.

8. Creating PivotTables

PivotTables are excellent for summarizing and analyzing data. This code creates a PivotTable from a data source.

Sub CreatePivotTable()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.PivotTableWizard
End Sub

You can expand this code to define the data source and PivotTable layout.

9. Exporting Data to PDF

Sharing your Excel data in PDF format is common. This code snippet demonstrates how to export a specific worksheet as a PDF.

Sub ExportToPDF()
    Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\ExportedFile.pdf"
End Sub

You can specify the file path and customize the PDF options.

10. Automatically Refreshing Data Connections

When working with external data sources, you can ensure that the data is up-to-date by automatically refreshing data connections. You can simply create a refresh button in your excel sheet and link the following VBA code snippet behind it. How to create a button and associate a macro behind it, refer to my previous tutorial – How to add different controls in excel such as Button, Checkbox, radio button, etc.

Sub RefreshDataConnections()
    ThisWorkbook.Connections("ConnectionName").Refresh
End Sub

Replace “ConnectionName” with the name of your data connection.