How do I insert a line break in the Excel VBA MsgBox (Message Box)? I tried ^p, but does not work.
Great question – this is actually a Excel VBA string / text question as much as it is a question about a VBA MsgBox. When I started out with Excel VBA one of the most frustrating problems I faced was creating text with a line break or a quote character. (Maybe that’s two problems…)
The problem is that Excel VBA syntax reserves the line feed character and quote character for a specific functions and if you don’t know the secret to getting these buggers in there I was in for a long frustrating night.
The trick here is to realize that the text in the VBA MsgBox caption is a text string A text string is a list of individual text characters (an array in technical programming jargon). Behind the scenes in computer land, each character is actually a ASCII number. You can force the computer to create any character you want by using the VBA Chr() function. The final piece of the puzzle is to know that ASCII character #13 is a new line character. So the code looks like:
There is another way to get this to work. In Visual Basic / VBA there are number of vb constants that are shortcuts for things like Char(34). The vb constant for a line feed is vbNewLine. So the code would look like:
If you wanted more than one line separating the lines of text you can add extra lines like this:
This would be my final evolution of the Excel VBA code. Notice that all the text fits onto the screen – this is an example of best practices in action. Generally I try to have all my code fit into 70-80 characters because it just seems to be more readable that way. I used two techniques to fit all the text on the screen.
The first technique is to do what I call "build the text string". When I build a text string each line builds on the prior line, adding more text. A little bit of overkill for this example but if you have a large amount of text that needs to be dynamically changed (feeding off a database for example) then I have found this technique to be useful. Also separating the text into multiple lines allows robust error handling if you are doing fancy stuff like getting data dynamically from databases.
The second technique is to use an underscore character ("_") to break up the line. An underscore allows you to create a new line visually but VBA views it as a continuation of the prior line. This can be useful in situation where you have alot of parameters that need to be passed in complex methods or subroutines. One of the downsides of the underscore character is that it is difficult to implement error handling because the whole line is evaluated as one line (because it is one line). If I were to use an underscore character when I am "building a string" I would have only one line to implement error handling, that is why I prefer to do it the way I presented it above.
Again If you want to see a video tutorial about a VBA Msgbox check out SpreadSheetSuperStar.com