英文:
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()
函数中连续排列,它是可以工作的。但是我无法将它们都放在一行中。这行代码必须换行以容纳多个值。我尝试编辑数组,但没有成功。
这里有什么解决办法?
英文:
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.
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:
Sub TestMe()
Dim valuesToRemove As Variant
valuesToRemove = Application.WorksheetFunction.Transpose(Sheet1.Range("A1:A3"))
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论