Writing long array in VBA code, how to handle line jumps?

Question

Making VBA code which removes company acronyms from their names. Example:

Apple Inc. -> Apple
Tesla inc. -> Tesla

The following line of code was tried:

valuesToRemove = Array("Inc","SRL","Ltd","Limited","etc.")

Because I have a long list of those incorporation acronyms (more than 100 - its for many countries), it seems the VBA editor is not accepting line jumps.

If the values are all one next to an other in the Array() function it works. But I can not fit those in one single line. The line must jump to accommodate many values. I tried editing the Array but with no luck.

enter image description here

What would be a solution here?

Thank you.. This seem to work. The must be a space preceding the underscore. — Alexandre Brandt, Aug 08 '23 at 15:22

Vityata · Accepted Answer · 2023-08-08T14:47:37.820

Option 1 - Add a new value in the array on each line

Sub TestMe()

    Dim valuesToRemove(3) As Variant
    valuesToRemove(0) = "SRL"
    valuesToRemove(1) = "S. R. L,; o LTda. (Solo Chule)"
    valuesToRemove(2) = "LTD"

End Sub

Option 2 - How to continue the code on the next line in VBA

Sub TestMe()

    Dim valuesToRemove() As Variant
    valuesToRemove = Array("SRL", _
                        "S. R. L,; o LTda. (Solo Chule)", _
                        "LTD")
End Sub

Option 3 - Creating an Array from a Range in VBA

Write these values in an excel range and then read the range and convert it to an array. This is probably the best option, as a user is able to change it from their Excel, without entering the code:

Sub TestMe()

    Dim valuesToRemove As Variant
    valuesToRemove = Application.WorksheetFunction.Transpose(Sheet1.Range("A1:A3"))
                        
End Sub

Hello. Thanks for the suggestions. I took the Option 2 approach of using an underscore to jump lines. — Alexandre Brandt, Aug 09 '23 at 12:53

Writing long array in VBA code, how to handle line jumps?

1 Answers1