数组:定义数组:
Dim arr(30 To 50) As Single 定义数组,从30到50,定义为浮点型~
arr(30) = 34.3
产生随机数,但是随机数要不同(1到20之间产生10个不同的随机数)
Sub sdlkfjl()
For i = 1 To 10
Cells(1, i) = Int(1 + Rnd() * 19) '给个随机数
For j = 1 To i - 1
Do While Cells(1, j) = Cells(1, i) '判断这个数与前面的数是否相同,知道不相同退出循环
Cells(1, i) = Int(1 + Rnd() * 19) '若是相同,则重新产生随机数
Loop
Next
Next
End Sub
→ On Error Resume Next 说明当一个运行时错误发生时,控件转到紧接着发生错误的语句之后的语句,并在此继续运行。
→ Collection对象 可以去除重复值
在Collection对象中,由两部分组成,一部分是值的部分,一部分Key的部分,其中Key的部分是用来区分重复值的,若是相同的Key,则只取第 一个的值作为Collection的值,所以在写的时候,要是想去掉要素的重复值,可以直接将要素值作为Key值,但是Key必须为字符型,所以可以用 CStr函数做转换~
另外由于会有重复的Key值,所以要用On Error Resume Next语句来越过错误~
Private Sub UserForm_Initialize()
Dim r As Integer
Dim i As Integer
Dim MyCol As New Collection
Dim arr() As Variant
On Error Resume Next
With Sheet1
r = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To r
If Trim(.Cells(i, 1)) <> "" Then
MyCol.Add Item:=Cells(i, 1), key:=CStr(.Cells(i, 1))
End If
Next
End With
ReDim arr(1 To MyCol.Count) '最大值已经变了,因为有重复的值会变成不重复的~
For i = 1 To MyCol.Count
arr(i) = MyCol(i)
Next
ListBox1.List = arr
End Sub
→ 当ComboBox1发生Change的时候,触发如下事件
Private Sub ComboBox1_Change()
Dim MyAddress As String
Dim rng As Range
ComboBox2.Clear
With Sheet1.Range("A:A")
Set rng = .Find(What:=ComboBox1.Text)
If Not rng Is Nothing Then
MyAddress = rng.Address
Do
ComboBox2.AddItem rng.Offset(, 1)
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> MyAddress
End If
End With
ComboBox2.ListIndex = 0
Set rng = Nothing
End Sub
其中
rng.Address <> MyAddress
一句很重要,因为FindNext函数还会向上找回去,若是没有这个限制条件,就会一直循环下去~
→ VbCrlf和Chr(13)都是表示换行的意思
→ 按Ctrl+J可以提示方法和属性
→ 空格+下环线可以将一行分成多行来写 Space+Underscore
→ Array函数 返回一个包含数组的Variant
Dim A As Variant
A = Array(10,20,30)
B = A(2)
→ Split函数 返回一个小标从零开始的一维数组,它包含指定数目的子字符串
Sub SplitExample()
Dim Str() As String
Str = Split("a,b,c,d,e", ",")
For i = 0 To UBound(Str)
s = s & Str(i) & vbCrLf
Next
MsgBox s
End Sub
参考:http://club.excelhome.net/forum.php?mod=viewthread&tid=196095
单元格简单复制,将单元格的值存成数组
Private Sub CommandButton1_Click()
arr = Sheet2.Range("a1:e83")
Range("a1:e83") = arr
End Sub
→ Split函数&Join函数例子,从一个句子里面提取姓名和性别
Sub SplitExample()
Dim Str() As String
Cells(2, 1) = "全部"
Cells(2, 2) = "姓名"
Cells(2, 3) = "性别"
Str = Split(Cells(1, 1), ",")
For i = 0 To UBound(Str)
Cells(i + 3, 1) = Str(i)
If Right(Str(i), 3) = "(女)" Then
Cells(i + 3, 2) = Left(Str(i), InStr(Str(i), "(") - 1)
Cells(i + 3, 3) = "女"
Else
Cells(i + 3, 2) = Str(i)
Cells(i + 3, 3) = "男"
End If
Next
MsgBox Join(Str, ",")
End Sub
Join函数是Split函数的反向过程~
→ 动态数组
ReDim Preserve 数组名(UBound(数组名) + n)在扩大数组边界后,原来的值保留,否则会自动删掉
→ 数组复制
可以用“=”直接实现数组的复制,但是必须保证“=”左边的数组是动态数组