在VBA代码中编写长数组时,如何处理换行?

huangapple go评论145阅读模式
英文:

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

问题

制作一个VBA代码,用于从公司名称中删除公司缩写。例如:

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

尝试了以下代码行:

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

因为我有一个很长的公司缩写列表(超过100个,涉及多个国家),似乎VBA编辑器不接受换行。

如果所有的值都在Array()函数中连续排列,它是可以工作的。但是我无法将它们都放在一行中。这行代码必须换行以容纳多个值。我尝试编辑数组,但没有成功。

在VBA代码中编写长数组时,如何处理换行?

这里有什么解决办法?

英文:

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.

在VBA代码中编写长数组时,如何处理换行?

What would be a solution here?

答案1

得分: 2

选项1 - 在每一行中向数组中添加一个新值

Sub TestMe()

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

End Sub

选项2 - 链接

Sub TestMe()

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

选项3 - 链接

将这些值写入Excel范围,然后读取该范围并将其转换为数组。这可能是最好的选择,因为用户可以在Excel中更改它,而无需输入代码:

Sub TestMe()

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

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 - https://stackoverflow.com/questions/22854386/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 - https://stackoverflow.com/questions/37689847/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:

在VBA代码中编写长数组时,如何处理换行?

Sub TestMe()

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

huangapple
  • 本文由 发表于 2023年8月8日 22:24:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76860486.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定