How do I Break VBA Code into Two or more Lines in Microsoft Excel 2010

When writing your code, at times we create lengthy statements and the user needs to scroll horizontally in the VB code editor to view the code.This will not affect the way the code runs, but it makes it difficult for the useror someone else to read the code on the monitor.In these cases, it is better to breakthe single long statement into several lines.

By breaking the code into more than 1 line, you are making the code easier to read, both online and when printed.

To break the code into multiple lines we can use follow these steps:

  • Open Microsoft Excel. Press Alt + F11 to launch the VBA Editor screen
  • Click on the Module that contains the code.
  • Click on the right side of the screen. Click on the character where you want to split the code.

img1

 

We are not able to view the code after the text FileFormat: . So press the spacebard key on the keyboard to add a space, then enter an underscore. Press Enter on the keyboard and the rest of the code on that line will drop down to a new line directly below the first. Underscore is used here as a line-continuation character. See the screenshot below.

img2

 

The above step can be repeated for bigger code lines which continue on many lines and thus we can use the underscore character for each such new line to be created. See the screenshot below –

This is a long code line split into multiple lines –

 

sFormula = “=IF(” & c & “$1=””NO””,””””,(” & _
“(SUMPRODUCT(–(” & z & “$M$2:$M$65535″”)=” & c & “$2),–(” & z & “$S$2:$S$65535″”)=””Premiums EB – Direct””),–(” & z & “$T$2:$T$65535″”)=$A343),” & z & “$G$2:$G$65535″”))+” & _
“SUMPRODUCT(–(” & z & “$M$2:$M$65535″”)=” & c & “$2),–(” & z & “$S$2:$S$65535″”)=””Premiums EB – RI””),–(” & z & “$T$2:$T$65535″”)=$A343),” & z & “$G$2:$G$65535″”))+” & _
“SUMPRODUCT(–(” & z & “$M$2:$M$65535″”)=” & c & “$2),–(” & z & “$S$2:$S$65535″”)=””Premiums EB – RO””),–(” & z & “$T$2:$T$65535″”)=$A343),” & z & “$G$2:$G$65535″”)))))”
Range(c & “344″).Formula = sFormula

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube