Function() ¤@ª½·d¤£À´

Andy2483

¦U¦ì«e½ú¦n
«á½úµoªíªº¾Ç²ß¥DÃD(³sµ²¦p¤U)·Q§ï¥ÎFunction ©I¥s¦r¨å¸ÌªºÄæ¦ì¹J¨ì²~ÀV
http://forum.twbts.com/thread-23764-1-1.html

°ÝÃD:
¤U¦CÃìµ²¥DÃD¸Ìªº½d¨Ò²¤Æµ{¦¡½X,¬°¤°»ò·| °ïÅ|ªÅ¶¡¤£¨¬ ªº°ÝÃD?

1. Option Explicit
   
2. Dim V
   
3. Function S(x)
   
4. Set V = CreateObject("Scripting.Dictionary")
   
5. V("¾Þ_NOÄæ") = 1
   
6. V("¾Þ_°O¿ý¤é´ÁÄæ") = 2
   
7. V("¾Þ_°O¿ý®É¶¡Äæ") = 3
   
8. V("¾Þ_IN_OUTÄæ") = 4
   
9. V("¾Þ_³Æ¥ÎÄæ") = 5
   
10. V("¾Þ_¹q¸ÜÄæ") = 6
    
11. V("¾Þ_¹}¥DÄæ") = 7
    
12. V("¾Þ_Ãdª«§OÄæ") = 8
    
13. V("¾Þ_Ãdª«¦WÄæ") = 9
    
14. V("¾Þ_¥Í¤éÄæ") = 10
    
15. V("¾Þ_¦~ÄÖÄæ") = 11
    
16. V("¾Þ_µù°O1Äæ") = 12
    
17. V("¾Þ_µù°O2Äæ") = 13
    
18. V("¾Þ_µù°O3Äæ") = 14
    
19. V("¾Þ_µù°O4Äæ") = 15
    
20. S(x) = V(x)
    
21. End Function
    
22. Sub TEST_20220922_1()
    
23. MsgBox S("¾Þ_NOÄæ")
    
24. End Sub

½Æ»s¥N½X

­ì¥Ñ1:
«á¾Ç¬O¾Ç²ß¥H¤U³oµ{¦¡½X¥i¥H©I¥s¦¨¥\

1. Function f(x)
   
2.     f = 2 * x ^ 4 - 4 * x ^ 3 - 3 * x ^ 2 + 7 * x - 2
   
3. End Function
   
4. Sub TEST_20220922_2()
   
5. MsgBox f(0)
   
6. End Sub

½Æ»s¥N½X

http://forum.twbts.com/viewthread.php?tid=23428&from=favorites
­ì¥Ñ2:
«á¾Ç¥Ø«e¥u·|¥Î¥H¤Uªº¤è¦¡

1. Option Explicit
   
2. Dim V
   
3. Sub S2()
   
4. Set V = CreateObject("Scripting.Dictionary")
   
5. V("¾Þ_NOÄæ") = 1
   
6. V("¾Þ_°O¿ý¤é´ÁÄæ") = 2
   
7. V("¾Þ_°O¿ý®É¶¡Äæ") = 3
   
8. V("¾Þ_IN_OUTÄæ") = 4
   
9. V("¾Þ_³Æ¥ÎÄæ") = 5
   
10. V("¾Þ_¹q¸ÜÄæ") = 6
    
11. V("¾Þ_¹}¥DÄæ") = 7
    
12. V("¾Þ_Ãdª«§OÄæ") = 8
    
13. V("¾Þ_Ãdª«¦WÄæ") = 9
    
14. V("¾Þ_¥Í¤éÄæ") = 10
    
15. V("¾Þ_¦~ÄÖÄæ") = 11
    
16. V("¾Þ_µù°O1Äæ") = 12
    
17. V("¾Þ_µù°O2Äæ") = 13
    
18. V("¾Þ_µù°O3Äæ") = 14
    
19. V("¾Þ_µù°O4Äæ") = 15
    
20. End Sub
    
21. Sub TEST_20220922_S2()
    
22. Call S2
    
23. MsgBox V("¾Þ_NOÄæ")
    
24. End Sub

½Æ»s¥N½X

ÁÂÁ¦U¦ì«e½ú«ü¾É

Andy2483

§ä¨ì¦Û¤vªº¿ù»~¤F:
S(x) = V(x)¬O¿ù»~ªº!
S = V(x)´N¯à°õ¦æ¤F!
ÁÂÁ½׾Â! ÁÂÁ¦U¦ì«e½ú!

Andy2483

¤µ¤Ñ²ß±o:¤£¥²­n­«½Æ©I¥sFunction ­È
Function()ÁöµM¥i¥H©I¥s¨ì»Ý­nªº­È
¦ý¬O¹B¥Î³o¨Ç­È®É»Ý­n¥t¥Î®e¾¹¸Ë,¤£µM¦b°j°é¸Ì¤@ª½­«½Æ©I¥s³o¨Ç­È,·|¦h¥X«Ü¦h®É¶¡

1. ³o¬O­ì©lµ{¦¡½X,­«½Æ©I¥s!°õ¦æ®É¶¡«Üªø
   
2. Private Sub TextBox1_Change()
   
3. Dim Arr, x
   
4. List7.Clear
   
5. List7.Locked = True
   
6. Le = Len(TextBox1.Text)
   
7. If Le < 3 Then
   
8.    Exit Sub
   
9. End If
   
10. Set xDic = CreateObject("Scripting.Dictionary")
    
11. Dim NM
    
12. For i = 1 To UBound(Szrr)
    
13.    If InStr(Szrr(i,  f("¾Þ_¹q¸ÜÄæ")), TextBox1.Text) Then
    
14.       NM = Szrr(i, f("¾Þ_¹q¸ÜÄæ"))
    
15.       xDic(NM) = ""
    
16.    End If
    
17. Next
    
18. If xDic.Count > 1 Then
    
19.    NM = ""
    
20. End If
    
21. mNo = 7
    
22. For i = 1 To xDic.Count
    
23.    List(mNo).AddItem WorksheetFunction.Index(xDic.keys, i)
    
24. Next
    
25. If xDic.Count > 1 Then
    
26.    Arr = Application.Transpose(xDic.keys)
    
27. End If
    
28. Set xDic = Nothing
    
29. With List(mNo)
    
30.    .Locked = False
    
31.    If .ListCount = 1 Then
    
32.       .SetFocus
    
33.       .ListIndex = 0
    
34.    End If
    
35. End With
    
36. List1.Clear
    
37. List1.Locked = True
    
38. mNo = 1
    
39. Set xDic = CreateObject("Scripting.Dictionary")
    
40. If NM = "" Then
    
41.    For i = 1 To UBound(Szrr)
    
42.       For x = 1 To UBound(Arr)
    
43.          If Szrr(i, f("¾Þ_¹q¸ÜÄæ")) = Arr(x, 1) Then
    
44.             xDic(Szrr(i, f("¾Þ_¹}¥DÄæ"))) = ""
    
45.          End If
    
46.       Next
    
47.    Next
    
48.    Else
    
49.       For i = 1 To UBound(Szrr)
    
50.          If Szrr(i, f("¾Þ_¹q¸ÜÄæ")) = NM Then
    
51.             xDic(Szrr(i, f("¾Þ_¹}¥DÄæ"))) = ""
    
52.          End If
    
53.       Next
    
54. End If
    
55. For i = 1 To xDic.Count
    
56.    List(mNo).AddItem WorksheetFunction.Index(xDic.keys, i)
    
57. Next
    
58. Set xDic = Nothing
    
59. With List(mNo)
    
60.    .Locked = False
    
61.    If .ListCount = 1 Then .SetFocus: .ListIndex = 0
    
62. End With
    
63. End Sub

½Æ»s¥N½X

1. ³o¬O¥ÎJ,k²±¸Ë©I¥s­È,¥u©I¥s¤@¦¸,®É¶¡¤j´TÁYµu
   
2. Private Sub TextBox1_Change()
   
3. Dim Arr, x, j, k
   
4. j = f("¾Þ_¹q¸ÜÄæ")
   
5. k = f("¾Þ_¹}¥DÄæ")
   
6. List7.Clear
   
7. List7.Locked = True
   
8. Le = Len(TextBox1.Text)
   
9. If Le < 3 Then
   
10.    Exit Sub
    
11. End If
    
12. Set xDic = CreateObject("Scripting.Dictionary")
    
13. Dim NM
    
14. For i = 1 To UBound(Szrr)
    
15.    If InStr(Szrr(i, j), TextBox1.Text) Then
    
16.       NM = Szrr(i, j)
    
17.       xDic(NM) = ""
    
18.    End If
    
19. Next
    
20. If xDic.Count > 1 Then
    
21.    NM = ""
    
22. End If
    
23. mNo = 7
    
24. For i = 1 To xDic.Count
    
25.    List(mNo).AddItem WorksheetFunction.Index(xDic.keys, i)
    
26. Next
    
27. If xDic.Count > 1 Then
    
28.    Arr = Application.Transpose(xDic.keys)
    
29. End If
    
30. Set xDic = Nothing
    
31. With List(mNo)
    
32.    .Locked = False
    
33.    If .ListCount = 1 Then
    
34.       .SetFocus
    
35.       .ListIndex = 0
    
36.    End If
    
37. End With
    
38. List1.Clear
    
39. List1.Locked = True
    
40. mNo = 1
    
41. Set xDic = CreateObject("Scripting.Dictionary")
    
42. If NM = "" Then
    
43.    For i = 1 To UBound(Szrr)
    
44.       For x = 1 To UBound(Arr)
    
45.          If Szrr(i, j) = Arr(x, 1) Then
    
46.             xDic(Szrr(i, k)) = ""
    
47.          End If
    
48.       Next
    
49.    Next
    
50.    Else
    
51.       For i = 1 To UBound(Szrr)
    
52.          If Szrr(i, j) = NM Then
    
53.             xDic(Szrr(i, k)) = ""
    
54.          End If
    
55.       Next
    
56. End If
    
57. For i = 1 To xDic.Count
    
58.    List(mNo).AddItem WorksheetFunction.Index(xDic.keys, i)
    
59. Next
    
60. Set xDic = Nothing
    
61. With List(mNo)
    
62.    .Locked = False
    
63.    If .ListCount = 1 Then .SetFocus: .ListIndex = 0
    
64. End With
    
65. End Sub

½Æ»s¥N½X

Andy2483

§ï¬°Call °Æµ{¦¡(Public«Å§i)¨ú±o­È,§ïFunction()©I¥s­È«Ü³Â·Ð
«á¾Ç¤ß¸Ì¯B¥X¤@­ÓºÃ°Ý
¥ÎFunction() ·Pı«D±`¼F®`
Function()¦³¤°»ò¯S§O¦n¥Î¤§³B¬OµLªk¨ú¥Nªº¶Ü?
«á¾Ç¾q¶w! ½Ð¦U¦ì«e½ú´£¥Ü¤@¤U!
§_«h¦A¾Ç¤Q¦~¤]µLªk°Ñ³z!!

Andy2483

§ï¬°Call °Æµ{¦¡(Public«Å§i)¨ú±o­È,§ïFunction()©I¥s­È«Ü³Â·Ð
«á¾Ç¤ß¸Ì¯B¥X¤@­ÓºÃ°Ý
¥ÎFunction() ·Pı«D ...
Andy2483 µoªí©ó 2022-9-22 15:58

ÁÂÁ½׾¦U¦ì«e½ú¤À¨É³o»ò¦h©«¤l
Function()»PCall °Æµ{¦¡«ÜµL¶d
1.«á¾Ç¤ß¸Ì¯B¥XªººÃ°ÝÂk©S¬O¦Û¤v¾Ç±o¤Ó¤Ö,¤£À´Åܳq,¨S­ì«h!
2.¾\Äý«e½ú­Ìªº©«¤lµo²{,®É¶¡®Ä²v¤~¬O­«ÂI
3.¬°¤F°»´ú¨Ï¥ÎªÌ¦³¨S¦³ÅÜ°ÊÄæ¦ì,¨C¦¸³£ Function¦^¶Ç¥¿½TÄæ¦ì,®ö¶O®É¶¡
4.À³¸Ó­n§ï­ì«h:¨Ï¥ÎªÌÅÜ°ÊÄæ¦ìµ{¦¡´N¤£¯à¥Î
5.¦Óµ{¦¡³]­pªÌ¤]­n¹w¯dÅÜ°ÊÄæ¦ì®É,½ÆÂøªºµ{¦¡½X¸Ó¦p¦ó¥Î³Ìµu®É¶¡¦]À³
6.Public Const («Å§i¬°¦@¥Îªº©T©wÅܼÆ)¬O¥Ø«e«á¾Ç·Q¨ìªº¤èªk

1. Public Const ¾Þ_NOÄæ As Integer = 1
   
2. Public Const ¾Þ_°O¿ý¤é´ÁÄæ As Integer = 2
   
3. Public Const ¾Þ_°O¿ý®É¶¡Äæ As Integer = 3
   
4. Public Const ¾Þ_IN_OUTÄæ As Integer = 4
   
5. Public Const ¾Þ_³Æ¥ÎÄæ As Integer = 5
   
6. Public Const ¾Þ_¹q¸ÜÄæ As Integer = 6
   
7. Public Const ¾Þ_¹}¥DÄæ As Integer = 7
   
8. Public Const ¾Þ_Ãdª«§OÄæ As Integer = 8
   
9. Public Const ¾Þ_Ãdª«¦WÄæ As Integer = 9
   
10. Public Const ¾Þ_¥Í¤éÄæ As Integer = 10
    
11. Public Const ¾Þ_¦~ÄÖÄæ As Integer = 11
    
12. Public Const ¾Þ_µù°O1Äæ As Integer = 12
    
13. Public Const ¾Þ_µù°O2Äæ As Integer = 13
    
14. Public Const ¾Þ_µù°O3Äæ As Integer = 14
    
15. Public Const ¾Þ_µù°O4Äæ As Integer = 15
    
16. Sub TEST_20220923_1()
    
17. MsgBox ¾Þ_NOÄæ
    
18. End Sub

½Æ»s¥N½X

7.µ{¦¡³]­pªÌªº¦]À³¿ìªk¬O³Ì§Ö®É¶¡²£¥Í¥¿½Tªº©T©wÅܼƫŧi,¨ú¥N±¼Âªº©T©wÅܼƫŧi

1. Option Explicit
   
2. Sub ¦Û°Ê²£¥Í«Å§i¬°©T©w­È()
   
3. Dim i&, x&, »²§Uªí¼ÐÃDÄærr, »²§UªíÅܼÆÄærr, Coderr, ¦C¼Æ&, Y, ¾Þ_¼ÐÃDrr
   
4. Set Y = CreateObject("Scripting.Dictionary")
   
5. With Sheets("Code¥Í¦¨»²§Uªí")
   
6.    »²§Uªí¼ÐÃDÄærr = .Range(.[AA2], .Cells(Rows.Count, "AA").End(3))
   
7.    »²§UªíÅܼÆÄærr = .Range(.[AB2], .Cells(Rows.Count, "AB").End(3))
   
8. End With
   
9. ReDim Coderr(1 To UBound(»²§Uªí¼ÐÃDÄærr) * 5, 1 To 1)
   
10. Call ¾Þ§@ªíRC§P©w
    
11. 
    
12. With Sheets("¾Þ§@ªí")
    
13.    ¾Þ_¼ÐÃDrr = .Range(.[A13], .Cells(13, C))
    
14.    For x = 1 To C
    
15.       If Trim(¾Þ_¼ÐÃDrr(1, x)) = "" Then
    
16.          MsgBox "¤£¤¹³\¦³ªÅÄæ!"
    
17.          Exit Sub
    
18.       End If
    
19.       Y(¾Þ_¼ÐÃDrr(1, x)) = x
    
20.    Next
    
21. End With
    
22. For i = 1 To UBound(»²§Uªí¼ÐÃDÄærr)
    
23.    ¦C¼Æ = ¦C¼Æ + 1
    
24.    Coderr(¦C¼Æ, 1) = "Public Const ¾Þ_" & »²§UªíÅܼÆÄærr(i, 1) & "Äæ As Integer = " & Y(»²§Uªí¼ÐÃDÄærr(i, 1))
    
25. 1238
    
26. Next
    
27. Workbooks.Add
    
28. [A1].Resize(UBound(Coderr), UBound(Coderr, 2)) = Coderr
    
29. Set Coderr = Nothing
    
30. MsgBox "±N¬¡­¶Ã¯ªºµ{¦¡½X½Æ»s¨ì ¿ëÃÑÄæ¦ì¼ÐÃDMªºModule ¸Ì»\±¼­ì¨Óµ{¦¡½X!"
    
31. End Sub

½Æ»s¥N½X