excel 中BVA 麻煩大佬幫忙解釋一下

大龍媛

Option Compare Text
8 C/ B& d1 M2 D
Public Function GSXS(Ref)

    GSXS = Ref.Formula
& W, e; N- n  [! z: y
End Function
. o5 `: V1 `' Q4 o
, C2 s/ T* x' w6 F! H* u- P! J# DPublic Function ZZL(RowHead, ColHead, Dummy)

Dim Values(20) As Variant
' _- {7 Q% b" `7 gDim PrevData(20) As Variant
/ V1 K2 M1 q. d' q2 KDim LE(20) As Integer

On Error GoTo err_handler1
' Do the vertical selection from rows
If RowHead.Rows.Count = 1 Then
" [; T' Z( E7 I9 \7 _    rindex = RowHead.Row    ' first argument is any cell on the row of possible values
Else
    ' Store the values to be compared with each column
    For ii = 1 To RowHead.Columns.Count
        rngname = RowHead.Cells(1, ii)
, h  c2 j! C- T* ~$ P: a        LE(ii) = InStr(rngname, "<=")
        If LE(ii) > 0 Then
            rngname = Mid(rngname, 1, LE(ii) - 1)
; L+ f9 F* A* b8 p+ S) d        End If
        Values(ii) = Range(rngname)
        'debug.Print "Variable:" & rngname & " is:" & Values(ii)
        PrevData(ii) = ""   ' initialise
    Next ii
$ Q$ S: v' |0 M. X
    rindex = 2
    'debug.Print RowHead.Columns.Count
  n# C: Z$ u& c9 x: w# i0 |' Z( v    Match = False
1 l0 E6 S, Y9 e  W5 P9 w6 e0 Z  l    For r = rindex To RowHead.Rows.Count
! p8 s5 o2 Y  D6 F6 [        For c = 1 To RowHead.Columns.Count   ' for each dimension
' w) P' S6 T1 P            data = RowHead.Cells(r, c)
4 c: u0 J: U# j" @' y            If data = "" Then
$ R4 n2 E: ^: k: k; S7 I                'debug.Print "Empty cell found: using " & PrevData(c)
                ' use the last valid cell in this column
# O! S- r8 K: @9 G' h                ' (this is to handle merged cells)
6 x4 V" c( Q! p. ?0 w! p" V                data = PrevData(c)
& C5 r& |( S% Q( C* I0 p! X3 i            End If
            'debug.Print "data:" & data
# g/ M" `3 M! ^6 n            PrevData(c) = data ' save for use by empty cells
            If data = Values(c) Or (data > Values(c) And LE(c) > 0) Or data = "*" Then
: ~$ d/ Y4 P1 A" J                If c = RowHead.Columns.Count Then   ' All columns match - It's a go
+ ~4 i. s% ]7 T# x- e                    Match = True
                End If
            Else    ' This column doesn't match - go to the next row
) p7 }: Q: q/ n* q9 X                Match = False
                Exit For
            End If
        Next c
- {8 W9 o! ]! {1 V        If Match = True Then    ' Don't search any more rows
            rindex = r
$ v9 k- {1 E6 \0 x, X  e1 y* @/ P            Exit For
& ?& y: h, }! M" v        End If
  k0 N# V& F$ |6 z9 M    Next r

3 X5 k! i8 w9 S2 Y2 \" a    If Match = False Then   ' Didn't find a matching set of values
        ZZL = "No match for rows"
        Exit Function
  X7 w+ G* T) R" p/ X# ~  X. H$ m# L    End If
$ ^# X( v6 W" Z$ P: ]% o" _
    rindex = rindex + RowHead.Row - 1   ' make absolute index
! d4 U! d5 {- w7 `End If

" ~  z; k2 ]- \4 b' Do the horizontal selection from columns
If ColHead.Columns.Count = 1 Then
/ u. y& x& U# S; Y" {5 P$ ]    cindex = ColHead.Column
Else
/ k( M; `% x, x4 M+ E3 d    ' Store the values to be compared with each row of the header
" B0 z3 A0 I" V4 j8 \    For ii = 1 To ColHead.Rows.Count
) l: L- V& Y" m3 @$ y5 @        rngname = ColHead.Cells(ii, 1)
! Y& M9 s$ B8 W6 h# j) X; I1 I        LE(ii) = InStr(rngname, "<=")
        If LE(ii) > 0 Then
            rngname = Mid(rngname, 1, LE(ii) - 1)
        End If
        Values(ii) = Range(rngname)
        'debug.Print "Variable:" & rngname & " is:" & Values(ii)
/ \6 }# q+ Z5 m+ X' j/ j0 ]        PrevData(ii) = ""   ' initialise
    Next ii

; Y1 J# B7 [" i$ [( L    cindex = 2
    'debug.Print ColHead.Columns.Count
/ X3 t  b9 a9 j$ d1 i    Match = False
) x9 T' T  C8 t+ O5 Q6 L0 e: O    For c = cindex To ColHead.Columns.Count
) L5 E$ H% T: R  `! q+ L: x        For r = 1 To ColHead.Rows.Count   ' for each dimension
: y5 j1 N' j6 ]9 a0 k# B            data = ColHead.Cells(r, c)
7 z; e; }# X" n" `7 `9 t            If data = "" Then
                'debug.Print "Empty cell found: using " & PrevData(r)
& K" v- T" ~: }2 J0 U" K                ' use the last valid cell on this row
                ' (this is to handle merged cells)
1 }* r2 B6 K; ?" s! \1 R$ s                data = PrevData(r)
            End If
+ s3 }+ D3 z- |7 _7 k) `            'debug.Print "data:" & data
; M$ L& G- G! g8 F            PrevData(r) = data ' save for use by empty cells
            If data = Values(r) Or (data > Values(r) And LE(r) > 0) Or data = "*" Then
$ H( |1 N- h1 y5 ^' l                If r = ColHead.Rows.Count Then   ' All rows match - It's a go
9 E5 j1 _! g( m9 J2 e5 r/ O7 H! T                    Match = True
                End If
            Else    ' This row doesn't match - go to the next column
                Match = False
' x( b3 q2 t, `$ q& W# |7 e( t                Exit For
' R$ I# k8 z9 _            End If
) o' ~5 k+ A7 ^, k        Next r
3 `& T! W' n3 n2 C) J        If Match = True Then    ' Don't search any more columns
            cindex = c
            Exit For
        End If
    Next c

    If Match = False Then   ' Didn't find a matching set of values
        ZZL = "No match for columns"
        Exit Function
    End If

    cindex = cindex + ColHead.Column - 1
  V3 V$ [+ I2 TEnd If

3 a* v' C" Q3 z3 {7 z- c' Return the cell value from Table
+ I' K% ^4 {( K" _8 F5 U: ?. N'debug.Print "Answer is in (R,C):  " & rindex, cindex
- A/ ]- C, R5 a* v7 P6 VZZL = ActiveSheet.Cells(rindex, cindex)
'debug.Print "Answer is : " & ZZL
6 g0 ~; _+ W+ W7 x3 x5 CExit Function
& [  B7 G. R! B+ |
7 H! O; f, u) {9 Xerr_handler1:
ZZL = "Error on range '" & rngname & "'"

End Function

大龍媛

本人是小白，想請教大佬，如何能看懂以上信息

魍者歸來

微軟官方的bbs里是有專業(yè)的VBA教程和API端口說明能檢索的（全英文）

機(jī)器畫家

上excel論壇問問看