How to apply line continuation where line too long?

Question

I have a long MySQL query in VBA.

I tried to put a space and '_'. What am I missing?

Sql = "insert IGNORE into nhc (date,dealer_code,name,area_executive,address1,address2,address3,area_territory_id,area_territory_name,micro_market_id,micro_market_name,town,postcode,state,area_name,distributor,remark,may_2020_ga,may_2020_awtu10,may_2020_sellin,may_2020_awmi10,jun_2020_ga,jun_2020_awtu10, _
jun_2020_sellin,jun_2020_awmi10,jul_2020_ga,jul_2020_awtu10,jul_2020_sellin,jul_2020_awmi10,aug_2020_ga,aug_2020_awtu10,aug_2020_sellin,aug_2020_awmi10,dealer_class,may_2020_projected_dealer_class,jun_2020_projected_dealer_class,jul_2020_projected_dealer_class,aug_2020_projected_dealer_class, _
current_clas_awtu_target,current_class_sellin_target,current_class_awmi_target,dealer_status,disc_date,ambitious_dealer?_y/n,shopfront_signage,may_2020_mnp_awtu10,jun_2020_mnp_awtu10,jul_2020_mnp_awtu10,aug_2020_mnp_awtu10,may_2020_ereload_sellin,jun_2020_ereload_sellin,jul_2020_ereload_sellin,aug_2020_ereload_sellin,may_2020_hero_sell_through,jun_2020_hero_sell_through,jul_2020_hero_sell_through,aug_2020_hero_sell_through _
,may_2020_ga_with_ocr,jun_2020_ga_with_ocr,jul_2020_ga_with_ocr,aug_2020_ga_with_ocr)values _
('" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "','" & row.Cells(3).Value & "', '" & row.Cells(4).Value & "', '" & row.Cells(5).Value & "','" & row.Cells(6).Value & "', '" & row.Cells(7).Value & "', '" & row.Cells(8).Value & "','" & row.Cells(9).Value & "', '" & row.Cells(10).Value & "', '" & row.Cells(11).Value & "','" & _
row.Cells(12).Value & "', '" & row.Cells(13).Value & "', '" & row.Cells(14).Value & "','" & row.Cells(15).Value & "', '" & row.Cells(16).Value & "', '" & row.Cells(17).Value & "','" & row.Cells(18).Value & "', '" & row.Cells(19).Value & "', '" & row.Cells(20).Value & "','" & row.Cells(21).Value & "', '" & row.Cells(22).Value & "', '" & row.Cells(23).Value & "','" & row.Cells(24).Value & "', '" & row.Cells(25).Value & "', '" & _
row.Cells(26).Value & "','" & row.Cells(27).Value & "', '" & row.Cells(28).Value & "', '" & row.Cells(29).Value & "','" & row.Cells(30.Value & "', '" & _
row.Cells(31).Value & "', '" & row.Cells(32).Value & "','" & row.Cells(33).Value & "', '" & row.Cells(34).Value & "', '" & row.Cells(35).Value & "','" & row.Cells(36).Value & "', '" & row.Cells(37).Value & "', '" & row.Cells(38).Value & "','" & _
row.Cells(39).Value & "', '" & row.Cells(40).Value & "', '" & row.Cells(41).Value & "','" & row.Cells(42).Value & "', '" & row.Cells(43).Value & "', '" & row.Cells(44).Value & "','" & row.Cells(45).Value & "', '" & row.Cells(46).Value & "', '" & row.Cells(47).Value & "','" & row.Cells(48).Value & "', '" & row.Cells(49).Value & "', '" & row.Cells(50).Value & "','" & row.Cells(51).Value & "', '" & row.Cells(52).Value & "', '" & row.Cells(53).Value & "','" & row.Cells(54).Value & "', '" & row.Cells(55).Value & "', '" & _
row.Cells(56).Value & "','" & row.Cells(57).Value & "', '" & row.Cells(58).Value & "', '" & row.Cells(59).Value & "','" & row.Cells(60).Value & "', '" & row.Cells(61).Value & "')"

Why not use `Join()` with the delimiter `', '`? – John Coleman Jul 09 '20 at 02:46 — John Coleman, Jul 09 '20 at 02:46

braX · Answer 1 · 2020-07-09T01:07:11.237

2

Break it into pieces:

SQL = "insert IGNORE into nhc"
SQL = SQL & " (date,dealer_code,name,area_executive,"

etc...

and pay attention to spaces in the right place. I like to make sure each line starts with a space (as shown above) so it's easy to tell if you are missing one.

Max String Size in VBA

Also consider using parameters instead for something like this so you dont need a string that is a million miles long.

edited Jul 09 '20 at 01:07

answered Jul 09 '20 at 00:58

braX

11,506
5
20
33

Thanks for your sharing, will try to improve my coding skill – robin Jul 09 '20 at 11:55
Please remember to select one of the answers as the accepted answer using the green checkmark on the left so the question does not remain open. – braX Jul 09 '20 at 11:57

score 2 · Answer 2 · answered Jul 09 '20 at 03:01

There are no string literals in the VBA. You need to end each line by closing off the quotes, an ampersand to concatenate the next line and an underscore as a line continuation " & _.

Using WorksheetFunction.TextJoin() will make your life a lot easier.

Sql = "insert IGNORE into nhc (date" & _
    "dealer_code, name, area_executive, address1, address2, address3, " & _
    "area_territory_id, area_territory_name, micro_market_id, micro_market_name, town, postcode, " & _
    "state, area_name, distributor, remark, may_2020_ga, may_2020_awtu10, " & _
    "may_2020_sellin, may_2020_awmi10, jun_2020_ga, jun_2020_awtu10,  jun_2020_sellin, jun_2020_awmi10, " & _
    "jul_2020_ga, jul_2020_awtu10, jul_2020_sellin, jul_2020_awmi10, aug_2020_ga, aug_2020_awtu10, " & _
    "aug_2020_sellin, aug_2020_awmi10, dealer_class ,  may_2020_projected_dealer_class,  jun_2020_projected_dealer_class,  jul_2020_projected_dealer_class, " & _
    "aug_2020_projected_dealer_class,  current_clas_awtu_target, current_class_sellin_target, current_class_awmi_target, dealer_status, disc_date, " & _
    "ambitious_dealer?_y/n, shopfront_signage, may_2020_mnp_awtu10, jun_2020_mnp_awtu10, jul_2020_mnp_awtu10, aug_2020_mnp_awtu10, " & _
    "may_2020_ereload_sellin, jun_2020_ereload_sellin, jul_2020_ereload_sellin, aug_2020_ereload_sellin, may_2020_hero_sell_through, jun_2020_hero_sell_through, " & _
    "jul_2020_hero_sell_through, aug_2020_hero_sell_through, may_2020_ga_with_ocr, jun_2020_ga_with_ocr, jul_2020_ga_with_ocr, aug_2020_ga_with_ocr, " & _
    "Error 2042) VALUES" & _
    "('" & WorksheetFunction.TextJoin("', '", False, Row.Resize(1, 60)) & ")"

Thanks for your sharing, will try to improve my codes with your input and learning — robin, Jul 09 '20 at 11:58
@robin Judging from the column names, you should split the `nhc` table. You should consider posting a question on [Code Review](http://codereview.stackexchange.com/) tagged database and whatever database you are using (MySql?). Try and post a schema and a detailed description of what each table does. — TinMan, Jul 09 '20 at 15:29
To compliment TinMan's suggestion - if you post on CR please read the [`sql` wiki](https://codereview.stackexchange.com/tags/sql/info) - especially the last section - i.e. **Question Guidelines** — Sᴀᴍ Onᴇᴌᴀ, Jul 09 '20 at 16:56
The Worksheet function `TextJoin()`is a feature of version 2019+. - Using *VBA* you could assign cells to a variant array `v = Sheet1.Range(row.Cells(1), row.Cells(59))` and `Join` after a double tranposition: `"('" & Join(Application.Transpose(Application.Transpose(v)), "', '") & "')"` @TinMan — T.M., Aug 16 '20 at 10:41
@T.M. Good Point. `TextJoin()` was added in 2016. I thought that it was around a lot longer. — TinMan, Aug 16 '20 at 16:17

Samuel Everson · Accepted Answer · 2020-07-09T02:48:43.530

I'm not sure if there is any documentation to support my answer specifically but per the Microsoft Documentation for the String Data Type:

You must enclose a String literal within quotation marks (" ").

Your continuations weren't working because they were within your string until you start adding the row.Cells(x).Value bits.

For the continuaton to be correct, per MS documentation for Visual Basic How To: Break and Combine Statements:

Use the line-continuation character, which is an underscore (_), at the point at which you want the line to break. The underscore must be immediately preceded by a space and immediately followed by a line terminator (carriage return) or (starting with version 16.0) a comment followed by a carriage return.

Note: This documentation is for Visual Basic. To make it relevant to Visual Basic for Applications you must concatenate (&) the string with the line-continuation character if you are continuing the string literal onto a new line; e.g:

"This string will be continued" & _
" on this line."

To use the line continuation correctly with your provided string, it should look like this:

Sql = "insert IGNORE into nhc (date,dealer_code,name,area_executive,address1,address2,address3,area_territory_id,area_territory_name,micro_market_id,micro_market_name,town,postcode,state,area_name,distributor,remark,may_2020_ga,may_2020_awtu10,may_2020_sellin,may_2020_awmi10,jun_2020_ga,jun_2020_awtu10," & _
" jun_2020_sellin,jun_2020_awmi10,jul_2020_ga,jul_2020_awtu10,jul_2020_sellin,jul_2020_awmi10,aug_2020_ga,aug_2020_awtu10,aug_2020_sellin,aug_2020_awmi10,dealer_class,may_2020_projected_dealer_class,jun_2020_projected_dealer_class,jul_2020_projected_dealer_class,aug_2020_projected_dealer_class," & _
" current_clas_awtu_target,current_class_sellin_target,current_class_awmi_target,dealer_status,disc_date,ambitious_dealer?_y/n,shopfront_signage,may_2020_mnp_awtu10,jun_2020_mnp_awtu10,jul_2020_mnp_awtu10,aug_2020_mnp_awtu10,may_2020_ereload_sellin,jun_2020_ereload_sellin,jul_2020_ereload_sellin,aug_2020_ereload_sellin,may_2020_hero_sell_through,jun_2020_hero_sell_through,jul_2020_hero_sell_through,aug_2020_hero_sell_through" & _
" ,may_2020_ga_with_ocr,jun_2020_ga_with_ocr,jul_2020_ga_with_ocr,aug_2020_ga_with_ocr)values" & _
" ('" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "','" & row.Cells(3).Value & "', '" & row.Cells(4).Value & "', '" & row.Cells(5).Value & "','" & row.Cells(6).Value & "', '" & row.Cells(7).Value & "', '" & row.Cells(8).Value & "','" & row.Cells(9).Value & "', '" & row.Cells(10).Value & "', '" & row.Cells(11).Value & "','" & _
row.Cells(12).Value & "', '" & row.Cells(13).Value & "', '" & row.Cells(14).Value & "','" & row.Cells(15).Value & "', '" & row.Cells(16).Value & "', '" & row.Cells(17).Value & "','" & row.Cells(18).Value & "', '" & row.Cells(19).Value & "', '" & row.Cells(20).Value & "','" & row.Cells(21).Value & "', '" & row.Cells(22).Value & "', '" & row.Cells(23).Value & "','" & row.Cells(24).Value & "', '" & row.Cells(25).Value & "', '" & _
row.Cells(26).Value & "','" & row.Cells(27).Value & "', '" & row.Cells(28).Value & "', '" & row.Cells(29).Value & "','" & row.Cells(30.Value & "', '" & _
row.Cells(31).Value & "', '" & row.Cells(32).Value & "','" & row.Cells(33).Value & "', '" & row.Cells(34).Value & "', '" & row.Cells(35).Value & "','" & row.Cells(36).Value & "', '" & row.Cells(37).Value & "', '" & row.Cells(38).Value & "','" & _
row.Cells(39).Value & "', '" & row.Cells(40).Value & "', '" & row.Cells(41).Value & "','" & row.Cells(42).Value & "', '" & row.Cells(43).Value & "', '" & row.Cells(44).Value & "','" & row.Cells(45).Value & "', '" & row.Cells(46).Value & "', '" & row.Cells(47).Value & "','" & row.Cells(48).Value & "', '" & row.Cells(49).Value & "', '" & row.Cells(50).Value & "','" & row.Cells(51).Value & "', '" & row.Cells(52).Value & "', '" & row.Cells(53).Value & "','" & row.Cells(54).Value & "', '" & row.Cells(55).Value & "', '" & _
row.Cells(56).Value & "','" & row.Cells(57).Value & "', '" & row.Cells(58).Value & "', '" & row.Cells(59).Value & "','" & row.Cells(60).Value & "', '" & row.Cells(61).Value & "')"

You'll notice that now each line is enclosed in quotation marks, followed by a space and underscore. With the formatting on SO, the underscore is now white in colour showing it's not part of the string literal anymore (which is red) as it's not enclosed in the quotation marks any longer.

With saying all that, I'd agree with the answer from Brax that it'd be 'better' to break your string down into smaller pieces and concatenate the smaller chunks together and the use of parameters to help reduce string size.

VBA handles [line-continuation characters](https://learn.microsoft.com/en-gb/office/vba/language/glossary/vbe-glossary#line-continuation-character) differently. To extend a string over multiple lines in VBA, you need to use `& _` (i.e. concatenation followed by a space followed by line-continuation) — barrowc, Jul 09 '20 at 02:29
@barrowc good pick up, 100% overlooked that! Will edit, thanks. — Samuel Everson, Jul 09 '20 at 02:38
@barrowc though it is worth pointing out the VBE glossary also states *The combination of a space followed by an underscore (_)...* - however I did notice you need to concatenate if you are continuing a string, but if say you are separating message box parameters you don't need to concatenate between the string (say for the message) and the button types for example. — Samuel Everson, Jul 09 '20 at 02:44
Thanks for your sharing and explaination, very good learning from you — robin, Jul 09 '20 at 11:57

How to apply line continuation where line too long?

3 Answers3