1.  数组赋值及取不重复值 By:山菊花

Private Sub CommandButton1_Click()
‘http://club.excelhome.net/dispbbs.asp?boardid=2&replyid=569457&id=196095&page=1&skin=0&Star=2
‘数组入门11.xls
    Dim xm() As String, Arr() As String, Temp() As String '声明变量
    Dim s%, r% '声明单值变量
    On Error Resume Next '启动一个错误处理程序
    xm = Split(Range("a1"), ",") '用A1单元格的数据创建数组
    r = 0 '初值
    s = UBound(xm) '最大下标
    For i = 0 To s '循环，数组中取不重复值
        Temp = Filter(Arr, xm(i)) '搜索数组
        If UBound(Temp) = -1 Then '如果未找到（数组的上限＝－1，说明该数组为空，即没有元素）
            r = r + 1 '序号，自增1
            ReDim Preserve Arr(1 To r) '定义动态数组大小
            Arr(r) = xm(i) '把姓名复制到数组Arr()中。
        End If
    Next
    Range("a2").Resize(1, r) = Arr '填充到工作表的一行之中
    'Range("a2") = Join(Arr, ",") '填充到工作表的A2之中
End Sub
 
By:Unarthur
取工作表区域的绝对引用公式到数组：arr=[a1:c5].Formula
 
2，取工作表区域的相对引用公式到数组：arr=[a1:c5].Formular1c1
 
3，取工作表区域的转置到数组：arr=Application.Transpose([a1:c5]) 数组间也可以转置：arr1=Application.Transpose(arr)
 
4，取数组arr的第n列赋值到某列区域：[e1:e5]=Application.Index(arr, , n) 或者赋值产生一个新数组：arr1=Application.Index(arr, , n)
 
5，同理，取数组arr的第n行赋值到某行区域：[a6:c6]=Application.Index(arr,n ) 或者赋值产生一个新数组：arr1=Application.Index(arr, n )
数组技巧.xls
Private Sub CommandButton1_Click()
    arr = [a4:c8].Formula '将公式保存到数组
   
    [e4:g8] = arr
   
End Sub
 
Private Sub CommandButton2_Click()
    arr = [a13:c17].FormulaR1C1 '将公式保存到数组
    [e13:g17] = arr
End Sub
 
Private Sub CommandButton3_Click()
arr = Application.Transpose([a22:c26])
[e22:i24] = arr
End Sub
 
Private Sub CommandButton4_Click()
 arr = [e22:i24]
[k22:m26] = Application.Transpose(arr)
End Sub
 
Private Sub CommandButton5_Click()
arr = [a32:c36]
[e32:e36] = Application.Index(arr, , 3)
End Sub
 
Private Sub CommandButton6_Click()
arr = [a42:c46]
[e42:g42] = Application.Index(arr, 3)
End Sub