Access΢n
Accessƽ_QQȺ̖84825014    Access_lƽ_dַ̳    gӭ΢Ž~̖AccessoftChu    οվ|ʾ|Y    
λã > g > Access-??/VBA

2019年欧洲杯预 :VBAMȫ̳10ACCESSMVBM

2020ŷޱԤ www.okrxb.com rg:2012-07-21 08:39:41
:L(Dd)   ID16058  У
ժҪ:mɫVBAMT̳10
:

1. ǰԣҪVBAM̫䌍һMֶ

2. MľS

Sub ()
Dim x As Long, y As Long
Dim arr(1 To 10, 1 To 3) '
һ103еĔMg
For x = 1 To 4
For y = 1 To 3
arr(x, y) = Cells(x, y) '
ͨ^ѭhцԪ^a1:c4ĔbMM
Next y
Next x
MsgBox arr(4, 3) '
ṩДД@ʾM
arr(1, 2) = "
Ҹһԇԇ" 'Sr޸ĔMָλõĔ
MsgBox arr(1, 2)
End Sub

YSкбʾĔMARR(3,2)ʾMе3ŵ2еԪһSMֻһԪ؛QARR4ʾMе4Ԫ

3. цԪ񔵓ȴ棺

һ•

Dim arr as Variant '•һ׃•

Dim arr(1 to 10, 1 to 2 ) , @N•Ҳe`̶СVBAMDzһbԪ񔵓

dim arr() @N•ʽ•һӑBMҲbԪ^һVBAM

b

arr =range("a9:c100") 'bܺ׃ = Ԫ^

x

b딵MĆԪֵ԰ MQ(ДД) ֱxȡԓλõֵĴa

Msgbox arr(3,2) 'Ϳȡ^ȥĶɵĔM3е2еă

ʾ

Sub s3()

Dim arr() '•һӑBMӑB̶ָ??/span>
Dim arr1 '•һVariant͵׃
arr = Range("a1:c7") '
цԪ^A1C7ֵb딵Marr

arr1 = Range("a1:c7") 'цԪ^A1C7ֵb딵Marr1
MsgBox arr(1, 1) '
arr1е1еĔֵ
MsgBox arr1(2, 3) '
arr12е3еĔֵ
End Sub

4. цԪ񔵓ȴ棺

Sub test()
Dim arr '
•һ׃ÁʢņԪ񔵓
Dim x As Integer
arr = Range("a2:d5") '
цԪ񔵓뵽arr44
For x = 1 To 4 '
ͨ^ѭharrMѭh
arr(x, 4) = arr(x, 3) * arr(x, 2) '
4(~)=3*2
Next x
Range("a2:d5") = arr '
єMŻصԪ
End Sub

Sub test1()
Dim arr(1 To 5) '
•һSM
For x = 1 To 5
arr(x) = x * 2 '
ͨ^ѭhoÿλxֵ
Next x
Range("A1:E1") = arr '
єM뵽excelеa1:e1Ԫ
Range("A1:A5") = Application.Transpose(arr) '
Ƿһ,ҪMMDúٴ
End Sub

5. ӑBM•

Sub darr()
Dim arr() '
•һӑBarrM(֪ʢٔ)
Dim k
k = Application.WorksheetFunction.CountIf(Range("a2:a6"), ">10") '
Ӌ10Ă
ReDim arr(1 To k) '
ٴ•arrĴС,ʢkֵ
For x = 2 To 6
If Cells(x, 1) > 10 Then
m = m + 1
arr(m) = Cells(x, 1) '
ͨ^ѭhѴ10Ĕb딵M
End If
Next x
MsgBox arr(2)
End Sub

6. ӑBM•
arr(-19 to 8)
@Mľ̖Ǐ-19.ôС̖-19,̖8, Z䷵ؾ:

Sub t1()

Dim arr(-19 To 8)

MsgBox UBound(arr) '̖,Y8

MsgBox LBound(arr) 'С̖,Y-19

End Sub

нMɵĶSM?SMе˜˺е˜Ҋ

Sub t2()
Dim arr(-19 To 8, 2 To 5)

MsgBox UBound(arr) 'ص1S(е)̖,Y8

MsgBox LBound(arr) 'ص1S(е)С̖,Y-19
MsgBox UBound(arr, 2) '
ص2S(е)̖,Y5
MsgBox LBound(arr, 2) '
ص2S(е)С̖,Y2
End Sub

Sub t3()
Dim arr
arr = Sheets(1).UsedRange 'Usedrange
ДДδ֪
MsgBox UBound(arr, 1) '
Ӌ@^ж
MsgBox UBound(arr, 2) '
Ӌ@^ж
End Sub

7. ʹArrayM

ʹArrayM

1SMArray("A",1,"C")

2SM Array(Array("a", 10), Array("b", 20), Array("c", 30))

Ҳ{excelȴ攵M

1S [{"A",1,"C"}]

2S [{"a",10;"b",20;"c",30}]

ȴ泣Mʲô

1xֵ

磺ҪoMarrքexֵ10 ,20,30,40 һҪքexֵ

arr(1)=10

arr(2)=20

arr(3)=30

arr(4)=40

ʹóֻһԒ

arr=array(10,20,30,40)

2{ùrʹã

Sub mylook()
Dim arr
arr =
[{"a",10;"b",20;"c",30}]
MsgBox Application.VLookup("b", arr, 2, 0) '{vlookup
End Sub

8. Mĺϲַ֣Join & Split

ַĺϲַҎɵIJǽ磺

A-REW-E-RWC-2-RWC ָ-ֳ6ַһM

һMarray(23,45,7,1,76)÷ָ-Bӳһַ

ɷNrVBAṩһ

splitַ,"ָ" ַ

join(M,"ָ") ÷ָBӔMÿԪһַ

Sub t1()
Dim arr, myst As String
myst = "A-REW-E-RWC-2-RWC"
arr = Split(myst, "-") '
-ָһMb딵M
'MsgBox arr(0) '
@ʾMĵһ(ָĔMј˞0,1),@ʾYA
MsgBox Join(arr, ",") '
","єMÿֵBӳһַ,Y"A,REW,E,RWC,2,RWC"
End Sub

ֵעǣsplitjoinֻ܌һSMMвdžԪSMôkֻһl;kDQһSM

Sub t2()
Dim ARR
ARR = Application.Transpose(Range("a1:a3")) ‘
DõķцԪһДDQһSM
MsgBox Join(ARR, "-")
End Sub

9. FilterFMYx:

MĺYxǸһėl,ĔMкYxϗlֵ,MһµĔMFMYxVBAǣ

Filter

÷Filter(M, Yxַ, Ƿ)

Sub DD()
arr = Array("ABC", "A", "D", "CA", "ER")
arr1 = VBA.Filter(arr, "A", True) '
YxкAĔֵMһ”M
arr2 = VBA.Filter(arr, "A", False) '
YxвAĔֵMһ”M
MsgBox Join(arr2, ",") '
鿴YxĽY
End Sub

zǺֻMģYxܾ_ƥ

10. VBAMT̳֮10(Y):ɽ֮ʯ
ɽ֮ʯԹVBAгõVBA߀{ñExcelMMзֽԃͷ{ùʡȥѭhД韩M\Ч

һMֵ

1MaxMin

MaxMinֵСֵĺͬVBAҲ󔵽MֵСֵ磺

Sub t()
arr = Array(1, 35, 4, 13)
MsgBox Application.Max(arr) '
ֵ
MsgBox Application.Min(arr) '
Сֵ
End Sub

2largesmall

largesmall ǷһMĵN͵NСVBAMͬm磺

Sub t1()
arr = Array(1, 35, 4, 13)
MsgBox Application.Large(arr, 2) '
2ֵ
MsgBox Application.Small(arr, 2) '
2Сֵ
End Sub

MĽyӋc

1Sum

SumڹͬҲԌVBAM磺


Sub t2()
arr = Array(1, 35, 4, 13)
MsgBox Application.Sum(arr) '
MM
End Sub

2CountCounta

CountCountaԽyӋMДֵĂ͔+ıĂ

Sub t3()
arr = Array(1, 35, "a", 4, 13, "b")
MsgBox Application.Count(arr) '
ֵؔĂ4
MsgBox Application.CountA(arr) ‘
ؔMıֵ͔Ŀ
End Sub

MIJԃͲ

1MachԃM

MatchԲԃһֵָһMеλҲVBAMIJԃ磺

Sub t4()
arr = Array(1, 35, 4, 13)
MsgBox Application.Match(4, arr, 0) '
ԃֵ4ڔMArrеλ
End Sub

2Index֔M

VBAһy}ǰв֔MѭhҲֻܽAPIҺ҂Խùindex_в֔MИɵĔMֳһИµĔMǣApplication.Index(M, , Д) 

Sub t2()
arr2 = Range("A1:B4") ‘
цԪ^A1B4ֵb딵Marr2
arr3 = Application.Index(arr2, , 2) '
єM2вֳb”Marr3”MSM
MsgBox arr3(2, 1) '
ȡ”M2еֵ
End Sub

MSDQ

TransposeDÔMڹп԰DQVBAͬҲDQЧ

1һSDS

Sub t9()
arr = Array(1, 35, "a", 4, 13, "b")
arr1 = Application.Transpose(arr)
MsgBox arr1(2, 1) ‘
DQĔM1жеĶSM
End Sub

2SMDһS

Sub t2()
arr2 = Range("A1:B4")
arr3 = Application.transpose(Application.Index(arr2, , 2)) '
ȡarr22ДDó1SM
MsgBox arr3(2,)
End Sub

עDÕrֻ1NеĔMֱDóһSM

˼}Ҫa1:c1еă“-”BaОʲô˃ɴtranspose

Sub t10()
arr = Range("A1:C1")
MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-")
End Sub

ԒVBAMĹֻгһ䌍vlookupLookupȵȺҲ̎VBAMп˾ȥLԇ°



AccessܛWQQȺ (Ⱥ̖:596608182) accessԴaW

鿴uՓ(2)

2020/2/29 20:44:02^
MsgBox Application.Min(arr) ʾɆTδҵ

2014/7/19 11:57:07־
䰡]uՓҾ_ӡ

luՓuՓ߷ĄuՓһ°

Ñ
ܡa
ȡݣ
 

Ҋ

g

PYԴ

| ՗l | ھͶ | | WվyӋ |