# VBA line continuation and its limit

## Line continuation in VBA

The **Nested24** procedure in **code 1**, constructs a demonstration nested **IF** formula with 23 nesting levels. **Code 1** line 6 assigns an Excel formula as a string to the `Formula`

variable.

### One logical line

The `Formula`

string is 603 characters in length and you need to horizontally scroll to view the complete string in the editor. The entire line 6 forms the logical line of the code statement which assigns the string to the `Formula`

variable. Although it does not effect operation of the code, the logical line can be broken into a number of physical lines to improve readability in the editor. This process is called line continuation.

**Code 1:**Sub

`Nested24`

constructs a 23 nesting levels IF formula to demonstrate length of a logical line of code. The code returns an integer in the range 1 to 24 as text

Sub Nested24() Dim Offset As String Dim Formula As String Offset = "RC[-1]" [B1].Select Formula = "=IF(" & Offset & " = 1,""One""," & "IF(" & Offset & " = 2,""Two""," & "IF(" & Offset & " = 3,""Three""," & "IF(" & Offset & " = 4,""Four""," & "IF(" & Offset & " = 5,""Five""," & "IF(" & Offset & " = 6,""Six""," & "IF(" & Offset & " = 7,""Seven""," & "IF(" & Offset & " = 8,""Eight""," & "IF(" & Offset & " = 9,""Nine""," & "IF(" & Offset & " = 10,""Ten""," & "IF(" & Offset & " = 11,""Eleven""," & "IF(" & Offset & " = 12,""Twelve""," & "IF(" & Offset & " = 13,""Thirteen""," & "IF(" & Offset & " = 14,""Fourteen""," & "IF(" & Offset & " = 15,""Fifteen""," & "IF(" & Offset & " = 16,""Sixteen""," & "IF(" & Offset & " = 17,""Seventeen""," & "IF(" & Offset & " = 18,""Eighteen""," & "IF(" & Offset & " = 19,""Nineteen""," & "IF(" & Offset & " = 20,""Twenty""," & "IF(" & Offset & " = 21,""Twenty one""," & "IF(" & Offset & " = 22,""Twenty two""," & "IF(" & Offset & " = 23,""Twenty three""," & "IF(" & Offset & " = 24,""Twenty four""," & Application.Rept(")", 24) ActiveCell.FormulaR1C1 = Formula End Sub

The b>code 1 formula is shown in figure 1. Note that the line continuation (line break) only applies to the code, not the output in the Excel formula bar.

### Multi physical lines

Breaking one logical line into a sequence of two of more physical lines

- The Visual Basic Editor (VBE) does not have a word-wrap option
- To insert a hard-break in a logical line of a code statement we use the line-continuation sequence
**Line-continuation sequence**- a space character followed by an underscore character (_), then a line termination character (the Enter key)- Note: VBA does not provide the Implicit Line Continuation feature available in VB

As can be seen, **code 2** is easier to read, than the format of **code 1**. What was one line, is now spread across 25 lines (line 7 to line 31) in **code 2**. There are 24 consecutive line-continuation characters.

**Code 2:**Sub

`Nested24CC`

constructs a 23 nesting levels IF formula to demonstrate the limitations of the line continuation character (CC)

Sub Nested24CC() Dim Offset As String Dim Formula As String Offset = "RC[-1]" [B1].Select Formula = "=IF(" & Offset & " = 1,""One""," & _ "IF(" & Offset & " = 2,""Two""," & _ "IF(" & Offset & " = 3,""Three""," & _ "IF(" & Offset & " = 4,""Four""," & _ "IF(" & Offset & " = 5,""Five""," & _ "IF(" & Offset & " = 6,""Six""," & _ "IF(" & Offset & " = 7,""Seven""," & _ "IF(" & Offset & " = 8,""Eight""," & _ "IF(" & Offset & " = 9,""Nine""," & _ "IF(" & Offset & " = 10,""Ten""," & _ "IF(" & Offset & " = 11,""Eleven""," & _ "IF(" & Offset & " = 12,""Twelve""," & _ "IF(" & Offset & " = 13,""Thirteen""," & _ "IF(" & Offset & " = 14,""Fourteen""," & _ "IF(" & Offset & " = 15,""Fifteen""," & _ "IF(" & Offset & " = 16,""Sixteen""," & _ "IF(" & Offset & " = 17,""Seventeen""," & _ "IF(" & Offset & " = 18,""Eighteen""," & _ "IF(" & Offset & " = 19,""Nineteen""," & _ "IF(" & Offset & " = 20,""Twenty""," & _ "IF(" & Offset & " = 21,""Twenty one""," & _ "IF(" & Offset & " = 22,""Twenty two""," & _ "IF(" & Offset & " = 23,""Twenty three""," & _ "IF(" & Offset & " = 24,""Twenty four""," & _ Application.Rept(")", 24) ActiveCell.FormulaR1C1 = Formula End Sub

### The limitation of the line-continuation sequence

- In VBA, there is a limit on the number of consecutive line-continuation sequences.

Adding one line-continuation to code 2 returns the error shown in figure 2. The editor prevents entry of the 25th underscore. Thus, the limit is 24 line-continuation characters.

### Solving the consecutive line-continuation limit

Line-continuations are frequently used with string variables. Another way to break lines for improved readability, and also overcome the 24 line-continuation limit is to use concatenation and build the string in a number of steps. To illustrate this, the `Nested24`

procedure is extended to a `Nested64`

procedure. A secondary motivation here, is to illustrate the limit of nested functions. Note: in VBA the catenation operator can be the ampersand (&) character or the plus (+) character.

**Code 3:**Sub

`Nested64`

constructs a 64 nesting levels IF formula to demonstrate the use of `var1 = var1 + var2 `

Sub Nested64() Dim Offset As String Dim Formula As String Offset = "RC[-1]" [B1].Select Formula = "=IF(" & Offset & " = 0,""Zero""," Formula = Formula + "IF(" & Offset & " = 1,""One"", " Formula = Formula + "IF(" & Offset & " = 2,""Two"", " Formula = Formula + "IF(" & Offset & " = 3,""Three""," Formula = Formula + "IF(" & Offset & " = 4,""Four""," Formula = Formula + "IF(" & Offset & " = 5,""Five""," Formula = Formula + "IF(" & Offset & " = 6,""Six""," Formula = Formula + "IF(" & Offset & " = 7,""Seven""," Formula = Formula + "IF(" & Offset & " = 8,""Eight""," Formula = Formula + "IF(" & Offset & " = 9,""Nine""," Formula = Formula + "IF(" & Offset & " = 10,""Ten""," Formula = Formula + "IF(" & Offset & " = 11,""Eleven""," Formula = Formula + "IF(" & Offset & " = 12,""Twelve""," Formula = Formula + "IF(" & Offset & " = 13,""Thirteen""," Formula = Formula + "IF(" & Offset & " = 14,""Fourteen""," Formula = Formula + "IF(" & Offset & " = 15,""Fifteen""," Formula = Formula + "IF(" & Offset & " = 16,""Sixteen""," Formula = Formula + "IF(" & Offset & " = 17,""Seventeen""," Formula = Formula + "IF(" & Offset & " = 18,""Eighteen""," Formula = Formula + "IF(" & Offset & " = 19,""Nineteen""," Formula = Formula + "IF(" & Offset & " = 20,""Twenty""," Formula = Formula + "IF(" & Offset & " = 21,""Twenty one""," Formula = Formula + "IF(" & Offset & " = 22,""Twenty two""," Formula = Formula + "IF(" & Offset & " = 23,""Twenty three""," Formula = Formula + "IF(" & Offset & " = 24,""Twenty four""," Formula = Formula + "IF(" & Offset & " = 25,""Twenty five""," Formula = Formula + "IF(" & Offset & " = 26,""Twenty six""," Formula = Formula + "IF(" & Offset & " = 27,""Twenty seven""," Formula = Formula + "IF(" & Offset & " = 28,""Twenty eight""," Formula = Formula + "IF(" & Offset & " = 29,""Twenty nine""," Formula = Formula + "IF(" & Offset & " = 30,""Thirty""," Formula = Formula + "IF(" & Offset & " = 31,""Thirty one""," Formula = Formula + "IF(" & Offset & " = 32,""Thirty two""," Formula = Formula + "IF(" & Offset & " = 33,""Thirty three""," Formula = Formula + "IF(" & Offset & " = 34,""Thirty four""," Formula = Formula + "IF(" & Offset & " = 35,""Thirty five""," Formula = Formula + "IF(" & Offset & " = 36,""Thirty six""," Formula = Formula + "IF(" & Offset & " = 37,""Thirty seven""," Formula = Formula + "IF(" & Offset & " = 38,""Thirty eight""," Formula = Formula + "IF(" & Offset & " = 39,""Thirty nine""," Formula = Formula + "IF(" & Offset & " = 40,""Forty""," Formula = Formula + "IF(" & Offset & " = 41,""Forty one""," Formula = Formula + "IF(" & Offset & " = 42,""Forty two""," Formula = Formula + "IF(" & Offset & " = 43,""Forty three""," Formula = Formula + "IF(" & Offset & " = 44,""Forty four""," Formula = Formula + "IF(" & Offset & " = 45,""Forty five""," Formula = Formula + "IF(" & Offset & " = 46,""Forty six""," Formula = Formula + "IF(" & Offset & " = 47,""Forty seven""," Formula = Formula + "IF(" & Offset & " = 48,""Forty eight""," Formula = Formula + "IF(" & Offset & " = 49,""Forty nine""," Formula = Formula + "IF(" & Offset & " = 50,""Fifty""," Formula = Formula + "IF(" & Offset & " = 51,""Fifty one""," Formula = Formula + "IF(" & Offset & " = 52,""Fifty two""," Formula = Formula + "IF(" & Offset & " = 53,""Fifty three""," Formula = Formula + "IF(" & Offset & " = 54,""Fifty four""," Formula = Formula + "IF(" & Offset & " = 55,""Fifty five""," Formula = Formula + "IF(" & Offset & " = 56,""Fifty six""," Formula = Formula + "IF(" & Offset & " = 57,""Fifty seven""," Formula = Formula + "IF(" & Offset & " = 58,""Fifty eight""," Formula = Formula + "IF(" & Offset & " = 59,""Fifty nine""," Formula = Formula + "IF(" & Offset & " = 60,""Sixty""," Formula = Formula + "IF(" & Offset & " = 61,""Sixty one""," Formula = Formula + "IF(" & Offset & " = 62,""Sixty two""," Formula = Formula + "IF(" & Offset & " = 63,""Sixty three""," Formula = Formula + "IF(" & Offset & " = 64,""Sixty four""," Formula = Formula + Application.Rept(")", 65) ActiveCell.FormulaR1C1 = Formula End Sub

The code 3 formula is shown in figure 3.

### The 64 nesting levels limit

Adding one more nesting level to code 3 returns the run-time error shown in figure 4. The A1=65 (shown by the yellow highlight), and 66 closing brackets (orange highlight) exceed the Excel 64 nesting level limit.

## References

msdn.microsoft.com, Statements in Visual Basic, Accessed: 21 July 2016

msdn.microsoft.com, Too many line continuations, Accessed: 21 July 2016

- This example was developed in Excel 2016 64 bit.
**Revised:**Wednesday 5th of October 2016 - 02:36 PM, Pacific Time (PT)