- 帖子
- 1018
- 主題
- 15
- 精華
- 0
- 積分
- 1058
- 點名
- 0
- 作業系統
- win7 32bit
- 軟體版本
- Office 2016 64-bit
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 桃園
- 註冊時間
- 2012-5-9
- 最後登錄
- 2022-9-28
|
4#
發表於 2012-12-25 15:32
| 只看該作者
回復 3# q1a2z5
自定義函數 , 複製下面代碼至一般模組
如在A1打上 =LightGame(C2,B4,B7,D7,D4)
會顯示C2,B4,B7 , 即是使五燈全亮所需開關的燈
須注意參數要依順時針或逆時針填入
代碼粗糙就不多解釋了- Function LightGame(in1, in2, in3, in4, in5) As String
- Dim adr(1 To 5)
- Dim d
- Dim init_s As String, op As String
- Dim i, j, k, m, ks
-
- adr(1) = in1.Address(0, 0)
- adr(2) = in2.Address(0, 0)
- adr(3) = in3.Address(0, 0)
- adr(4) = in4.Address(0, 0)
- adr(5) = in5.Address(0, 0)
-
- Set d = CreateObject("scripting.dictionary")
-
- init_s = in1 & "," & in2 & "," & in3 & "," & in4 & "," & in5 '初始狀態
- d.Add "0", init_s
-
- For i = 1 To 5
- For Each k In d.keys
- ks = Split(k, ",")
- For j = ks(UBound(ks)) + 1 To 5
- s = Split(d(k), ",")
- Select Case j - 1
- Case LBound(s)
- s(UBound(s)) = IIf(s(UBound(s)) = "亮", "滅", "亮")
- s(j - 1) = IIf(s(j - 1) = "亮", "滅", "亮")
- s(j) = IIf(s(j) = "亮", "滅", "亮")
- Case UBound(s)
- s(j - 2) = IIf(s(j - 2) = "亮", "滅", "亮")
- s(j - 1) = IIf(s(j - 1) = "亮", "滅", "亮")
- s(LBound(s)) = IIf(s(LBound(s)) = "亮", "滅", "亮")
- Case Else
- s(j - 2) = IIf(s(j - 2) = "亮", "滅", "亮")
- s(j - 1) = IIf(s(j - 1) = "亮", "滅", "亮")
- s(j) = IIf(s(j) = "亮", "滅", "亮")
- End Select
- d(k & "," & j) = Join(s, ",")
-
- If Join(s, ",") = "亮,亮,亮,亮,亮" Then
- ks = Split(k & "," & j, ",")
- For m = 1 To UBound(ks)
- If op = "" Then op = adr(ks(m)) Else op = op & "," & adr(ks(m))
- Next m
- LightGame = op
- Set d = Nothing
- Exit Function
- End If
- Next j
- Next k
- Next i
- End Function
|
|
