How to Get Cell Value as String Using Excel VBA (4 Easy Ways)

In Visual Basic for Applications (VBA), working with cell values is a fundamental aspect of automating Excel tasks. While cells can contain various data types such as numbers, dates, or formulas, there are situations where you specifically need to get a cell value as a string using VBA. This article serves as a guide to help you understand different methods and techniques for obtaining cell values as strings in VBA.

We’ll explore the versatility of VBA and cover a range of scenarios, including extracting values from specific cells, getting cell values from different types of variables, converting cell values of other variables to strings, and even setting cell value as string.

Understanding these methods will enable you to effectively modify and process cell data to meet the needs of your application.

How to Get Cell Value as String with Excel VBA: 4 Approaches

1. Get String Cell Value from VBA Variable Type

We can get cell values as strings simply by declaring VBA variable type.

1.1 Use String Type Variable

Here, we will show you how to get a cell value declaring a string-type variable. There is a dataset below from where we will get our cell values.

To do that-

Launching the VBA and inserting a module. Paste the below code:

Sub Get_Cell_Value_String()
Dim CellValue As String
CellValue = Range("B5").Value
Debug.Print CellValue
End Sub

After that, click on the Play button or press F5.

Since there is a value of “Jhon” in cell B5, the same thing also appeared in the Immediate window. Like this, we can get the value of the cell as string by the VBA value of the cell.

1.2 Apply Variant Type Variable

In the previous section, we discussed about getting a cell value declaring a string-type variable. Now we are going to show you getting a cell value as string declaring a variant-type variable. Let’s follow the procedures below:

To run VBA and add a module in Microsoft Excel.
After launching VBA and adding a module. paste the below code in the module.

Sub Get_Cell_Value_Variant()
Dim CellValue As Variant
CellValue = Range("B6").Value
Debug.Print CellValue
End Sub

After that, click on the Play button or press F5.

We found “Sarah” as output since the value is in cell B6, the same thing also appeared in the Immediate window. Like this, we can get the cell’s value as a string by the VBA value of the cell.

2. Use Range or Cells Property

We can reference a cell with a Cells property or Range object. Here we are going to show you that-

Launch VBA and insert a Module in Microsoft Excel
After launching the VBA and inserting a module. Paste the below code:

Sub Get_Cell_Value_select()
Range("B7").Select
End Sub

In the VBA code, we wrote cell B7 as the range. So after running the code, you can see on your Excel active datasheet that cell B7 has been selected.

See this image for a better understanding.

3. Get Value from One Cell to Another Cell

With VBA, we know how to get data from the cell. Now, the question is how to insert a cell’s value into the other cell.

In this segment, we are going to show you copy a cell’s value to another cell using VBA macro. Just follow the following procedures:

First, launch VBA and insert a Module in Microsoft Excel
Now, write down the following code:

Sub Get_Cell_Value_copy()
Range("B14").Value = Range("B11").Value
End Sub

After that, click on the Play button or press F5.

For cell B14, we need the value from the cell B11 value; that’s all the code says. So, this will get the value from cell B11 to B14 using the VBA code.

4. Convert Cell Value as String

We work with lots of conversion functions in our work area in Excel. Such a function is the CStr function in VBA. VBA CStr function converts any data type to string type. In this segment, we are going to use this function to convert the cell values into strings.

4.1 Integer to String

Now let us convert the very basic data type which is an integer into a string.

Firstly, launch VBA and insert a Module in Microsoft Excel
In the Module paste the following code:

Sub integer_to_string()
Dim A As Integer
Dim B As String
A = Range("F5").Value
B = CStr(A)
Debug.Print B
Debug.Print (TypeName(B))
End Sub

Press the Run button.

You will see two outputs in the immediate window. The second output “String” means that the first output “24” convert into a string variable.

4.2 Single/Double to String

Here we will convert a double variable to a string. We taking cell F5 as our range which is a double variable and we are going to convert it into a string variable.

First, launch VBA and insert a Module in Microsoft Excel.
Then paste the following code into the module.

Sub Double_to_string()
Dim A As Double
Dim B As String
A = Range("F5").Value
B = CStr(A)
Debug.Print B
Debug.Print (TypeName(B))
End Sub

Click on the Play button or press F5 to run the code.

Two outputs will be visible in the immediate window. The first output, “23.9” is transformed into a string variable by the second output, “String“.

4.3 Date to String

Here, a date variable will be converted into a string. We are going to turn the date variable in cell C5 into a string variable by using the VBA macro. To do that, follow the processes below:

Launch VBA and insert a Module in Microsoft Excel.
After launching the VBA and inserting a module. Paste the below code:

Sub Date_to_string()
Dim A As Date
Dim B As String
A = Range("C5").Value
B = CStr(A)
Debug.Print B
Debug.Print (TypeName(B))
End Sub

Now run the code by pressing the F5 key or by clicking on the play button to see the result.

In the immediate window, there will be two outputs displayed. The second output, “String” shows the first output, “12/1/1998” converted into a string variable.

How to Set Cell Value as String Using VBA in Excel

Now we’ll learn how to set cell value as a string using VBA following two ways-

1. Setting Cell Value as Variable

In the previous sections, we explored the process of getting cell values as strings. Now, we will explore the process of setting cell values as strings using VBA in Excel. Let’s follow the following instructions, to set up the cell value as a string:

Launch VBA and insert a Module in Microsoft Excel
After launching the VBA and inserting a module. Paste the below code:

Sub Set_string_1()
strtext = "ExcelDemy"
Range("A13").Value = strtext
Debug.Print strtext
End Sub

After that, click on the Play button or press F5.

You will see “ExcelDemy” as an output in the immediate window and on your Excel sheet cell A13 as well.

2. Setting Multiple Cells’ Values at Once

Here we are going to show how to set multiple cell values at once. Follow the below instructions, to set up multiple cell values as a string:

After launching the VBA and inserting a module. Paste the below code:

Sub Set_string_2()
Range("B4:D10").Value = "1"
End Sub

After that, click on the Play button or press F5.

If you notice the VBA code you can see that we put 1 inside a quotation mark(“”). It’s because 1 is an integer. If we put 1 in between quotation marks it will act as a string.

So as output you can see the cell values of cell B4:D10 change into “1”.

Things to Remember

Cell Format: The format of a cell can affect how its value is displayed. When retrieving cell values as strings, be aware that the formatting may impact the output. For instance, if a cell is formatted as a date, the retrieved string value might be in a date format rather than the desired string representation. You can consider adjusting the cell format or using the “Text” property of the cell to obtain the displayed value.
Empty Cells: If a cell doesn’t contain any value, attempting to retrieve it as a string may result in an empty string. To handle such cases, you can check if the cell is empty using VBA’s “IsEmpty()” function or test the length of the retrieved string value to ensure it contains meaningful data.
Range Selection: Accurate specification of the range is crucial when retrieving cell values to prevent errors or unintended outcomes. Double-check the range references and ensure they correspond to the desired cells or ranges in your worksheet.

Frequently Asked Questions

1. How to extract value from a string in VBA?

To extract a value from a string in VBA, you can use various string manipulation functions and techniques. Here are a few common approaches:

Using Left, Mid, or Right functions
Using Split function
Using regular expressions

2. How can I get the value of a named range in VBA?

To get the value of a named range in VBA, you can refer to the range using its name and then retrieve its value. Here’s an example:

Dim cellValue As String
cellValue = CStr(Range("MyNamedRange").Value)

In this code, “MyNamedRange” is the name of the range, and the Value property retrieves its value as a string.

3. How to combine string and cell values in VBA?

To combine a string and a cell value in VBA, you can use the concatenation operator (&) or the Concatenate function.

Using the concatenation operator (&):

Dim stringValue As String
Dim cellValue As String
stringValue = "The value in cell A1 is: "
cellValue = CStr(Range("A1").Value)
Dim combinedString As String
combinedString = stringValue & cellValue
MsgBox combinedString

Download Practice Workbook

If you want a free copy of the illustrated workbook we discussed during the presentation, please click the link below this section.

Get Cell Value As String.xlsm

Conclusion

In this article, we have explored different ways of getting, converting, and setting cell values as strings. By using the methods discussed in this article, you can efficiently extract cell values, handle different data types, and incorporate them into your VBA code. The methods stated above provide you with the ability to use cell values as strings efficiently.

I hope you like this article. If you have any queries or suggestions regarding this article comment below in the comment section.

Get FREE Advanced Excel Exercises with Solutions!