拆分函数返回的数组大小 - Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21773889/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Array Size Returned by Split Function - Excel VBA
提问by Kentot
Here is what I have done so far.
这是我到目前为止所做的。
Sub MP_division()
Worksheets(3).Activate
Dim last_cell As Integer
Dim platforms() As String
Dim arr_size As Integer
platforms = Split(Cells(2, 47), ", ")
last_cell = Mid(Range("A1048576").End(xlUp).Address, 4)
arr_size = len(platforms) // is there something like this?
For x = 1 To last_cell
For y = 1 To arr_size
//do something
Next
Next
End Sub
My question is, how may I get the array size(arr_size) that is returned by the split function, so that I could use in my for loop? Thank you.
我的问题是，如何获得 split 函数返回的数组大小（arr_size），以便我可以在 for 循环中使用？谢谢你。
回答by Dmitry Pavliv
Consider abound using LBoundand Ubound:
Sub MP_division()
Dim last_cell As Long
Dim platforms() As String
Dim x As Long, y As Integer
With ThisWorkbook.Worksheets(3)
platforms = Split(.Cells(2, 47), ", ")
last_cell = .Cells(.Rows.Count, "A").End(xlUp).Row
For x = 1 To last_cell
For y = LBound(platforms) To UBound(platforms)
'do something
Next
Next
End With
End Sub
Btw, Splitalways returns zero-basedarray (starts from 0, but not from 1). Thats why I recomend you to use both, Ubound and Lbound.
顺便说一句，Split总是返回从零开始的数组（从 开始0，但不是从1）。这就是为什么我建议您同时使用 Ubound 和 Lbound。
One more thing, I've changed Dim last_cell As Integerto Dim last_cell As Long, since max value of Integeronly 32767and if the last row would be greater than 32767, you would get an error with Integer.
还有一件事，我已更改Dim last_cell As Integer为Dim last_cell As Long，因为最大值Integer仅为32767，如果最后一行大于32767，您将收到Integer.
And avoid using Select/Active statements(it's about Worksheets(3).Activate)
并避免使用 Select/Active 语句（关于Worksheets(3).Activate）
回答by Shubham Arya
Dim first as integer
Dim last as integer
Dim arr() as string
Dim lengthOfArray as integer
'split your data and save it in arr'
first = LBound(arr)
last = UBound(arr)
lengthOfArray = last - first
msgbox lengthOfArray 'This will display the length of the array'
回答by farope
VBA LBound and UBound return the first and the last array position, so the correct answer is:
VBA LBound 和 UBound 返回第一个和最后一个数组位置，所以正确答案是：
size = UBound(myArray) - LBound(myArray) + 1
