CODEWITHSUNDEEP
pythonvba
timbram
timbram

Reputation: 1865

Can VBA take a multiline string like Python can?

In Python, I can create a multi-line string by using """.

For instance:

x = """

Hello
World """"

Is there a way to do this with VBA?

Upvotes: 1

Views: 426

Answers (4)

omegastripes
omegastripes

Reputation: 12612

VBA syntax for that case should be

x = vbCrLf & _
    vbCrLf & _
    "Hello" & vbCrLf & _
    "World " & vbCrLf

But there is a limit of line continuations, for Excel 2010 it is 24 for me. If you try to insert one more line with line continuation into the below string

x = vbCrLf & _
    vbCrLf & _
    "Hello" & vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    "World " & vbCrLf

then you get the error message:

Too many line continuations

So the only way then to split the string to several multiline lines, as follows:

x = vbCrLf & _
    vbCrLf & _
    "Hello" & vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf
x = x & vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    vbCrLf & _
    "World " & vbCrLf

Upvotes: 0

Sam
Sam

Reputation: 43

This is an alternative if you want to comment a lot of code:

In the VBA editor, go to View, Toolbars, Customise... or right click on the tool bar and select Customise...

Under the Commands tab, select the Edit menu on the left.

Then approximately two thirds of the way down there's two icons, Comment Block and Uncomment Block.

Drag and drop these onto your toolbar and then you have easy access to highlight a block of code, and comment it out and uncomment with the click of a button!

Upvotes: 0

Ben McCormack
Ben McCormack

Reputation: 33088

This drives me insane every single time I want to work with a multi-line string in VBA, so I wrote a little Python script that reads from stdin and spits out VBA that you can copy/paste into your VBA editor.

make_vba_multiline.py:

import fileinput

print 'Dim str As String'
print 'str = ""'
to_print = []
for line in fileinput.input():
  line = line.strip('\n')
  line_vba = 'str = str & "%s"' % line.replace('"','""')
  to_print.append(line_vba)

all_lines = " & vbCrLf\n".join(to_print)
print all_lines

For example, if I have the following when I list the contents of helper:

$ ls -la helper/
total 8
drwxr-xr-x@ 3 ben  staff  102 Apr 19 14:03 .
drwxr-xr-x@ 7 ben  staff  238 Apr 19 14:03 ..
-rw-r--r--@ 1 ben  staff  262 Apr 19 13:56 make_vba_multiline.py

When I pipe it into the script, I get:

$ ls -la helper/ | python helper/make_vba_multiline.py
Dim str As String
str = ""
str = str & "total 8" & vbCrLf
str = str & "drwxr-xr-x@ 3 ben  staff  102 Apr 19 14:03 ." & vbCrLf
str = str & "drwxr-xr-x@ 7 ben  staff  238 Apr 19 14:03 .." & vbCrLf
str = str & "-rw-r--r--@ 1 ben  staff  262 Apr 19 13:56 make_vba_multiline.py"

Upvotes: 1

Mark Cidade
Mark Cidade

Reputation: 99957

No, VBA does not have multi-line strings.

Upvotes: 1

Related Questions