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>