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


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



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.

xlf-nested24
Fig 3: the cell B1 formula - showing the B1 formula from the Nested64 macro

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.


xlf-nested24
Fig 4: the cell B1 formula - showing the attempted inclusion of another nesting level. 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