Board logo

標題: [發問] 如何在match中引用2維陣列 [打印本頁]
作者: yen956    時間: 2015-12-6 13:15     標題: 如何在match中引用2維陣列

本帖最後由 yen956 於 2015-12-6 13:16 編輯

下列VBA中,有一段match須引用1維陣列,
故先將2維陣列先轉成1維陣列再引用(紅色部分),
請問紅色部分如何正確引用(如何精簡)?
  1. Public arW, arL
  2. '取得寛度與長度界限的陣列, 供Match用
  3. Sub init()
  4.     ReDim arW(3) As Integer
  5.     ReDim arL(3, 3) As Integer
  6.     Dim W1 As Integer, L1 As Integer
  7.     arW(0) = Split(Cells(3, 3), "~")(1)    '寛度的上限
  8.     For W1 = 0 To 2
  9.         arW(W1 + 1) = Split(Cells(W1 * 3 + 3, 3), "~")(0) '寛度按降冪排
  10.         For L1 = 0 To 2
  11.             arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 3, 5), "~")(0)   '長度按升冪排
  12.         Next
  13.         arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 2, 5), "~")(1)     '長度的上限
  14.     Next
  15. End Sub

  17. Private Sub CommandButton1_Click()
  18.     Dim I As Integer, J As Integer, arL2(3) As Integer
  19.     Dim MHW, MHL, IDW As String, IDL As String
  20.     init
  21.     For I = 4 To [G4].End(xlDown).Row
  22.         MHW = Application.Match(Cells(I, 8), arW, -1)
  23.         IDW = Application.Index([B1:B11], MHW * 3, 1)
  24. [color=Red]        For J = 0 To 3
  25.             arL2(J) = arL(MHW - 1, J)    '將2維陣列轉為1維陣列
  26.         Next
  27.         MHL = Application.Match(Cells(I, 9), arL2, 1)[/color]
  28.         '長度代號分[D3:D5,D6:D9,D10:D12]三區
  29.         IDL = Application.Index([D3:D5,D6:D8,D9:D11], MHL, 1, MHW)
  30.         Cells(I, 10) = IDW & "_" & IDL
  31.     Next
  32. End Sub
複製代碼
(註:VBA可正常運作)
       For J = 0 To 3
            arL2(J) = arL(MHW - 1, J)    '將2維陣列轉為1維陣列
        Next
        MHL = Application.Match(Cells(I, 9), arL2, 1)

[attach]22725[/attach]
作者: yen956    時間: 2015-12-6 13:41

Sorry, 應該放在程式區才對, 只好將錯就錯!!
作者: 准提部林    時間: 2015-12-6 18:44

Function GET_WL(xA, Va, Vb) As String
Dim Arr, AA, j&, Jm%, T$
Arr = xA.Value
For j = 1 To UBound(Arr)
　　If Arr(j, 2) <> "" Then
　　　　Jm = 0:  AA = Split(Arr(j, 2) & "~", "~")
　　　　If Va > Val(AA(0)) And Va <= Val(AA(1)) Then T = Arr(j, 1): Jm = 1
　　End If
　　If Jm = 1 Then
　　　　AA = Split(Arr(j, 4) & "~", "~")
　　　　If Vb > Val(AA(0)) And Vb <= Val(AA(1)) Then GET_WL = T & "_" & Arr(j, 3): Exit For
　　End If
Next j
End Function

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
另一參考，自訂函數，彈性較好些：
Ｊ４．公式：=get_wl(B$3:E$11,H4,I4)
作者: yen956    時間: 2015-12-6 19:52

回復 3# 准提部林
謝謝准大, 非常好用又精簡的VBA,
正在用剛學來的監看視窗研究中,
對於如何使用2維陣列中的某一陣列的表達式
也在大大的這個VBA中得到解答, 謝謝!!
作者: yen956    時間: 2015-12-7 10:25

本帖最後由 yen956 於 2015-12-7 10:27 編輯

自言自語：
雖然在監看視窗中, 可以看得到二維陣列arL()
的一維表示式：arL(0),arL(1),..
但在VBA中郤不能使用arL(0),arL(1),..的表示式,
(會出現索引超出範圍的錯誤!)
故,二維陣列若要給match引用,還是要轉成一維陣列,
似無法精簡.
(當然, 准大的VBA非當精簡,又提供另一思路, 真的很不錯)
[attach]22732[/attach]
[attach]22733[/attach]
作者: stillfish00    時間: 2015-12-7 17:05

回復 5# yen956

[attach]22741[/attach]

Index 函數可以忽略第三參數，只取出某 row 成為一維陣列
但因原本該函數是設計給工作表 Range 用 , 故須注意 :
1. 二維陣列 row 數不可超過65536
2. 取出的一維陣列，index是由1開始而不是0
3. 二維陣列過大時，該函數效率很差。
作者: c_c_lai    時間: 2015-12-8 08:54

回復 5# yen956
我將准提部林版大的自訂函數稍稍修改了一些，
你用 [F8] 去 Debug 一次，便會全盤明瞭了。
我將結果改寫到 K 欄，以便你進行比對寫入值。
  1. '  自訂函數，彈性較好些：  By 准提部林
  2. '  Ｊ４．公式：=get_wl(B$3:E$11,H4,I4)
  3. Function GET_WL(xA As Variant, Va As Range, Vb As Range) As String
  4.     Dim Arr, AA, j&, Jm%, T$
  5.    
  6.     Arr = xA
  7.     For j = 1 To UBound(Arr)
  8.         If Arr(j, 2) <> "" Then
  9.             Jm = 0:  AA = Split(Arr(j, 2) & "~", "~")
  10.             If Va > Val(AA(0)) And Va <= Val(AA(1)) Then T = Arr(j, 1): Jm = 1
  11.         End If
  12.         If Jm = 1 Then
  13.             AA = Split(Arr(j, 4) & "~", "~")
  14.             If Vb > Val(AA(0)) And Vb <= Val(AA(1)) Then GET_WL = T & "_" & Arr(j, 3): Exit For
  15.         End If
  16.     Next j
  17. End Function

  19. Sub Ex()
  20.     Dim cts As Integer
  21.    
  22.     For cts = 0 To 5
  23.        [K4].Offset(cts) = GET_WL(Range("B$3:E$11"), [H4].Offset(cts), [I4].Offset(cts))
  24.     Next cts
  25. End Sub
複製代碼
作者: c_c_lai    時間: 2015-12-8 10:21

本帖最後由 c_c_lai 於 2015-12-8 10:24 編輯

回復 3# 准提部林
請教版大，
請問我在K11欄內輸入  =get_wl(B$3:E$11,H4,I4)
為何會出現 #REF! 字樣？
自訂函數在 Excel 要如何設定其定義讓它能被引用？
P.S. 真不好意思我對 Excel  如何使用內在函數，以及
       自訂函數 (Function) 如何在欄內應用方面所知有限。
謝謝您指教！
[attach]22744[/attach]
作者: 准提部林    時間: 2015-12-8 11:31

回復 8# c_c_lai


#REF與儲存格參照有關, 應與自訂函數無關, 若K4也是同公式卻可正常顯示, 就要查看看原因?
自訂函數大致與一般公式類似, 只是提供參數或儲存範圍讓程式去運算~~
作者: c_c_lai    時間: 2015-12-8 13:13

本帖最後由 c_c_lai 於 2015-12-8 13:21 編輯

回復 9# 准提部林
謝謝您回覆，我使用您#3樓的程式
測的手都軟了，還是一樣 (無論是在任何欄位，包含J4)
依然是 #REF!。甚至寫了兩支測試程式，都 PASS：
  1. Function AddCol(h, i) As String
  2.     AddCol = h + i
  3. End Function

  5. Function AddCol2(w, h, i) As String  '  故意帶入 w 參數
  6.     AddCol2 = h + i
  7. End Function
複製代碼
[attach]22747[/attach]
唯獨 =get_wl(B$3:E$11,H4,J4) 不行：
[attach]22748[/attach]
問題應該出在程式碼內部無法參照到 B$3:E$11，
但用 VBA 檢測也找不出問題點。
作者: 准提部林    時間: 2015-12-8 13:24

回復 10# c_c_lai


參考檔：
[attach]22749[/attach]

若還有問題，可將檔案上傳！！
作者: c_c_lai    時間: 2015-12-8 13:53

回復 11# 准提部林
能否幫我檢查看看，到底差異性在哪裡？
謝謝您！
[attach]22750[/attach]
作者: 准提部林    時間: 2015-12-8 14:01

回復 12# c_c_lai


=get_wl(B$3:E$11,H4,J4) 　＞　=get_wl(B$3:E$11,H4,I4)

第三個參數在ｉ欄，
不過就算用J4，公式結果為空白（因無符合區間值），也不會有#REF或#Value錯誤，
會不會是版本問題？（我是2000)
作者: c_c_lai    時間: 2015-12-8 14:33

回復 13# 准提部林
終於問題解決了，剛剛重新從#1下載
便直接做結果 OK了，再回到原先有問題的先前
問題檔案，怎麼做怎麼錯，極有可能那支檔案
裡有潛在問題，不管它了！
謝謝您耐心地回覆，再次謝謝！
作者: 准提部林    時間: 2015-12-8 14:47

回復 14# c_c_lai


是否可能 get_wl 已用在〔定義名稱〕，才會產生＃REF∼∼
作者: c_c_lai    時間: 2015-12-8 14:55

本帖最後由 c_c_lai 於 2015-12-8 14:59 編輯

回復 15# 准提部林
觀察前後檔案存檔後之的 Icon 顯示：
[attach]22753[/attach]
您提及的狀況也極有可能，謝謝您！
作者: yen956    時間: 2015-12-8 17:26

回復 6# stillfish00
謝謝stillfish00大大:
原來用 array + index就可以化2維陣列為1維陣列,
不必用For ...next轉換, 真是太好了, 謝謝!!
作者: yen956    時間: 2015-12-8 17:38

回復 7# c_c_lai
c大你好:
准大的function本來就相當不錯,
我的主題:如何將2維陣列化為1維陣列供 Match 用,
已在6#得到stillfish00大大的指導,
謝謝c大一再指導, 謝謝!!



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)