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.



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.



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


  1. When a space_ is inserted in code, the new line flares red and the following message is received "Compile error. Expected end of Statement" What did I miss?

  2. Dear sir,
    It's not work. it's show invalid character. Then I force to break in many line It's show compile error.
    Here the code
    ActiveCell.FormulaR1C1 = "=((RC[18]*MRP!R3C6)+(RC[19]*MRP!R4C6)+(RC[20]*MRP!R5C6)+(RC[21]*MRP!R6C6)+(RC[22]*MRP!R7C6)+(RC[23]*MRP!R8C6) _
    +(RC[24]*MRP!R9C6)+(RC[25]*MRP!R10C6)+(RC[26]*MRP!R11C6)+(RC[27]*MRP!R12C6)+(RC[28]*MRP!R13C6)+(RC[29]*MRP!R14C6)+(RC[30]*MRP!R15C6) _
    +(RC[31]*MRP!R16C6)+(RC[32]*MRP!R17C6)+(RC[33]*MRP!R18C6)+(RC[34]*MRP!R19C6)+(RC[35]*MRP!R20C6)+(RC[36]*MRP!R21C6)+(RC[37]*MRP!R22C6) _
    +(RC[38]*MRP!R23C6)+(RC[39]*MRP!R24C6)+(RC[40]*MRP!R25C6)+(RC[41]*MRP!R26C6)+(RC[42]*MRP!R27C6)+(RC[43]*MRP!R28C6)+(RC[44]*MRP!R29C6) )

Leave a Reply

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

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.