GBKEE

¦^´_ 4# temple

¬O³o¼Ë¶Ü?

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim myarray1(2 To 4, 2 To 4) As Single
   
4.     Dim i As Integer, j As Integer
   
5.     i = UBound(myarray1, 1)
   
6.     i = i - LBound(myarray1, 1) + 1 '²Ä¤@ºûªº¤¸¯À¼Æ
   
7.     j = UBound(myarray1, 2)
   
8.     j = j - LBound(myarray1, 2) + 1 '²Ä¤Gºûªº¤¸¯À¼Æ
   
9.    
   
10.     myarray1(2, 2) = 1
    
11.     myarray1(2, 3) = 1
    
12.     myarray1(2, 4) = 3
    
13.    
    
14.     myarray1(3, 2) = 1
    
15.     myarray1(3, 3) = 1
    
16.     myarray1(3, 4) = 2
    
17. 
    
18.     myarray1(4, 2) = 1 / 3
    
19.     myarray1(4, 3) = 1 / 2
    
20.     myarray1(4, 4) = 1
    
21. 
    
22.     With Range("C5")                '«ü©w·¡åJ®æ¦ì¸m
    
23.         .Resize(i, j) = myarray1    'ÂX¥Rªº½d³ò«ü©w¬°myarray1ªº­È
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 6# temple
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim myarray1(2 To 4, 2 To 4) As Single
   
4.     Dim AR As Variant
   
5.     Dim i As Integer, j As Integer
   
6.    
   
7.     myarray1(2, 2) = 10
   
8.     myarray1(2, 3) = 10
   
9.     myarray1(2, 4) = 3
   
10.    
    
11.     myarray1(3, 2) = 5
    
12.     myarray1(3, 3) = 17
    
13.     myarray1(3, 4) = 2
    
14. 
    
15.     myarray1(4, 2) = 1 / 3
    
16.     myarray1(4, 3) = 1 / 2
    
17.     myarray1(4, 4) = 1
    
18.    
    
19.     AR = myarray1
    
20.     ReDim Preserve AR(LBound(myarray1) To UBound(myarray1), LBound(myarray1, 2) To UBound(myarray1, 2) + 1)
    
21.    
    
22.     For i = LBound(AR) To UBound(AR)
    
23.         AR(i, UBound(AR, 2)) = Application.Product(Application.WorksheetFunction.Index(myarray1, i - LBound(AR) + 1))
    
24.     Next
    
25.    
    
26.     i = UBound(AR, 1)
    
27.     i = i - LBound(AR, 1) + 1 '²Ä¤@ºûªº¤¸¯À¼Æ
    
28.     j = UBound(AR, 2)
    
29.     j = j - LBound(AR, 2) + 1 '²Ä¤Gºûªº¤¸¯À¼Æ
    
30.    
    
31.     With ActiveSheet.Range("C5")     '«ü©w·¡åJ®æ¦ì¸m
    
32.         .Resize(i, j) = AR      'ÂX¥Rªº½d³ò«ü©w¬°Arªº­È
    
33.     End With
    
34. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 8# temple

ReDim ³¯­z¦¡¦bµ{§Ç¼h¦¸¤¤¥Î¨Ó­«·s°t¸m°ÊºA°}¦CÅܼƪºÀx¦sªÅ¶¡¡C
»yªk
ReDim [Preserve] varname(subscripts) [As type] [, varname(subscripts) [As type]] . . .
Preserve ¿ï¾Ü©Ê¤Þ¼Æ¡C·í§ïÅܭ즳°}¦C³Ì«á¤@ºûªº¤j¤p®É¡A¤´µM«O¦³­ì¨Óªº¸ê®ÆªºÃöÁä¦r¡C

1. 
   
2. Option Explicit
   
3. Sub EX()
   
4.     Dim AR()
   
5.     AR = [A1:F2].Value '-> AR(1 To 2, 1 To 6)
   
6.    
   
7.     ReDim Preserve AR(1 To 2, 1 To 7)
   
8. End Sub
   

½Æ»s¥N½X

¤U¸ü (43.67 KB)

2015-3-5 09:16

¤U¸ü (65.15 KB)

2015-3-5 09:16

GBKEE

¦^´_ 11# temple

1. '**************'¤u§@ªí¤W¼Æ­ÈªºÅÜ°Ê*************************
   
2. Option Explicit
   
3. Private Sub Worksheet_Change(ByVal Target As Range)
   
4.     Application.EnableEvents = False
   
5.     If Not Intersect(Range("a1:e3"), Target.Cells(1)) Is Nothing Then
   
6.         '¼Æ­Èªº§ïÅܦb "a1:e3" ªº½d³ò¤¤
   
7.         Ex_¤u§@ªí¤Wªº­«ºâ
   
8.     End If
   
9.     Application.EnableEvents = False
   
10. End Sub
    
11. Sub Ex_¤u§@ªí¤Wªº­«ºâ()
    
12.     Dim myarray1(), ar(), i As Integer
    
13.     With Range("a1:d3")
    
14.         For i = 1 To .Rows.Count
    
15.             Rows(i).Cells(1, "E") = Application.Product(.Rows(i))
    
16.         Next
    
17.     End With
    
18. End Sub
    
19. '*************'°}¦C¼Æ­È§ïÅÜ *******************************
    
20. Option Explicit
    
21. Dim Ar_Key As String, myarray1()
    
22. Sub Ex()
    
23.     Dim i As Integer, S As String
    
24.     myarray1() = Range("a1:d3").Value   '°}¦C¼Æ­È
    
25.     For i = 1 To UBound(myarray1)
    
26.         S = S & Join(Application.Index(myarray1, i), ",") & ","
    
27.     Next
    
28.     If S <> Ar_Key Then  '¤ñ¹ï°}¦C¼Æ­È
    
29.         Ex_­«ºâ
    
30.         Ex_°}¦C¬ö¿ý
    
31.     End If
    
32. End Sub
    
33. Sub Ex_­«ºâ()
    
34.     Dim myarray1(), ar(), i As Integer
    
35.     myarray1() = Range("a1:d3").Value
    
36.    
    
37.     ReDim ar(1 To UBound(myarray1))
    
38.     For i = 1 To UBound(ar)
    
39.         ar(i) = Application.Product(Application.Index(myarray1, i))
    
40.     Next
    
41.     Range("e1:e3") = Application.WorksheetFunction.Transpose(ar)
    
42. End Sub
    
43. Private Sub Ex_°}¦C¬ö¿ý()  '
    
44.     Dim i As Integer
    
45.     myarray1() = Range("a1:d3").Value
    
46.     Ar_Key = ""
    
47.     For i = 1 To UBound(myarray1)
    
48.         Ar_Key = Ar_Key & Join(Application.Index(myarray1, i), ",") & ","
    
49.         '°}¦C¸ê®Æ¬ö¿ý¤U¨Ó
    
50.     Next
    
51. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 16# temple
Matrix.xlsx  ¨S¦³¥¨¶°ªºExcel ÀÉ
Matrix.xlsm  ¦³¥¨¶°ªºExcel ÀÉ

GBKEE

¦^´_ 19# temple

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Application.EnableEvents = False  '°±¤îIJµo¨Æ¥ó
   
3.     If Not Intersect(Range("b2:e5"), Target.Cells(1)) Is Nothing Then
   
4.         '¼Æ­Èªº§ïÅܦb "a1:e3" ªº½d³ò¤¤
   
5.         Ex_¤u§@ªí¤Wªº­«ºâ
   
6.     End If
   
7.     Application.EnableEvents = True    '­n·|¦^´_¯àIJµo¨Æ¥ó
   
8.     'Application.EnableEvents = False  '³o¸Ì¿ù»~
   
9.    
   
10. End Sub

½Æ»s¥N½X