excel 中BVA 麻烦大佬帮忙解释一下

大龙媛

Option Compare Text
; F* s4 ~. A1 V
9 t5 T9 k9 |& aPublic Function GSXS(Ref)
4 v0 Y0 s' [% a3 Z  h. S4 ?6 F: C
, P+ j# t$ ^9 [; Q+ n1 P4 G. i    GSXS = Ref.Formula

% Y2 X4 A7 O( P+ F6 e  LEnd Function
# _8 d0 F" q5 X, U! G
4 y6 i. R( q- b0 V9 y* {  cPublic Function ZZL(RowHead, ColHead, Dummy)
: T9 _6 g; K4 R5 z, w! X1 p
Dim Values(20) As Variant
Dim PrevData(20) As Variant
Dim LE(20) As Integer

On Error GoTo err_handler1
7 Z/ q  [* C% o- c' Do the vertical selection from rows
: i! L9 h% s% vIf RowHead.Rows.Count = 1 Then
3 G: z4 d7 Y; V( r% `6 B    rindex = RowHead.Row    ' first argument is any cell on the row of possible values
Else
% |$ i6 Z8 @2 z+ i    ' Store the values to be compared with each column
    For ii = 1 To RowHead.Columns.Count
. ]3 \0 C0 v6 a, ^5 K        rngname = RowHead.Cells(1, ii)
- J& U' j/ U6 \+ f) [        LE(ii) = InStr(rngname, "<=")
        If LE(ii) > 0 Then
  D" h" d7 y: O# X5 U            rngname = Mid(rngname, 1, LE(ii) - 1)
% a6 J6 \& ~. w4 q6 V% t9 W$ P        End If
        Values(ii) = Range(rngname)
        'debug.Print "Variable:" & rngname & " is:" & Values(ii)
1 u6 D$ L6 d+ q5 `+ }        PrevData(ii) = ""   ' initialise
    Next ii
+ V9 k+ o8 t! O4 {
    rindex = 2
    'debug.Print RowHead.Columns.Count
2 d' R: I1 e- G  J    Match = False
) w# o6 W! e' T% D, v, j7 V8 H8 ]    For r = rindex To RowHead.Rows.Count
1 Z- }; Q2 H* C        For c = 1 To RowHead.Columns.Count   ' for each dimension
3 W" e1 n6 V+ c7 A( h8 l            data = RowHead.Cells(r, c)
            If data = "" Then
6 d  V. c' C, G2 z6 l5 j4 N" H/ O, w& [                'debug.Print "Empty cell found: using " & PrevData(c)
  {6 C8 K' v" A% f2 W0 _9 {9 \0 C                ' use the last valid cell in this column
                ' (this is to handle merged cells)
3 d! |+ Y" X6 \                data = PrevData(c)
$ a/ u( O: v( F: N) @            End If
            'debug.Print "data:" & data
8 X8 ?' c+ a- a* _            PrevData(c) = data ' save for use by empty cells
+ l; h" q+ ~8 g5 `2 Q7 U! q            If data = Values(c) Or (data > Values(c) And LE(c) > 0) Or data = "*" Then
# y) |" O' D) y' W; R                If c = RowHead.Columns.Count Then   ' All columns match - It's a go
                    Match = True
                End If
            Else    ' This column doesn't match - go to the next row
- \6 G3 F, Y5 A# H3 w                Match = False
8 z2 j* n! J3 |6 M1 s. l                Exit For
! R  ]9 K0 ^/ u9 R) D            End If
        Next c
4 \- R0 s  K" J5 X7 `, a7 `        If Match = True Then    ' Don't search any more rows
            rindex = r
            Exit For
        End If
    Next r

) k0 U6 J; w' |* Q4 m    If Match = False Then   ' Didn't find a matching set of values
! z. C  N4 }# L; H        ZZL = "No match for rows"
        Exit Function
! f3 l0 i2 Z, c5 Z9 s8 B( c    End If

    rindex = rindex + RowHead.Row - 1   ' make absolute index
End If
7 H7 o0 H. K+ j9 W
3 c6 C- a4 @% ^' V' Do the horizontal selection from columns
: C( S1 U) A9 h# c( }( v) B  AIf ColHead.Columns.Count = 1 Then
    cindex = ColHead.Column
' \6 c9 S1 M% e/ [$ W3 F. p) oElse
# S% j2 P4 m* z& l2 E2 D    ' Store the values to be compared with each row of the header
  l- H9 h' w0 t+ h    For ii = 1 To ColHead.Rows.Count
( L2 P3 Z/ B+ M        rngname = ColHead.Cells(ii, 1)
% V) M7 ~! ?( v# c$ ?' y4 [        LE(ii) = InStr(rngname, "<=")
" Z9 I6 R* b6 b* A7 j        If LE(ii) > 0 Then
            rngname = Mid(rngname, 1, LE(ii) - 1)
        End If
% M* E  z+ c$ I7 R        Values(ii) = Range(rngname)
/ a9 M# h! d* G0 c4 I$ B6 u        'debug.Print "Variable:" & rngname & " is:" & Values(ii)
        PrevData(ii) = ""   ' initialise
    Next ii
: _$ l/ [+ z! g+ w* [4 U
* E  _' @* x3 w, n5 D    cindex = 2
# Q% T6 f- B' g$ x* Y4 t- C$ q# y    'debug.Print ColHead.Columns.Count
    Match = False
5 e# p, y% t- q9 L3 J0 ?3 m    For c = cindex To ColHead.Columns.Count
4 b& L3 D- V, q9 l. w- c6 `0 d) [        For r = 1 To ColHead.Rows.Count   ' for each dimension
$ |4 l4 Z0 ~  e7 h& n7 ^4 ^            data = ColHead.Cells(r, c)
9 w1 H4 Y' t' t            If data = "" Then
; w' S' ]# N/ r  f/ k                'debug.Print "Empty cell found: using " & PrevData(r)
                ' use the last valid cell on this row
; U) j+ ^6 E2 w4 A& F2 F% z4 k                ' (this is to handle merged cells)
) s! C# z  e+ B% f  ]                data = PrevData(r)
, m8 f- Z' S/ X" I1 G5 |) h4 ?- r            End If
            'debug.Print "data:" & data
! }) \' b  A+ O0 T$ G            PrevData(r) = data ' save for use by empty cells
  J. J, o% e  S  R2 O. ~2 n$ t3 z            If data = Values(r) Or (data > Values(r) And LE(r) > 0) Or data = "*" Then
                If r = ColHead.Rows.Count Then   ' All rows match - It's a go
" N  u# T4 E9 i# y: {: B) i( c7 S                    Match = True
                End If
& s, j; w" d( r! I5 ]! Y6 u3 p1 w# `            Else    ' This row doesn't match - go to the next column
                Match = False
( P' y, Y6 Y, @4 J  f. F" F                Exit For
            End If
        Next r
        If Match = True Then    ' Don't search any more columns
* D9 Y- Z4 h9 w8 Z            cindex = c
            Exit For
        End If
    Next c
5 A# [- g6 H* o9 y0 r+ P
2 ^! N: t, S6 V  j5 k. }- G    If Match = False Then   ' Didn't find a matching set of values
        ZZL = "No match for columns"
3 |9 p1 E3 ~4 t1 x  n( }, C$ @& q        Exit Function
    End If

  y: H% U6 z1 X, _; v    cindex = cindex + ColHead.Column - 1
End If
/ ]$ H0 q2 Q5 f+ d. k. Z, g- o
' O, R! D- g( G/ W- F6 z' Return the cell value from Table
. y4 O5 U8 W7 a' ]' m; o'debug.Print "Answer is in (R,C):  " & rindex, cindex
ZZL = ActiveSheet.Cells(rindex, cindex)
9 Y! W6 Y$ K6 d6 j1 Y4 i' ^'debug.Print "Answer is : " & ZZL
Exit Function

err_handler1:
; t$ ~/ T5 ^% l8 n* s$ X% H! |ZZL = "Error on range '" & rngname & "'"

End Function

大龙媛

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

魍者归来

微软官方的bbs里是有专业的VBA教程和API端口说明能检索的（全英文）

机器画家

上excel论坛问问看