最近要做个项目,通过Office VBA来自动生成一系列的控件(包括文本框、直线等),并将这些控件组合在一起(这在Office里很好实现,只要选中这些控件,使用上下文菜单里的组合菜单项即可)。最开始的代码如下:
Dim
element1, element2
Dim i As Integer
Set element1 = Nothing
Set element2 = Nothing
For i = 0 To 7
Set element1 = Application.ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, i * 30 , 30 , 25 , 25 )
If Not (element2 Is Nothing ) Then
ActiveDocument.Shapes.Range(Array(element1.Name, element2.Name)).Select
Selection.ShapeRange.Group.Select
Set element2 = Selection.ShapeRange
Else
Set element2 = element1
End If
Next i
Dim i As Integer
Set element1 = Nothing
Set element2 = Nothing
For i = 0 To 7
Set element1 = Application.ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, i * 30 , 30 , 25 , 25 )
If Not (element2 Is Nothing ) Then
ActiveDocument.Shapes.Range(Array(element1.Name, element2.Name)).Select
Selection.ShapeRange.Group.Select
Set element2 = Selection.ShapeRange
Else
Set element2 = element1
End If
Next i
上面的代码生成了 8 个 TextBox ,将其两个为一组进行组合。这样做虽然从技术上没问题。但是如果生成的 TextBox 很多的话,如 1000 个,就会很慢。主要把时间消耗在了是用 VBA 进行组合操作上。因此,只要将组合的方式改为将所有控件都选中,然后组合一次就可以解决这个问题。在上面的代码中,使用了 Array 函数生成了 Variant 类型的数组。而使用 Array 函数是无法根据实际需要生成实际大小的数组的。因此,需要使用 dim 来定义这个数组,代码如下:
Dim
elements(
0
To
7
)
As
Variant
Dim i As Integer
For i = 0 To 7
elements(i) = Application.ActiveDocument.Shapes.
AddTextbox(msoTextOrientationHorizontal, i * 30 , 30 , 25 , 25 ).Name
Next i
ActiveDocument.Shapes.Range(elements).Select
Selection.ShapeRange.Group.Select
Dim i As Integer
For i = 0 To 7
elements(i) = Application.ActiveDocument.Shapes.
AddTextbox(msoTextOrientationHorizontal, i * 30 , 30 , 25 , 25 ).Name
Next i
ActiveDocument.Shapes.Range(elements).Select
Selection.ShapeRange.Group.Select
应使用Dim elements(
数组上标 to
数组下标)
的形式,不能使用Dim elements(
数组下标)
的形式。如上面的数组定义代码不能写成Dim elements(7) as Variant
。
如果在程序运行时改变数组的大小,可以使用如下的代码:
Dim
elements(
0
To
7
)
As
Variant
Dim newElements As Variant
Dim i As Integer
For i = 0 To 7
elements(i) = Application.ActiveDocument.Shapes.
AddTextbox(msoTextOrientationHorizontal, i * 30 , 30 , 25 , 25 ).Name
Next i
newElements = elements
ReDim Preserve newElements( 0 To 10 ) As Variant
For i = 8 To 10
newElements(i) = Application.ActiveDocument.Shapes.
AddTextbox(msoTextOrientationHorizontal, i * 30 , 200 , 25 , 25 ).Name
Next i
ActiveDocument.Shapes.Range(newElements).Select
Selection.ShapeRange.Group.Select
Dim newElements As Variant
Dim i As Integer
For i = 0 To 7
elements(i) = Application.ActiveDocument.Shapes.
AddTextbox(msoTextOrientationHorizontal, i * 30 , 30 , 25 , 25 ).Name
Next i
newElements = elements
ReDim Preserve newElements( 0 To 10 ) As Variant
For i = 8 To 10
newElements(i) = Application.ActiveDocument.Shapes.
AddTextbox(msoTextOrientationHorizontal, i * 30 , 200 , 25 , 25 ).Name
Next i
ActiveDocument.Shapes.Range(newElements).Select
Selection.ShapeRange.Group.Select
要注意的是,在使用 Redim 时,不能使用已经指定类型的数组,而需要将这个数组放到 Variant 变量中。如不能使用如下的代码来增加数组长度:
ReDim
Preserve
elements(
0
To
10
)
As
Variant
本文转自 androidguy 51CTO博客,原文链接:http://blog.51cto.com/androidguy/216658,如需转载请自行联系原作者