Line continuation limit

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.

xlf-nested24 — **Fig 1: the cell B1 formula** - showing the B1 formula from the Nested24 macro

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.

xlf-too-many — **Fig 2: "Too many line continuations" error** - activated by attempting to include the 25th line-continuation underscore character in the Nested24CC macro in code 2

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: Friday 24th of February 2023 - 10:37 PM, Pacific Time (PT)