Line Continuation Limit

In VBA, you can use a line continuation character (a space followed by an underscore) to split a single VBA line into two or more. Like this:

sString = “My dog “ & _
“has fleas.”

I just tried to make a really long constant string and I separated it into it’s 35 or so lines so that it would be readable. Lo and behold, at line continuation character number 25, I got an error: Too many line continuation characters.

I know I use too many line continuations. I’m a product of the newsgroups where it behooves you to not post code whose lines exceed 76 characters. Even when I code and have no intention of posting on a newsgroup, I still find myself keeping the lines short. Well, this is the first time I’ve ever run into a limit. By the way, I fixed it by:

sString = “My dog “
sString = sString & “has fleas.”

One final note about error messages: This limit may be hard and fast, or it may be one of those “available memory” type limits. I don’t know and a rudimentary Google search didn’t tell me. (See footnote 1). Microsoft’s error message certainly could of told me though. How about a message like “Too many line continuation characters. They are limited by available memory.” or “Too many line continuation characters. You can only have 25.” or “Too many line continuation characters. Why don’t you learn how to code properly, you twit.”

FN1: I was going to tell this really boring story about why I’m using a computer that doesn’t have much RAM, but I think I’ll just make a really boring separate post instead.

10 Comments

  1. Depending on which version of Excel you use, the line continuations limit is less.
    See MSKB 141513 for the beef.

    Occasionally I’ll maintain workbooks with screens of SQL embedded in the VB code module. The line continuation situation gets pretty annoying then.

    Space followed by underscore followed by newline = ignore. That’s easier to program than a limit (plus the lame error message).

    …I imagine theres a VBA developer somewhere in Redmond, reading this, cackling to themselves while they mark New Zealand on their “world map of line continuation victims”…

  2. Rick says:

    Have you ever tried this site http://rafb.net/paste/ for posting code to newsgroups?

    Here is an example:

    http://rafb.net/paste/results/D1D7V491.html

  3. Web Surf says:

    I wrote some code in XL 2003 and VBA,

    My users with XL 97 reported “compile error in hidden module”

    I tried saving my file as XL 97, it just said “File not saved”

    After 2 days, I narrowed it down to the fact that different XLs have different allowed number of line continuations.

    Gawd I wish the error messages made a bit more sense. Like maybe a line number where the error took place ? Or plain english error messages like “Too many line continuations”

  4. daniel says:

    This sort of “lacking explanation” error messages remind me of 2 other MS bugs (sorry, functionalities) that make you waste half an hour in debugging until you realise the true meaning of the error and correct it in a few seconds:

    - Excel automatation through VB6: Work on Column 255, then Column = Column + 1. Excel raises an error to the tune of “Excel cannot carry out the specified command” (or somthing like this). No mention why or no note of the 255 column limit…

    - MS Access 2000 and over: Play with a access and hit 2 Gb during a query. Access will inform you that there is some sort of (random) error with your query, but nothing on the real error like the query could not complete and/or that Access has reached it’s maximum filesize limit of 2 gb and you need to delete data or compact the database to continue…

    Cheers,
    Daniel

  5. Joshua says:

    Yes, it’s really sucks how VB limits on how many line continuations to use. I have a very long SQL query which means I will need a lot of line continuations, but I ran into this error. Really sucks.

  6. Fencliff says:

    Regarding the solution:

    * sString = “My dog “
    * sString = sString & “has fleas.”

    You said you were building a constant string? I don’t think this kind of method would compile with constants, you’d have to use and intermediary constant to build it.

    * Private Const gstr1 As String = “My Dog “
    * Private Const gstr2 As String = gstr1 & “has fleas.”

    In which case I think I would rather break my fanatic 80-column line requirement.

  7. Jon Peltier says:

    Joshua -

    The SQL query is just a string, right? You can create that string however you want. Did you split the line into multiple commands, as in Dick’s example?

  8. Jon Peltier says:

    Fencliff -

    Are you confusing constants with variables?

  9. Jon: The VBA compiler is smart enough to allow consts to be dependent on previously defined consts.
    This technique is quite handy for creating bitmask consts.

    You can write code to impress your friends

    Private Const gstr1 As String = “Your VBA version is “

    #If VBA6 Then
        Private Const gstr2 As String = gstr1 & “VBA6.”
    #Else
        Private Const gstr2 As String = gstr1 & “not VBA6.”
    #End If

    Sub test()
        MsgBox gstr2

    End Sub

  10. Jon Peltier says:

    Rob -

    I was not aware of this capability. Thanks.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: