Excel-VBA Solutions: September 2022

Output Data in Excel VBA

VBA can display data in a few different ways.

In the immediate window
In a message box
In an Excel sheet cell
In a label/textbox control of a userform

Display data in the immediate window

This is a data output method commonly used by developers to test subroutines and functions while developing VBA applications. In this method you can write data into the immediate window using the Debug.Print method. Here is an example.

Sub PrintIntoImmediateWindow()

Debug.Print "Hello, World!"

End Sub

This will write "Hello, World!" into the immediate window of the VBA editor.

Print data into immediate window of the VBA editor

Display data in a message box

From this method you can output data in a message box. We often use this option to display the program completion messages to the users.

Sub ShowDataInMessagebox()

MsgBox "Completed!"

End Sub

Write data into a Excel sheet cell

You can also write data into Excel worksheets using VBA. This data output method is typically used when generating reports from VBA applications. You can generate sales reports, invoices etc through a VBA application using this method. Below is a very simplest example of this method. It will write "Hello, World!" into the cell A1 of the sheet1.

Sub PrintInWorksheet()

Worksheets("Sheet1").Range("A1").Value = "Hello, World!"

End Sub

Output data in a label/textbox control of a userform

This data output method can be used when developing applications with one or more userforms. If you have a userform in your form, then you can display data inside label and textbox controls. Below is a sample form I created to show how to output data in a textbox control of a userform. I’m going to develop a simple code to print “Hello, World!” when a user clicks the “Print” button. Assume the name of the textbox is “txtMessage” and the name of the command button is “cmdPrint”.

Double click on the cmdPrint button. Then you will see this kind of click event in the userform code module.

Private Sub cmdPrint_Click()

End Sub

Then add the following code to that click event.

txtMessage.Value = "Hello, World!"

So the completed code should look like this.

Private Sub cmdPrint_Click()

txtMessage.Value = "Hello, World!"

End Sub

Now when you click on the button, “Hello, World!” will be displayed in the textbox.

Also you can use TextBox.Text property instead of the value property as well.

txtMessage.Text = "Hello, World!"

Also read
Open and read text file using VBA
Convert an Excel Sheet to PDF Using VBA
Convert an Entire Workbook to a Single PDF File
Open files in a specific directory (folder)

How to Find the Style Number of an Excel Chart Using VBA

In this lesson you will learn how to find the style number of an Excel chart using VBA. Let’s consider this sample Excel sheet. This Excel sheet lists the number of sales for a few different items. Then an Excel chart has been used to visualize the sales of each item.

Now let’s see how we can find the style number of this Excel chart using VBA. I will show you how to find the style number of an active chart and of a chart by its name. In the first example you will be able to find the style number of a selected chart.

Find style number of an active chart using VBA

In this example we are going to find the style number of an active chart. To find the style number from this method, first click on the chart you want to find the style number of.

Then run this simple subroutine.

Sub FindChartStyleNo_Method1()

MsgBox ActiveChart.ChartStyle

End Sub

This is the result obtained from the subroutine.

Find the style number of a chart by its name using VBA

From the previous subroutine we were able to get the style number of an active chart. You may know that each chart of a worksheet has a name. So in this next VBA macro we are going to find the style number of an Excel chart by its name. Remember that these names are not unique. Because users can create multiple charts with the same name.

Don’t know how to find the name of a chart? Check this post.
Find the name of a chart in Excel

Now let’s look at how to find the style number of a chart by its name. Assume that the name of our chart is “Chart 1”. Then we can find the style number of the chart using the following simple VBA macro.

Sub FindChartStyleNo_ByName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 1").Chart

     Debug.Print MyChart.ChartStyle

End Sub

If we run the above macro the style number of the Excel chart will be printed in the immediate window like this.

In this next example you will learn how to go through all the charts in the worksheet and print their names and style numbers. Here is the example sheet I’m going to use.

Let’s name this subroutine as FindChartStyleNo_AllCharts

Sub FindChartStyleNo_AllCharts()

End Sub

We need two variables for this subroutine.

Dim WS As Worksheet
Dim Sh As Shape

Next we can assign the worksheet to the WS variable as follows.

Set WS = Worksheets("Sales data")

Now we need to iterate through all the shapes of the worksheet. We can use For Each statement to do that.

For Each Sh In WS.Shapes

Next

Inside the For Each loop we need to separate only the charts. Because lots of other objects also belong to this shapes collection. So here we are going to use an If statement and the “Shape.Name” property to distinguish charts from other objects. Once charts are extracted then we can print the name and the style number in the immediate window.

If InStr(1, Sh.Name, "Chart", vbTextCompare) > 0 Then
Debug.Print "Chart name - "; Sh.Name & " Style Number - " & Sh.Chart.ChartStyle
End If

So here is the full code of this subroutine.

Sub FindChartStyleNo_AllCharts()

     Dim WS As Worksheet
     Dim Sh As Shape

     Set WS = Worksheets("Sales data")

     For Each Sh In WS.Shapes
          If InStr(1, Sh.Name, "Chart", vbTextCompare) > 0 Then
               Debug.Print "Chart name - "; Sh.Name & " Style Number - " & Sh.Chart.ChartStyle
          End If
     Next

End Sub

This is the result of the above subroutine

Name and style number of multiple charts are printed in immediate window

But this method will only work when the user hasn't changed the chart name manually. If it is possible for the user to change the chart names then you can use the “Shapes.Type” property instead of the “Shapes.Name”. This is how you can modify the If statement section to use “Shapes.Type” property.

If Sh.Type = 3 Then
Debug.Print "Chart type - "; Sh.Type & " Style Number - " & Sh.Chart.ChartStyle
End If

Type and style number of multiple charts are shown in the immediate window

3 is the MsoShapeType value that represents the charts. Check below article from Microsoft documentation to see the values for different types of shapes.

MsoShapeType enumeration (Office)

Extract Numbers From a String Using VBA

When we develop VBA applications, sometimes we need to develop functions or subroutines to extract numbers from strings. So in this lesson you will learn a few different ways to extract numbers from a string using VBA.

Assume we have a string like this.

234sTsur45$p^

We are going to learn how to extract only 23445 from the above text string. There are few different ways to do this.

Method 1 - Using IsNumeric function

In this method we are going to use an inbuilt function called “IsNumeric” to extract only numbers from the string. Let’s name this subroutine as ExtractNumbers.

Sub ExtractNumbers()

End Sub

First we need to declare a few variables.

Dim MyString As String
Dim Tmp_Char As String
Dim ResultString As String
Dim i As Integer

Then we can assign our string to the MyString variable.

MyString = "234sTsur45$p^"

Next we need a For Next statement to iterate through each character of the string.

For i = 1 To Len(MyString)

Next i

Inside this For Next statement we can use the Mid function to extract each character one by one. Each character will be assigned to the Tmp_Char variable temporarily.

For i = 1 To Len(MyString)
Tmp_Char = Mid(MyString, i, 1)
Next i

Now use IsNumeric function inside an If statement to check whether each character is a value or not. If the IsNumeric function returns true then we add that number to the ResultString variable.

For i = 1 To Len(MyString)
     Tmp_Char = Mid(MyString, i, 1)
     If IsNumeric(Tmp_Char) = True Then
         ResultString = ResultString & Tmp_Char
     End If
Next i

Want to learn more about the IsNumeric function? Check this post.

IsNumeric Function

This is the complete code of the first method.

Sub ExtractNumbers()

     Dim MyString As String
     Dim Tmp_Char As String
     Dim ResultString As String
     Dim i As Integer

     MyString = "234sTsur45$p^"

     For i = 1 To Len(MyString)
         Tmp_Char = Mid(MyString, i, 1)
         If IsNumeric(Tmp_Char) = True Then
             ResultString = ResultString & Tmp_Char
         End If
     Next i

     Debug.Print ResultString

End Sub

23445 will be printed in the immediate window when you run the above subroutine.

Method 2 - Using Select Case statement

In this method we are going to use Select Case statement instead of the If statement and IsNumeric function to extract numbers from the string. Below is the complete code of the second method.

Sub ExtractNumbers_Method2()

     Dim MyString As String
     Dim Tmp_Char As String
     Dim ResultString As String
     Dim i As Integer

     MyString = "234sTsur45$p^"

     For i = 1 To Len(MyString)
         Tmp_Char = Mid(MyString, i, 1)
         Select Case Tmp_Char
         Case 0 To 9
             ResultString = ResultString & Tmp_Char
         End Select
     Next i

     Debug.Print ResultString

End Sub

In this method Select Case statement is used to check whether the character is equal to value from 0 to 9. If the character is equal to value from 0 to 9 then those characters are appended to the ResultString.

Return a String From VBA function

Function is a block of code which can be used multiple times inside a program. So you can omit writing the same code again and again by using functions. Also programmers can break down the problem into smaller segments by using the functions. This will help programmers to organize their codes in a more meaningful way. Functions normally return a value to the sub or to the function called them. So in this lesson you will learn how to return a string from a VBA function. Here is the syntax of a function which returns a string.

Function FunctionName() as string

     '-----------------------
     'Codes of the function
     '-----------------------

     FunctionName = ResultString

End Function

Above is the syntax of a function which has no arguments. So let’s create a very simple function which outputs a string using the above syntax.

Function WebsiteName()

WebsiteName = "Excel-VBA Solutions"

End Function

Above function returns the name of this website whenever it is called. Now you can call this function from a subroutine or from another function. This is how you can call the above function from a subroutine.

Sub Main()

     Dim Response As String

     Response = WebsiteName()

     Debug.Print Response

End Sub

When you run the subroutine, the name of the website will be printed in the immediate window.

Next let’s look at how to develop a function which has an argument. Here is the syntax of a function having one argument.

Function FunctionName (Argument as type) as string

     '-----------------------
     'Codes of the function
     '-----------------------

     FunctionName = ResultString

End Function

Type of the argument doesn’t need to be a string type. You can pass an argument of types such as Integer, Long, Boolean, Variant etc as well. Next let’s see how we can develop a function with an argument which returns a string. This is the sample Excel sheet I’m going to use for this example.

For this example I’m going to create a function which can return the employee name when we pass Id as the argument. Let’s name this new function as FindName. We need to use a For Next statement inside our function to get the expected result. Assume the name of the worksheet is Data. You can develop the function as below to get the name of the employee when passing the Id as argument.

Function FindName(Id_no As Long) As String

     Dim WS As Worksheet
     Dim i As Integer
     Dim LastRow As Integer

     Set WS = Worksheets("Data")
     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     For i = 2 To LastRow
         If WS.Range("A" & i).Value = Id_no Then
             FindName = WS.Range("B" & i).Value
             Exit Function
         End If
     Next i

     'If id not found
     FindName = ""

End Function

So above is an example VBA function with an argument which returns a string. Program check for the matching id while iterating through each row number. If it finds a matching id then the function will return the corresponding name. Exit function statement is used to stop executing other lines of code once a function finds a match. If the Id is not found, the function will be executed til the end and will return an empty string. Here is how you can use the above function inside a subroutine.

Sub EmployeeData()

     Dim EmployeeName As String
     Dim IdNumber As Long

     IdNumber = 135421
     EmployeeName = FindName(IdNumber)

     MsgBox EmployeeName

End Sub

Name of the relevant employee will be displayed in a message box when the code is executed.

This is what happens when you pass an Id which is not available in the worksheet.

Sub EmployeeData()

     Dim EmployeeName As String
     Dim IdNumber As Long

     IdNumber = 135429
     EmployeeName = FindName(IdNumber)

     MsgBox EmployeeName

End Sub

How to Sum a Row in Excel

In this lesson you will learn how to sum a row in Excel. I will show you two methods to get the total of an Excel row. Let’s consider this sample Excel sheet. We have names of students in column A. Then we have marks of five tests in the next five columns. Assume we want to calculate the total of five tests for each student.

Method 1

In the above example Excel sheet we want to get the sum of each row to the column G. First let’s look at how to sum a row in Excel for one row. Then we can apply similar formulas for other rows as well. Here are the steps to follow. First click on the G2 cell. Next go to the “FORMULAS” tab in the Excel ribbon.

Then click on the AutoSum

Then you will see that the Sum formula is automatically inserted to the selected cell.

Now press enter. Total value will be calculated like this. So this is one way to sum a row in Excel.

Next let’s see how we can apply similar formulas to other rows easily. To do that, first take the mouse pointer to the right bottom corner of the G2 cell. Mouse pointer will change to a + mark. Then drag the mouse upto 10th row while holding down the left mouse button. Then the total for each row will be calculated like this.

You can use this same technique to sum a column as well. Only difference is you need to select a cell bottom to the data set. For an example you can select cell B11.

Method 2

Next let’s look at the second method to sum a row in Excel. I will use this sample Excel sheet to explain this second method.

Suppose we want to get the sum of the 3rd row. If we use this second method we can get the result to any row except row 3. Because if we try to get the result value in the same row then we will get a Circular references error.

To avoid this error let’s get the result in the cell A5. To get the result using the second method enter the following formula in cell A5. Note that you can enter this formula in any cell which is not on row 3.

=SUM(3:3)