標題: excel 中BVA 麻煩大佬幫忙解釋一下 [打印本頁] 作者: 大龍媛 時間: 2022-6-6 18:03 標題: excel 中BVA 麻煩大佬幫忙解釋一下 Option Compare Text * s" u. ? T* ] 9 ]8 e9 }6 l. }4 O/ NPublic Function GSXS(Ref) 8 I; }, V/ Q$ E% a, y4 {; B8 T8 q% Z: q7 V
GSXS = Ref.Formula . T4 ~* O& [; X, {! a& O : }; z; B2 V6 a- G, O4 E4 L. T: Z4 C/ E; NEnd Function% R: z8 \: Q' D/ R
3 w0 [, S6 g+ D1 O% M( n# H2 a$ c
Public Function ZZL(RowHead, ColHead, Dummy) ( K* B u) k6 C- v7 O$ c $ [* f+ h$ e1 Y, Q8 M0 r) rDim Values(20) As Variant & \# D' `9 O7 n$ ~6 N9 |3 p% bDim PrevData(20) As Variant4 s9 ?6 [0 G) }6 q
Dim LE(20) As Integer . c& b' s( G7 ?; {7 b , d+ q, U ?) @" i% W8 SOn Error GoTo err_handler1 " j3 x# W- l7 g6 u6 ]' Do the vertical selection from rows) O) I% S$ g1 ?" i9 S
If RowHead.Rows.Count = 1 Then 5 Z& S7 G) Y* q9 P* w rindex = RowHead.Row ' first argument is any cell on the row of possible values # K& j; Q. y4 K$ Z: m3 ?4 t V5 ZElse " Q, C N! e# |6 a ' Store the values to be compared with each column4 U( p* M# O8 I; k) t+ v# j
For ii = 1 To RowHead.Columns.Count - Q' F9 q( R2 h# P rngname = RowHead.Cells(1, ii)+ h: ~# `) y+ F2 b1 \- T$ \( g
LE(ii) = InStr(rngname, "<="), Y, V; }0 N0 |7 `+ I; |) o `6 s8 R
If LE(ii) > 0 Then" {+ K) i* s" t
rngname = Mid(rngname, 1, LE(ii) - 1)" H- T# c: [ V3 c3 ~
End If! U7 S* G# k! {
Values(ii) = Range(rngname)! x+ Q( ?. X& k: T
'debug.Print "Variable:" & rngname & " is:" & Values(ii); t, c; m; ~) H, o: n. q! \+ j. B
PrevData(ii) = "" ' initialise & C$ U4 F, |* X* \ Next ii ( V, i* U% N. x9 _* i5 n% A+ P2 K+ r7 A
rindex = 2 & ?/ g) ~" C: i* o8 s" y6 i2 v 'debug.Print RowHead.Columns.Count % R. i# q! {$ G: _8 M Match = False / e. x8 B) f1 t6 `: I( z0 O For r = rindex To RowHead.Rows.Count; m8 Q8 x+ E/ R' ]; `
For c = 1 To RowHead.Columns.Count ' for each dimension * ~. v5 r: i) l* j: m data = RowHead.Cells(r, c) & F3 Q% Q& N$ C! A If data = "" Then2 R6 N% }+ V% B) [6 w" {
'debug.Print "Empty cell found: using " & PrevData(c)1 [& @3 s5 E7 E* B6 T2 L
' use the last valid cell in this column 7 k$ X0 {3 ]* V" z ' (this is to handle merged cells) 7 {* F8 l- O+ Z# |0 E F data = PrevData(c)2 d+ q; ~9 Y7 C4 h
End If 3 h: L7 @- G( Z; m; O4 y+ I$ l 'debug.Print "data:" & data $ N) F8 W8 y$ ?+ C) K PrevData(c) = data ' save for use by empty cells- y8 F" x _" ?1 o/ t- k6 s0 r; T
If data = Values(c) Or (data > Values(c) And LE(c) > 0) Or data = "*" Then ; z; ^2 Z# h; T& M: p% U If c = RowHead.Columns.Count Then ' All columns match - It's a go2 K, F% m2 ~& l3 X+ M
Match = True ; S8 V( A0 K+ I |3 u, K End If 9 m( O" Y# ]0 U% M Else ' This column doesn't match - go to the next row% \( [1 c6 a& J \ `
Match = False* C& l" d* A# R& c
Exit For$ i- v! m4 |& t* N8 u3 [: i1 P# `" f
End If/ N7 _9 z: `* ~" `+ l1 P
Next c 6 s6 n9 B6 k' t8 J9 z& @. l If Match = True Then ' Don't search any more rows . B Y. J: @4 N' l& L rindex = r6 u# I! U, o/ G- M# f; v# t
Exit For 4 g% R6 H% S; G" i6 U End If 2 w. H$ |; P0 G- J$ X Next r 7 a! z: K8 E! w+ n* C8 ?; R! y1 _+ _4 i& Y5 }9 w
If Match = False Then ' Didn't find a matching set of values ~& W6 d7 j+ Z9 N, G* c" R% }" p ZZL = "No match for rows"( ?) d7 G( H0 o7 M8 t2 R
Exit Function. g6 g* G7 T" k1 s. |5 |* a) f
End If 6 o( w6 q e! z9 f* I+ N6 y# p/ W, V$ t8 v
rindex = rindex + RowHead.Row - 1 ' make absolute index N( ]) f y5 p8 P
End If 1 r4 b; }) }9 C! ? 4 c4 b- A0 b2 M9 M; E- l' Do the horizontal selection from columns , N. k1 p" g9 V3 |! W5 T2 f U4 j! yIf ColHead.Columns.Count = 1 Then % w: d; g+ s' U6 a" | cindex = ColHead.Column 9 j8 ~; ?/ `2 ~2 q: m% f$ Z; IElse , l7 |0 T$ h: y3 Q% G ' Store the values to be compared with each row of the header+ R6 T. ?; o+ \: j$ H! e1 Y
For ii = 1 To ColHead.Rows.Count- n6 S3 o m/ \, N
rngname = ColHead.Cells(ii, 1) % n7 ?, w; u9 H7 B/ I9 x LE(ii) = InStr(rngname, "<="); I3 |2 G" j3 D1 u* {" U
If LE(ii) > 0 Then 7 T8 o. H/ `( h7 \& [ rngname = Mid(rngname, 1, LE(ii) - 1) * @" D/ G" J, ]- F& S2 b, i* l, j End If+ i) |# w& D' v [, B: M: X! N
Values(ii) = Range(rngname), N' J0 w4 N9 E
'debug.Print "Variable:" & rngname & " is:" & Values(ii)' j- E2 y+ R8 K% ]0 F
PrevData(ii) = "" ' initialise1 r6 L2 }$ A- S4 k2 U
Next ii! r0 @$ h1 u5 M7 T: u& r
n& E; Y2 H# n2 [7 g" V cindex = 2 ! W# F5 g C0 j& O( C3 I1 V4 o 'debug.Print ColHead.Columns.Count3 \ J7 [. u2 H0 m
Match = False ' x) o+ }9 i6 J- F. K# F: B! e For c = cindex To ColHead.Columns.Count1 q% A, `0 k, z% r- t Z
For r = 1 To ColHead.Rows.Count ' for each dimension( D) @* D, ]7 b' @* M/ G
data = ColHead.Cells(r, c)6 q1 k3 S5 P0 j b6 a1 g
If data = "" Then$ `( k0 J0 r/ i8 r( y2 Z3 E
'debug.Print "Empty cell found: using " & PrevData(r) 8 d$ x* d( T! t& ]4 | ' use the last valid cell on this row & P, f4 }- f. L s. _% Z5 @+ l ' (this is to handle merged cells)5 J" _! b. q# D3 I
data = PrevData(r) 7 s/ N$ h% j0 ~ End If& L3 l% G1 L+ ] m2 X; J9 p7 K: l
'debug.Print "data:" & data + n' \; ^/ ]' `0 S8 B' z0 d PrevData(r) = data ' save for use by empty cells: {5 j2 L: n; d0 N
If data = Values(r) Or (data > Values(r) And LE(r) > 0) Or data = "*" Then 1 b; z' u- h; Q# N3 C# c If r = ColHead.Rows.Count Then ' All rows match - It's a go7 o% q6 E0 o8 Y. s' R: Z* P% n9 T3 u
Match = True 4 E: B+ A# F0 T2 M% b. b: B6 A End If 9 P, Q3 u- J9 Q" Z( e: G4 ~ Else ' This row doesn't match - go to the next column3 ~7 ]3 R* _" Q+ t" C2 V
Match = False" \% U1 u- N' \/ l; v8 x: o
Exit For$ g. }# H9 u9 r2 m
End If. O+ e) A' A& W# E; y
Next r. t- Q' u% c9 H$ o' f ~
If Match = True Then ' Don't search any more columns : O. r, ?7 o# t1 E0 ^0 G2 q* x m cindex = c 5 O; z9 x+ S3 @( r) [/ s' ^0 }8 Y Exit For . ~) f) X0 G$ ~; { End If " N, q9 g' d. N6 `5 w Next c6 g# Y# S$ y7 l z1 K H0 i
# |5 \- `$ L4 N8 B+ c; b
If Match = False Then ' Didn't find a matching set of values( z4 m) @: I; n2 l
ZZL = "No match for columns" 3 u( E t# i2 U& e2 b Exit Function$ F" E- V! |1 P2 _8 y% e% @
End If ; K) C; T6 z2 _ 0 y. h& e5 T9 Q cindex = cindex + ColHead.Column - 1 + n( k) a. S! v2 v1 P- ?! q8 i0 ^End If , `% R2 Z9 J( M( g8 R + ]2 n t2 g' t% \2 b, \, A' Return the cell value from Table $ m7 O6 j9 L; J* m- Z- ?'debug.Print "Answer is in (R,C): " & rindex, cindex 4 b# Q& {3 }; n! t" B4 G" I1 EZZL = ActiveSheet.Cells(rindex, cindex)8 j8 p- ]# {. l1 |6 m* Q0 C* ~* ~
'debug.Print "Answer is : " & ZZL* G- {+ J* Z, E- O+ F8 F
Exit Function + M0 g5 F- Y! ^ + K7 u0 W5 p) I! zerr_handler1:) m b8 g: f( ]# }: W5 u) A
ZZL = "Error on range '" & rngname & "'" , p w/ O4 }5 }$ ?. e- x! C - l8 \" i) l2 W" h7 A FEnd Function8 N# H0 c5 t- Q" @6 _4 [' V& c) o