Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Uses of Adding Single Quotes to Start of Excel Cells

Adding single quotes to the start of cells in Excel can be necessary in various scenarios to ensure data accuracy and proper interpretation. Here are a few scenarios where this technique might be useful.

Preserving Leading Zeros

When dealing with numeric codes or identifiers that begin with zeros, Excel might automatically remove leading zeros. Adding a single quote before such values prevents Excel from treating them as numbers, preserving the leading zeros.
00123 might become 123
'00123 remains as is

Preserving leading zeros using single quotes

Preparing Data for SQL Queries

In SQL queries, single quotes are often used to denote string values. If you are creating SQL queries in Excel and have a list of values, adding single quotes ensures that they are recognized as strings.
Apple might become 'Apple'

SQL query example

Handling Special Characters

If your data contains special characters that might have specific meanings in Excel or other applications, adding single quotes can help avoid misinterpretation.
=SUM(A1:A10) might be seen as a formula
'=SUM(A1:A10) ensures it's treated as text

Show formulas using single quotes

Creating CSV Files

When creating Comma-Separated Values (CSV) files, adding single quotes to cells can be beneficial. It helps maintain the intended format, especially when dealing with leading zeros or text that might resemble numerical data.
01234, 56789 might become 1234, 56789
'01234, 56789 remains as is

Also read
Add Single Quotes to Excel Cells Using VBA
How To Quote All Cells Of A CSV File
How to Put Double Quotes in a String in VBA

Extract Formulas From Cells Using VBA

In Excel, formulas are the backbone of data analysis and calculations. Extracting these formulas using VBA can be a powerful tool for various purposes. In this guide, we'll explore step by step how to use VBA to extract formulas from cells.

Here are a few scenarios where we need to extract formulas from cells using vba

  • To help in keeping track of changes made to formulas over time.
  • Analyze formulas to identify potential errors or inconsistencies.
  • Extracting formulas can help you analyze dependencies and relationships between different cells.
  • To partially automate the documentation process, providing insights of the logic behind calculations.

Now let’s see how we can develop a macro to extract a formula from a cell using VBA. First let’s see how we can get the formula from a particular cell we want. Let’s consider this example sheet.

Sample worksheet with one formula

This worksheet has some values from B1 cell to B9 cell. Then I have put the following formula in cell B11.

Assume the name of the worksheet is “Sheet1”. Then the following subroutine will show the formula of cell B11 in a message box.

Sub ExtractOneFormula()

   Dim WS As Worksheet

   Set WS = Worksheets("Sheet1")

   MsgBox WS.Range("B11").Formula

End Sub
Macro will extract and show the formula in a message box

Now we learnt how to get a formula from a particular cell using VBA. Next let’s look at how to get a list of all the formulas in a sheet with cell addresses. Let’s consider this example Excel sheet.

Sample Excel sheet with lots of formulas

In this worksheet, Total sales in column D is calculated using formulas. For an example D2 cell has following formula

Then Sales Ranking in column E is also calculated using formulas. For an example E2 cell has the following formula.

Also % of Total Sales in column F is calculated using formulas. F2 cell has following formula

Then 3 fields(Grand Total Sales, Average Quantity Sold, Maximum Unit Price) in cells B13, B14 and B15 are calculated using following formulas.

Grand Total Sales =SUM(D2:D11)
Average Quantity Sold =AVERAGE(B2:B11)
Maximum Unit Price=MAX(C2:C11)

Then here is the macro to print all the formulas and their cell addresses in the immediate window

Sub ExtractFormulas()

   Dim WS As Worksheet
   Dim Rng As Range

   Set WS = Worksheets("Sheet2")

   For Each Rng In WS.UsedRange
     If Rng.HasFormula = True Then
       Debug.Print "Formula in " & Rng.Address & ": " & Rng.Formula
     End If
   Next Rng

End Sub
All the formulas are extracted and printed in the immediate window

In the above subroutine, the statement “If Rng.HasFormula = True Then” is used to identify the cells having formulas.

Also Rng.Address statement outputs the cell address of the cell and Rng.Formula statement output formula of that cell.

Also read
How to Add a Formula to a Cell Using VBA
Access Formula Bar Using Keyboard

Extract Only Numbers From a String Using VBA

In this lesson you will learn how to extract only numbers from a string using VBA. Here are some examples where extracting only numbers from a string can be useful.

Data cleaning in Excel
You have an Excel sheet with a column containing alphanumeric data, and you want to extract only the numeric part for analysis or calculation purposes.

Example worksheet showing how numbers are extracted from alphanumeric data

Financial Data Processing
You are working with financial data that includes transaction descriptions, and you want to extract only the transaction amounts.

Only the amounts are extracted from transaction descriptions

Web Scraping
You are scraping data from a website, and some of the retrieved text includes both numeric and non-numeric characters. You want to filter out only the numeric values.

Above are a few scenarios where you may need to extract only numbers from a string using VBA.

Now let’s see how we can develop a VBA program to do this. Let’s create a function to extract numbers from a given string. Name of the function is ExtractNumbers. It takes one parameter, InputString, which is expected to be a string. The function return will also be a string. Below is the step by step guide on how to develop this function. You can find the completed function and test subroutine at the bottom.

Function ExtractNumbers(InputString As String) As String

End Function

Next we need to declare 3 variables.

Dim i As Integer
Dim Char As String
Dim ResultString As String

i is used as a loop counter. Char represents a single character in the string. ResultString will store the extracted numbers.

A For loop is used to iterate through each character in the inputString. The loop starts from the first character (1) and continues until the length of the string (Len(inputString)).

For i = 1 To Len(InputString)

Next i

Mid Function extracts a single character from the InputString at the position i and assigns it to the variable Char.

Char = Mid(InputString, i, 1)

Check this microsoft documentation to learn more about mid function.

Mid function

If Statement checks if the extracted character is numeric using the IsNumeric function.

If IsNumeric(Char) = True Then

End If

Want to learn more about IsNumeric Function? Then check this post.

IsNumeric Function

If the character is numeric, it is appended to the resultString

ResultString = ResultString & Char

Following line is used to return the extracted numbers as the result of the function.

ExtractNumbers = ResultString

Here is the full code of the ExtractNumbers function.

Function ExtractNumbers(InputString As String) As String

   Dim i As Integer
   Dim Char As String
   Dim ResultString As String

   'Loop through each character in the input string
   For i = 1 To Len(InputString)
     Char = Mid(InputString, i, 1)

     'Check if the character is a number
     If IsNumeric(Char) = True Then
       'Append the number to the result string
       ResultString = ResultString & Char
     End If
   Next i

   'Return the result string containing only numbers
   ExtractNumbers = ResultString

End Function

Now let's create a subroutine to use the ExtractNumbers function on a sample string. This subroutine initializes a string, calls the function, and prints the extracted numbers in the Immediate Window.

Sub TestExtractNumbers()

   Dim originalString As String
   Dim result As String

   'Example string with alphanumeric characters
   originalString = "ProductA123"

   'Call the function to extract numbers
   result = ExtractNumbers(originalString)

   'Display the result in the Immediate Window
   Debug.Print result

End Sub
Result is printed in the Immediate Window

Run VBA Code Automatically on Workbook Open

In Excel automations, running VBA code upon workbook opening can streamline processes and boost efficiency. From this post you will explore the possibilities of this advanced feature, allowing you to tailor Excel to your unique needs and maximize productivity in your daily workflow. Then this article will guide you through the steps to run a VBA code when opening an Excel file using an example.

First of all let’s explore the possibility of this advanced feature.

  • Data Refresh: If your workbook relies on external data sources, running VBA code on open can trigger an automatic refresh, ensuring that your data is always up-to-date.
  • Security Measures: You may want to use password prompts when opening the workbook, ensuring that only authorized users can use the file. For this you can use this technique to show a login form when opening the workbook. Check this post to learn how to show a userform automatically when opening an Excel file.
    Show Userform Automatically When Opening Excel File
  • User Interface Customization: You can use this technique to VBA to customize the workbook's interface, displaying specific sheets upon opening. For example, your workbook may have several worksheets and you may want to show only a particular worksheet on the workbook open. This is the usage I showed in the example below.
  • Initialization Tasks: Running VBA code on open is handy for initializing variables, setting default values, or configuring the environment to default state.
  • Version Control: You can use this technique to VBA to log information about when the workbook was last opened, helping with tracking changes.
  • Connected Workbooks: In scenarios where multiple workbooks are interlinked, running VBA code on open can establish connections.
  • Alerts and Notifications: Displaying alerts or notifications when opening the Excel VBA application. In some Excel VBA applications you might need to inform users about important updates or pending tasks whe he/she opens the application.
  • Automated Backups: This technique can be used to create an automatic backup system for your Excel VBA application. For an example you can tell the VBA to create a backup every time when a user opens the application. Or you can limit it to create only one backup per day if the user opens the application multiple times within one day.

So now you explored various possibilities of this feature. Next let’s learn how to write a code to do this. I will explain this using an example.

This example belongs to user interface customization. Assume your workbook has 3 worksheets called “Input”, “Report”, “Settings”. Suppose you want to only show the “Input” worksheet when the user opens the workbook hiding the other sheets. Users might be able to see other sheets using buttons while using the application.

Now first of all we need to develop a simple macro to show only the “Input” tab hiding other sheets.

Sub ShowOnlyInput()

   Worksheets("Input").Visible = True
   Worksheets("Report").Visible = xlVeryHidden
   Worksheets("Settings").Visible = xlVeryHidden

End Sub

Note that there are different ways to hide worksheets using VBA. Some methods don't allow users to manually unhide sheets when hidden with VBA. Check this post if you like to learn more about hiding and unhiding worksheets in Excel.

Hide And Unhide Worksheets Using VBA

We can put above simple code inside a module of the VBA project. Don’t know how to insert a module to a VBA project? Then check this post.

How to Insert Modules in Excel VBA Projects

Add the code inside a module

Now we need a way to run this subroutine when the user opens the workbook. To do that we can use the workbook open event. Events are occurrences or triggers that happen within the Excel application, and VBA allows you to write code that responds to these events. So if you write a code inside the Workbook.Open event then it will be executed when the user opens the workbook.

Follow these easy steps to add code to the workbook open event.

Go to the VBA editor and double click on the ThisWorkbook Module.

Double click on ThisWorkbook module

Now select “Workbook” from the first dropdown.

Select Workbook from the first dropdown

When you select the “Workbook” from the dropdown, the second dropdown will be automatically changed to “Open” and the following code will be added to the ThisWorkbook module.

ThisWorkbook open event

Also if you look at the second dropdown, you will notice that there are lots of other events available related to the Workbook object.

Other events available for Workbook object

Now we can run the previous subroutine we wrote by calling it from inside the Open event of the Workbook.

Private Sub Workbook_Open()

   Call ShowOnlyInput

End Sub

Also instead of putting the code in a separate subroutine, you can put the code directly inside the Workbook_Open event as follows.

Private Sub Workbook_Open()

   Worksheets("Input").Visible = True
   Worksheets("Report").Visible = xlVeryHidden
   Worksheets("Settings").Visible = xlVeryHidden

End Sub

But it is always a good practice to divide the code into meaningful sections. Because it enhances readability, maintainability, and overall code quality. When code is divided into sections, each part focuses on a specific task or functionality. This makes it easier for developers (including the original coder or others) to understand the purpose and flow of each section.

How to Insert Modules in Excel VBA Projects

VBA (Visual Basic for Applications) is a powerful tool in Excel that allows users to automate tasks and enhance functionality. In this guide, we will walk you through the process of inserting a module into your Excel VBA project.

Step 1: Accessing the Developer Tab

Go to the "Developer" tab on the Excel ribbon. If you don't see the Developer tab, you may need to enable it in Excel's options. Check this previous post to learn how to enable the Developer tab in your Excel application.

How to show the Developer tab in Excel

Enable the Developer tab

Step 2: Opening the VBA Editor

Click on "Visual Basic" in the Developer tab. This action opens the VBA Editor, where you can create and manage your VBA code.

Click Visual Basic in the Developer tab

Step 3: Selecting the Workbook

In the Project Explorer window on the left, click on the workbook for which you want to insert a module. This step is crucial if you have multiple workbooks open.

Select the workbook in the project explorer

In this example I’m going to insert the module to the VBA project of the Book2 file.

Step 4: Inserting a Module

From the menu, select "Insert" and then choose "Module." This command adds a new module under the selected workbook in the Project Explorer.

Insert module
A new module inserted to the VBA project

Step 5: Opening the Module

When you insert a new module, that module will be automatically open in the central editing area. If you have multiple modules in one or more VBA projects and if you want to add/edit or view code in a particular module, then double-click on the module you want. This action opens that particular module in the central editing area, allowing you to add/edit or view your VBA code.

Inserting a module provides a foundation for incorporating custom automation and functionalities into your Excel projects. With this guide, you can now easily insert VBA modules into your workbooks.

Populate Userform Listbox from Range

In this lesson you will learn how to populate a userform listbox with a range. In VBA (Visual Basic for Applications), a list box is a graphical user interface control that allows users to make a selection from a list of items. It is commonly used in user forms to display data or present a list of options from which the user can choose. When it is used to present a list from which the user can select, the selected item or items in the list box can then be used for further processing in the other VBA codes associated with controls such as command buttons. Or by codes even associated with events such as listbox_click events etc.

A list box can contain a collection of items, typically displayed in a vertical list. These items can be static (defined at design time) or dynamic (populated at runtime). In this example we will use a collection of static items stored in a worksheet. If you want to populate the list box with a dynamic data set, then the best option is to populate data from an array. This post explains how to populate a listbox from an array.

Fill a Listbox From an Array
Sample data in an Excel range

Name of this worksheet is “EmployeeData”. This worksheet contains Full Name, Job Title, Department, Employee ID and Date of Joining for 10 employees. Now let’s see how we can populate these data into a userform listbox. This is the listbox we are going to use for this example. Name of the form is frmEmployeeData and name of the listbox is lstEmployeeData.

VBA Userform Listbox

Now we can write the vba code inside the UserForm_Initialize event to populate the listbox with Excel range.

Private Sub UserForm_Initialize()

End Sub

This is how we can populate the list with the above Excel range.

Private Sub UserForm_Initialize()

     With lstEmployeeData
         .ColumnCount = 5
         .RowSource = "=EmployeeData!A1:E11"
     End With

End Sub

This is how the listbox is populated from the Excel data range.

Listbox populated from the range

Remember that you can’t use worksheet names having spaces.In the above example we use an Excel sheet called “EmployeeData”. Assume our worksheet name is “Employee Data”. Then we can’t modify the code as follows.

Private Sub UserForm_Initialize()

     With lstEmployeeData
         .ColumnCount = 5
         .RowSource = "=Employee Data!A1:E11"
     End With

End Sub

Because then it will throw an error like this.

Error message when worksheet name contains space

How to Test a Custom VBA Function in Immediate Window

In this post I will show you how you can test a custom function in the immediate window. When you develop advanced VBA applications with multiple subroutines and functions you may need to test them while developing the code. Then you can use this technique to test individual functions without running the whole program. So now let’s look at how to call a user defined function from the immediate window. Here is a simple custom function I created to use as an example.

Function SumValues()

     Dim a As Long
     Dim b As Long
     Dim c As Long
     Dim Result As Long

     a = 1000
     b = 2000
     c = 3500

     Result = a + b + c
     SumValues = Result

End Function

This is a very simple function which returns the sum of three values. Now let’s see how we can test this function in the immediate window. First open the immediate window if it is not already opened.

How to Show Immediate Window in VBA Environment

When you open the immediate window you can test the custom function using one of the below two methods.

Method 1

Enter the function name followed by a question mark.


So here is the result you will get.

Call a custom function in the immediate window

That's one way to call a custom function from the immediate window.

Method 2

In this method we are going to use the debug.print method to print the result of the function in the immediate window.

This is how you can test a custom VBA function in the immediate window using this second method.

debug.Print SumValues()
Test function in the immediate window using debug.print method

Also read
How to Return an Array From VBA Function
How to Pass an Array to a VBA Function
How to Search For Multiple Strings Using VBA InStr Function
BESSELJ Function

Contact Form


Email *

Message *