Excel Vba Code Continue on Next Line
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.
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)
Source: https://excelatfinance.com/xlf/xlf-line-continuation-limit.php
Post a Comment for "Excel Vba Code Continue on Next Line"