[vba] How to break long string to multiple lines

I'm using this insert statement in my code in vba excel but i'm not able to break it into more than one line

SqlQueryString = "Insert into Employee values(" & txtEmployeeNo.Value & " _
,'" & txtContractStartDate.Value & "' _
,'" & txtSeatNo.Value & "' _
,'" & txtFloor.Value & "','" & txtLeaves.Value & "')"

It is giving error "Expected end of statement". Plz help.

This question is related to vba excel

The answer is


you may simply create your string in multiple steps, a bit redundant but it keeps the code readable and maintain sanity while debugging or editing

SqlQueryString = "Insert into Employee values(" 
SqlQueryString = SqlQueryString & txtEmployeeNo.Value & " ,"
SqlQueryString = SqlQueryString & " '" & txtEmployeeNo.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtContractStartDate.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtSeatNo.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtContractStartDate.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtSeatNo.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtFloor.Value & "',"
SqlQueryString = SqlQueryString & " '" & txtLeaves.Value & "' )"

You cannot use the VB line-continuation character inside of a string.

SqlQueryString = "Insert into Employee values(" & txtEmployeeNo.Value & _
"','" & txtContractStartDate.Value &  _
"','" & txtSeatNo.Value & _
"','" & txtFloor.Value & "','" & txtLeaves.Value & "')"

I know that this is super-duper old, but on the off chance that someone comes looking for this, as of Visual Basic 14, Vb supports interpolation. Sooooo cool!

Example:

SQLQueryString = $"
   Insert into Employee values( 
       {txtEmployeeNo}, 
       {txtContractsStartDate},
       {txtSeatNo},
       {txtFloor},
       {txtLeaves}
)"

It works. Documentation Here

Edit: After writing this, I realized that the OP was talking about VBA. This will not work in VBA!!! However, I will leave this up here, because as someone new to VB, I stumbled upon this question looking for a solution to just this problem in VB.net. If this helps someone else, great.


If the long string to multiple lines confuses you. Then you may install mz-tools addin which is a freeware and has the utility which splits the line for you.

Download Mz-tools

If your string looks like below

SqlQueryString = "Insert into Employee values(" & txtEmployeeNo.Value & "','" & txtContractStartDate.Value & "','" & txtSeatNo.Value & "','" & txtFloor.Value & "','" & txtLeaves.Value & "')"

Simply select the string > right click on VBA IDE > Select MZ-tools > Split Lines

enter image description here