64

I would like to type the mathematical forumla in VBA code which many lines. I would like to split it into many lines. How do I do it?

For example:

U_matrix(i, j, n + 1) = k * b_xyt(xi, yi, tn) / (4 * hx * hy) * U_matrix(i + 1, j + 1, n) + (k * (a_xyt(xi, yi, tn) / hx ^ 2 + d_xyt(xi, yi, tn) / (2 * hx)))

is very long. would like to split it.

Tried this:

U_matrix(i, j, n + 1) = k * b_xyt(xi, yi, tn) / (4 * hx * hy) * U_matrix(i + 1, j + 1, n) 
_+ (k * (a_xyt(xi, yi, tn) / hx ^ 2 + d_xyt(xi, yi, tn) / (2 * hx)))

But not working.. Need some guidance on this..

lakshmen
  • 28,346
  • 66
  • 178
  • 276
  • Does it matter if the "_" is on the previous line? – RichS Apr 04 '14 at 05:41
  • No, it does not matter. A line continuation is a space character followed by an underscore character (_), then a line termination character (the Enter key) – Roy Latham Dec 31 '21 at 17:41

4 Answers4

108

To have newline in code you use _

Example:

Dim a As Integer
a = 500 _
  + 80 _
  + 90

MsgBox a
Brad Solomon
  • 38,521
  • 31
  • 149
  • 235
Stokke
  • 1,871
  • 2
  • 13
  • 18
  • 2
    Is there something that can be used for array initialization? If there's a newline after "_", it gives an error: Dim StringPairs As Variant StringPairs = [ {"abc", "def"}, _ {"123", "456"}] – John Pankowicz Jan 03 '18 at 04:36
  • 4
    It should be `" _"`, not `"_"`. – bers Mar 20 '21 at 11:43
  • 1
    On the contrary: if I want to combine 2 separate lines of code into 1 line, is there a way? (like `;` in a lot of languages) – JackeyOL Jul 28 '21 at 20:28
  • 2
    @JackeyOL regular colon `:` is used for that in VBA. For example `Dim a As Integer: a = 500 + 80 + 90: MsgBox a` – Stokke Aug 03 '21 at 10:09
29
(i, j, n + 1) = k * b_xyt(xi, yi, tn) / (4 * hx * hy) * U_matrix(i + 1, j + 1, n) + _
(k * (a_xyt(xi, yi, tn) / hx ^ 2 + d_xyt(xi, yi, tn) / (2 * hx)))

From ms support

To continue a statement from one line to the next, type a space followed by the line-continuation character [the underscore character on your keyboard (_)].

You can break a line at an operator, list separator, or period.

Helix Quar
  • 526
  • 6
  • 22
14

In VBA (and VB.NET) the line terminator (carriage return) is used to signal the end of a statement. To break long statements into several lines, you need to

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).

(From How to: Break and Combine Statements in Code)

In other words: Whenever the interpreter encounters the sequence <space>_<line terminator>, it is ignored and parsing continues on the next line. Note, that even when ignored, the line continuation still acts as a token separator, so it cannot be used in the middle of a variable name, for example. You also cannot continue a comment by using a line-continuation character.

To break the statement in your question into several lines you could do the following:

U_matrix(i, j, n + 1) = _
     k * b_xyt(xi, yi, tn) / (4 * hx * hy) * U_matrix(i + 1, j + 1, n) + _
     (k * (a_xyt(xi, yi, tn) / hx ^ 2 + d_xyt(xi, yi, tn) / (2 * hx)))

(Leading whitespaces are ignored.)

Community
  • 1
  • 1
IInspectable
  • 46,945
  • 8
  • 85
  • 181
  • 3
    Upvoted for explanation in addition to answer. Commenting to add that there is an additional step if you're trying to break up a string. If you want to have a string on two lines you need to close the quote; add an ampersand(&), space, and underscore(_); and start the new line with another quote. Remember to include a trailing space or leading space or you'll end up with a word mash where the two strings join. – PC_Goldman - SE is rotting Oct 05 '17 at 14:06
8

If you want to insert this formula =SUMIFS(B2:B10,A2:A10,F2) into cell G2, here is how I did it.

Range("G2")="=sumifs(B2:B10,A2:A10," & _

"F2)"

To split a line of code, add an ampersand, space and underscore.

trotta
  • 1,232
  • 1
  • 16
  • 23
Curt Grasso
  • 81
  • 1
  • 1
  • I'd just add that this particular method is only required in VBA if you are splitting in the middle of a literal string. Otherwise, you just need a space and an underscore. – Trashman Apr 27 '21 at 16:40