Break Long String to Multiple Lines in VBA

If you find any kind of problem or impediment in Excel and VBA, you will soon notice that there is always an easy way out and solution to it.

Some of the issues that we can stumble upon are related to the view. In VBA, it is a good practice to keep a clean code and to make it visible and clear for everyone who might use it in the future.

In the example below, we will show how to break a long string into multiple lines in VBA, to achieve what we said above.

Break a Long String to Multiple Lines in VBA

First thing first, we need to open our module. To do this, we will simply click the ALT + F11 combination on our keyboard, then right-click on the left window and go to Insert >> Module:

Graphical user interface, application

Description automatically generated

Once there, we will write the things we need in the right-side window.

We will define the text that will go into the cell A1 to be as follows:

Range("a2") = "This is our text. As you will notice, it will be pretty large, and we will not be able to read everything without scrolling. This is a huge issue. We should separate it somehow."

In the module, this looks odd as well:

Graphical user interface, application

Description automatically generated

To show our sentences in different rows, we got to add a line break. This action is fairly easy to do in Excel. All we need to do is to type a space on your keyboard and follow it up with an underscore, so basically you need to type in “ _” at the end of your sentence.

Now, it is not enough to simply put the space and underscore at the end of each sentence, because if we do so and try to run our code, we will get the following message:

Graphical user interface, text, application, Word

Description automatically generated

For our code to work, we need to make a few adjustments. We need to close in each line with the parentheses and start every sentence (except for the first one) with the “&” sign, so the Excel knows it is a continuation of our sentence. This is what our final code will look like:

Range("a3") = "This is our text. As you will notice," _
& "it will be pretty large, and we will not be able to read everything without scrolling." _
& " This is a huge issue. We should separate it somehow."

And in the module it will look like this:

Graphical user interface, application, Word

Description automatically generated

As it can be seen, we have a lot better view of our text now, and we do not have to drag the text left or right.

When we execute our code by clicking F5 on our keyboard, we will have the same text in both cells (cells A2 and A3).

Although you get the same result, remember that the whole point and importance of having the brake lines is to make your code neat and transparent, for yourself and for everyone else that might benefit from it.

Posted in vba