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:
Once there, we will write the things we need in the right-side window.
We will define the text that will go into cell A1 to be as follows:
|
1 |
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:
To show our sentences in different rows, we got to add a line break. This action is fairly easy to do in Excel. All you 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:
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 Excel knows it is a continuation of our sentence. This is what our final code will look like:
|
1 2 3 |
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:
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.