一个字符串是VBA中可以迭代的数组吗? [英] Is a string an array in VBA that can be iterated through?
问题描述
例如,我可以像C / C ++一样迭代吗?
执行如下操作:
char myArray [10];对于(int i = 0; i< length; i ++)
{
cout<< myArray的[I];
}
VBA中的等价物是什么?它不像我所期望的那样行事。在VBA之前从来没有试过操纵很多的字符串! :
这不是一个数组,而是可以使用 MID
Sub test()
Dim strSentence As String
Dim lngCount As Long
strSentence =这是一个字符串
对于lngCount = 1到Len(strSentence)
Debug.Print Mid(strSentence,lngCount,1)
Next lngCount
End Sub
另请参见 Mat's Mug answer ,以获得更多优点。
编辑
实际上,有一种方法来遍历一个字符串,即将字符串转换为一个数字的字符串数组,然后迭代通过他们。为此,我们必须将原始字符串转换为unicode格式,以便将Null字符作为分隔符。这是一个可重现的例子:
Sub test2()
Dim strSentence As String
Dim lngCount As Long
Dim strArray()As String
strSentence =这是一个字符串
strSentence = StrConv(strSentence,vbUnicode)
strArray = Split strSentence,vbNullChar)
对于lngCount = 0到UBound(strArray)
Debug.Print strArray(lngCount)
下一页lngCount
End Sub
出于好奇,我比较了两种方法(使用Mat的Mid版本,速度更快):
Sub test_sub()
Dim strTest(1 To 5000)As String
Dim lngStringIter As Long
Dim lngChars As Long,dblTick As Double
'生成一些长字符串
对于lngStringIter = 1到5000
strTest(lngStringIter)= vbNullChar
对于lngChars = 1到10
strTest(lngStringIter)= strTest(lngStringIte r)& _
Chr(Int((90 - 65 + 1)* Rnd + 65))& strTest(lngStringIter)
下一页lngChars
下一页lngStringIter
'看看会发生什么..
dblTick = CDbl(Now())
对于lngStringIter = 1到5000
测试strTest(lngStringIter)
下一个lngStringIter
Debug.PrintTime Mid:,CDbl(Now()) - dblTick
dblTick = CDbl (Now())
对于lngStringIter = 1到5000
test2 strTest(lngStringIter)
下一个lngStringIter
Debug.PrintTime Split:,CDbl(Now()) - dblTick
End Sub
结果:
时间分割:1.15740767796524e-05
所以看起来Split方法有点快一些。
VBA字符串是实施为 BSTR数据类型。有关此数据类型的更多信息,请参见此处和此处。
Is a string an array in VBA?
For instance, can I iterate through it like I could in C/C++ ?
Do something like this:
char myArray[10];
for (int i = 0; i < length; i++)
{
cout << myArray[i];
}
What would the equivalent be in in VBA? It is not behaving as I would expect. Never actually tried to manipulate many strings before in VBA! :)
It is not an array but what you want can be done using MID
Sub test()
Dim strSentence As String
Dim lngCount As Long
strSentence = "This is a string"
For lngCount = 1 To Len(strSentence)
Debug.Print Mid(strSentence, lngCount, 1)
Next lngCount
End Sub
See also Mat's Mug answer for an additional good point.
Edit
Actually there is a second way to iterate through a string, which is to convert the string to an array of single-digit strings and then iterate through them. To this end, we fist have to convert the original string to unicode format so that the Null character can be taken as delimiter. Here is a reproducible example:
Sub test2()
Dim strSentence As String
Dim lngCount As Long
Dim strArray() As String
strSentence = "This is a string"
strSentence = StrConv(strSentence, vbUnicode)
strArray = Split(strSentence, vbNullChar)
For lngCount = 0 To UBound(strArray)
Debug.Print strArray(lngCount)
Next lngCount
End Sub
Out of curiosity, I compared the two approaches (using Mat's version of Mid, which is faster):
Sub test_sub()
Dim strTest(1 To 5000) As String
Dim lngStringIter As Long
Dim lngChars As Long, dblTick As Double
' Generate some long strings first
For lngStringIter = 1 To 5000
strTest(lngStringIter) = vbNullChar
For lngChars = 1 To 10
strTest(lngStringIter) = strTest(lngStringIter) & _
Chr(Int((90 - 65 + 1) * Rnd + 65)) & strTest(lngStringIter)
Next lngChars
Next lngStringIter
' Lets see what happens..
dblTick = CDbl(Now())
For lngStringIter = 1 To 5000
test strTest(lngStringIter)
Next lngStringIter
Debug.Print "Time Mid: ", CDbl(Now()) - dblTick
dblTick = CDbl(Now())
For lngStringIter = 1 To 5000
test2 strTest(lngStringIter)
Next lngStringIter
Debug.Print "Time Split: ", CDbl(Now()) - dblTick
End Sub
Results:
Time Mid: 4.62962998426519e-05
Time Split: 1.15740767796524e-05
So it seems that the Split approach is somewhat faster.
A VBA string is implemented as a BSTR datatype. More info for this data type can be found here and here.
这篇关于一个字符串是VBA中可以迭代的数组吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!