[PDF]Excel VBA

0 * I






















































Excel VBA

^UJIJLaJ

JjSrt jt^V'

2020


di^ l^U£JI

^LuIaII a



- - < S u/iaII uiuJ

J1a21j (j^a^j^l

CC BY-NC-SA

A

^CJUdj ^djj C^jjJOLdiSl 6(Jj,A3t!i5l
Ailjdd

L_^jaI] (JaxJI L_budj

^jjdiij (JIac-vi j

A


ui:>uui

^ uill


ufl^ULlI 0

tlujiiVl (j-a















1

7.Excel VBA 1.

7 .? VBA c?A L* ‘u^l

3. (J j t n^ l VBA

8 .VBA Module

8.Procedures j^V'

8 .Sub Procedure

9 .Function

10 .Object oriented Programming

11 .Object Model cijLuI£ 1I jj>
12 .Collections

13 .Objects Properties cjLuISII (_>ajL^

13.Variables djljjiiall

13. Methods

15.(VBE) ^tjVI jja^ 2.

15.t Visual Basic Editor (VBE) j^iJ jj^ j* I--

15.VBE -ilj^Vl jj^ jl^j ^

17.VBE jal^j ^)*-aUc.

19.Code Window

19.Properties Window o-aiC^l slaU

19.Immediate Window j^ljVl slaU

21.VBA JC JjVl


VII



























26.(J;ljal£Vl dltaLo

29 .*tA ^(.^aILuiI ,3

30 . ^)ix-aVl ‘_Ix^all j^)£Lq Cblj)Jo*s

32 .c^ ^

33 .j' Button

34 . ^ ‘'**^ Aic. Ajjjudill lj!>tiJl ^lAkjjoil

34.(3^^

37.Variables 4^ljj*iAll 4,

37 .^

38 . VBA C:lj^l ^Ijji

39 .VBA J

40 .Declaring variables cjljjiiall jc. j:^y\

43 .cLjIjjxIa]! 3^“^

44 .-iasa j^V' ti*-^ t/ J:!^' Procedure only variables

45 .-i^aa ji' ti'-iaj cjljjiiJI Module only variables

46 . Public variables cjljjiiall

46 .Static Variables cijljjiiaJl

47 .Reset

48 .Constants j^l

49 .Constants Scope tjUaj

49 .Working with Strings ijjljjiiJl ^ J^usll

50 .Date variables ^ (J^usll

51 .Assignment Statements ui!*^l

51 .Arrays

52 .Declaring Arrays ja*^l oc

52 . Multidimensional Arrays

53 .Dynamic Arrays

viii

































55 .(jUaUI ^ J-abuJ) ,5

56 .CgIIs

57 .Offset jVI

57.Velue

57 .

58 .Text a^U.

60.Count -^1 a^U.

60 .HasFormula a^U.

61 .Font a^U.

61.Interior a^UJl

64.(jUajJI C:Lul£ li s'ijih'i jS-aJ MetflOdS ji'

64 .Select aijiij

65 .Clear Method ai^j

66 . Delete method ‘-ai^l ai^j

67 .cW .6

67.IF-Thenaia=.

67 .IF- Thent>Jia.

68 . IF-Then-Elseai^.

68.IF- Then - [Else]- Endif au^

70.If-Then -Elseif- Endif (al-i^l

72 . If-Elseif-Else j*l^l

73 .Comparison operators (^l cjUjliall (iaijt

74 .

74.AND J-UJI

74 .OR

75 .NOT lUUJI

77.Select Case statement aLia.


IX

































83.Looping Statements j'j^t cW .7

83 .For....Next

84 .for ^ Step

88.Exit For c> for c>

90.For

92.For...Each...Next

97.Do...While...Loop J j^l

99.Do Until...Loop

101.VBA JljJj ^JJ JtjJ .8

101. U>

101 . (Built-In VBA Functions) VBA

102 .Date, Time, and Now J'j-iil

103 . Month and MonthName

104 .^juAaJil TypeName ^lAkiLoil

105 .^ V (^1 VBA J'j-^

105.iMsgBox^'j 1-

105 . ilnputBox^'j 2-

106 .:Shell ^'^1 3-

108. .il0.^1 hi.q\1 ^ 'IL*

108 .Worksheet Functions ^jj J'j^

109 .(JaxII 'Ui.Vi

109 .ti'-kj (_53 j (_sjjV'J iaj' jLajj

110 .VLOOKUP j.laiLL.-l Jic. Jlia

113.VBA ^ J-Uill .9

113.Runtime time errors eLLi.'VI

117 .On Error GoTo

118 .Exit Sub


X































ellUjia-all (J


118 .On Error Resume Next
119 .Resume Label

120 .Resume

121 .Debugging Techniques (> 5Jljl

121. eLLAV' t-sLAiS')/ Message Box J

125 .«LLi.V' 2 :^ Debug.Print

126 .VBA Debugger

126 .Breakpoints ‘-iajjll

127 .i_sajjJI ialsj jIjjI

128 .Immediate Window

129 .Watch Window siaU

132.Locals Window

132 ..-I

135.Events10.

137.Event-Handler ja Li

139.VBA >.lji ijiiiJ All ^lAaJLoll <11^1

139. Workbook Events >■ Alia.! ^ 3JAi

139 .Open Workbook AiAl

140 .Before Close AjlAI

141 .Activate AjtaJI ^Jc. JIA

141 .Deactivate AiAl

142 .New Sheet AiAl JIA

142 . Before Save AjtaJI JIA

143 .Worksheets Events J^l (i'jJ Alja.i SIlVi

143.Activate AiAl

143 .Deactivate AiAl

144 .Select Change AiAl


XI

































146.

146.

146.

149

149.

150.

151.
154.
154.
156.
159
159.
162.

164.

165.

166.
166.
167.
167.
171.

174.

175.
179.
181.
182.
183.
185.


.Worksheet Calculate

.Worksheet Change


jlj^l CjIjUJ. 4 ^ Jplillil ,11

.MsgBox ^'^1




L>^ AjLaJLoil

.InputBox ^>1

.InputBox (J^

Application.InputBox method

.UserForms .12


.... Command Button _y>ljVl jj

.Show UserForm jjj^'

UserForms Codes jiLajiU a jIjSVi SjIjS

.Unloading a Form (i^l

.Fliding a form

.^IjVl jljjU 2L^Udl JljSVl aJ:^


I_gb0| ”Aj*SJx>» j!ill A^lAjoull"

.Option Buttons JjJ

Option Button jj^ a^UJI

.Check Box


.List Box

. List Box

.Populating List

^ List Box ^


XII































ellUjia-all (J


188

191

191

193

194

195

197

198


Multi-Select ListBox ^^1 j-i*!*!! jlAi-VI

.Combo Box ^-WLall S^Lall


.Text Box o^'

^lAaJLoil (^5-ic. (JI!Lq
djUl^l jx^ ^.Uj .cJHq


djULiJ j j ^Lja]

_ ^HaII ^Lojoill ^ ^HaII AiLjal


xiii




















cjIjjVi (> Visual Basic for Applications (VBA) ^ ^

AiLjalj ^ x>\l AHajV
A \ ^Ludjl jl (JjjuaSI (JIa

^\ ^Ludjl cJ^) ^ _flJjJa^l cJ-AXJ

L_fljjuj l^U ^-^LLi ^ j^i ^ diajjujjj£jLi duic.) ^j ,1 a j^j dUl^l JLk^l jl j^ljill

dli d^>dij| dlc-Ui^ A^k^ VJ j!n V 1^1 j VBA ^)-
.VBA ^ c> C5^'


,l^ii]j)^ ui (3^ l-^lijj lJj^^ c^j V) VBA Cy^ C5^~J

VBA (J-^ldJl ^1 j^Vl j; ^1-^ dllL>^ (ill

^ ciljic^Lau JUi Help d.^C'tjud^ ^L^lLi ^ (. ** 1 djldill I

QA Ia^)^j ^lia^Vt L-flUijSlj (jL
,dl j^l

^l^^duol j^jC> LJ^‘^ VBA -^1 0^1^ ij'^'* ^ C^l (Jj

^1 ^1 ^ x>\l ^I^Lj ^ j^)SL
d^A ,A.^L^I dc. Ajlc-djuoV J^)SL
lJi^)xj d.^^1 1^1 c^l IjJajI (,Jj AC'^^juoJ c^LaC>l L)^ aJIjl3 aI^^I

AjLoxJI c^Uj ^I.^Ij ^ (^^:yj.AA (,Jxjd ^1 Aj^ VBA -^1 Ajjjl^ AjLoxj

,A un ^ L^'^l (J^uoiaII L-iS^ ^


1






A ^ i CIjLaIS ^A^JLuoJ 1-^ (J’ VBA A^djiaxJl (3^^

\iXLS, ^\jil\ :jj£11 iA:^ ik . jj£ll S^l^j
Worksheets("sheetl").Activate
Range("Al").Value = "Hello World!!"

Range("Al").lnterior.Color = vbGreen


Sheetl )njuinj (J^ljuaSVI CIiL^IjujIj Ai^)x>i CJJ^ CJ^

4^)jjak.Vl (j^U cilts A1 "Hello World!!" ^^^

(.** 3? \ ^Ij a q utix> 6^)fk A1 ^

(^jA ^jUll ^1 AjjJa^ VBA (JjIjujSVI diL^Luol ^

djl^j (Jj,^uaUJ (3^1 ju0 A1 (j£j ^ lil _IA j) VBA tljJUO 6^)fk (3^1-JUO
^ (J-oljiIllI Sj)nk ikl^ L^lj£j| (jU VBA AjIjS jI j^)SL
.5Jl111

cJ^ojJ Jiajudjlj LS^ 1^ AjIj£ IjAxUC>I ^

<11^^ A^joulU dlxjjaj lA^I ^jjajJ dlS^1 ^j A-AaC- 6^li dlli CJJ^ CJ’

1 tdmjuli (JJ^ (JjJaS) (JSj^ ^LujJ IajjSxj ^1

lit AA IAi^Ij (JHaII 6^^£aj (J^Lkl (J^‘^ ^UalLaiAll aJxoVI

Cj^ cJ3^ AjI^ (^J£judj ^ ^9 i\l diA^

(>**^1juiIjujVIj tA
(JIa) (^Jjii ^^)k.l 0^1^ A-i^^ ^aixj ci^JuoJ L_fljJuJ L^IjSJI 6^^^Aa1I

JIa a AjlS ^1 ^aixJ (^.^LalJ lJj^^ IjA 6^^^Aa 1I djL^LujVI (j) ^ .VBA

_l1u^)Sjuj liL^ jl ^jJjU

ajj^x>\I ^)j^IjxJI ^Ij VBA diL^Luol ^1 ^ ! VBA

A^jUll A^I£ A.^^)JI qx> C1 jI^I^)^VIj (Jlj^lj A-AxAill (JIa 1-^


2






Object Model Object Oriented Programming


^jjjLujVI A\

Visual Basic Editor j^Vl jj^ :VBE jj^ J^t -

^^)juj ^ ^aJJ VBA

(JjVI AjIjS ^

_
Macro (J,^uaiA ^l.liJLuj| Aij^)ia ^

5jtL 5-jjj£^l ^\jA\^ jjSLill dil^j ^UiuV j Rocordor

A ^jQj^ ^ (Jj.^uaLi ^jQj^ ^ (3^“^ VBA


_A-iic.

A-^j l^ljjij Variables C-

(Jj^^asll LoS _Clll^)jiLlAll ir'\Y\\ Ajqj^j L^OaJj 1 ^ "iC.

(J^lxillJ l^-k. J Ailik-
Range Objects (jUaill cijUjI£ 1 (jjiaill :(jl^t cjUjU ^ J^bu]!

_ciiliLlaill 1 ^ Methods 1 ^ k^\\

^Control statennents ^=^^1 c> cW -




(> ^ I^Iji^I a^j Select.Case j IF ....Then VBA ^

^ l^ljLkioil A-^j Logical Operators AjSlaiJI ^^ ^ US .5JUVi




Do.While j For....Next j'j^' lU^ c> ^^ Ua . ia^

_aJU| j dlVU 6.1xjj (Jjlx^aiL U J


^ aj^jJI jUujVI (j->x> ^ 6 jL^ ^ Function ^1-^1 iJtj^^t

A-j}-a n

Aj^I^ l^l VBA Ul ^ lU (^J

^ _AjL^ax^aSUl (JaxJI A-9^J VBA A-^^^AaII (Jlj^l

(JS C5^ aJUI ^liaC.| ^A Aj^l^l


3








VBA AjuLu^I (3^ g.1 m\*\\\

(jl ^lia^VI (j^)ialll ^ (^J .L^^)^' L^’

LoS _Aij^)ia Ajuti (J^IjlIII ^ lia^l (jl^ 4jqj^j VBA

djU^I (j-o jj^l D6bugging " 4 -^tcix^aill ^ (j^)]alll

lLjji^ lIj jji^ Ak. t^lilj VBA l 3^>^ ^ j-^U-St

L_fljjuj (Jj^^asll (^J <>**vW i^jC>
dilklj^VI A^ ^ I^Aa dJjliluoVI J Jz^JoiSVI ^ Ic-J^Vl cIjIa^VI L-fljxSj

.^lilj lI^jAj

:lnputBox j MsgBox J^ (> ^aSA^I Jc^liUt ^jUJt J.*^!

MsgBox l 1^
AjUI^I ^AkLujj In put Box ^^AkLoiAll
c^'j Application.InputBox cij^^l ^ :iSa ciil3 ^Ljal .jl cJ!^

^djliUoill ^1 AjV^tx^l ^AkLuA

AjUI^I Jbk^V 6A^1 (JjUjo jll ^ Us6r Forms ^ ;^jL4li( ^j-uip

(Jlk^l L_UJJJ <•—u^ (J-
Qjii 4-iIac. j) AjIaI^I i^jjii a1^ a^IAaII a \jui^I ^Ia1A.I j) AjIaI^I

caUI^I (Aii£^^ (3^ AikAAll CAljLi^ (JtkAVI

Ia^ Laa ^^LAa^^)jLA]| CAIjI^I AjL^nJa (^J£jA]Ij

JIa l^jlc. ^)j^Uc. 5iljJal 4jqj^J ^aLaII ^LuAI A\^f\ ^^>Ai ^ Iaa ^ ,caIjI^I

AjIa^!^ ^-jL^Ajuj!^ ^a^^-
^^JajoAaII

^VBA A-^LuoVi caI^I^-aII l^LuASIj ^^^aAAjolaI a .^^ l^1A£JI Iaa

^IaJJ ^1 4-1^ L^1A£JI IaA ^ IaAS ^^^jJaAll (JjljuaSVI ^ (^ (JliAVU Lia

((^Jjjuj^l CAIjjujIjujI (_}ajAj (AHa ((_]jja^l ^lAkjjuol (,5“^ Jsjuj^^JA ^^^JjoiaJ

(JjIa^I (JIa 4 q\*i^ a\I (JjjoiSVl caIjaI ^IaAAjujI ^(JaxJI cAlaliaillj Ij^IaJI ^a (JaIxAII


4






2019 <^^-1^1 Cy^ U^diV^^Lx^l ^a ^J^IjLIII tdjlJaJa^^l^

ciii£-
Jajl^l (J^Lk (JjjuoSI dil^L^ " Uj^j^ AjuIIa


?(^Um tj^ (..i^

lJ_5^ ^A 4L_L^^pll L^Ij£J1 I^A

1jL7* ill 1 ^ K^kit \ ^1^


IjL^aikl ^ dlljjii^l {J 'J^ U^ di^ l^Ij£J) I^A

c^IUa (JS ^ AjI <--i^ l^Ij£JI (J-
A^j^)ju1a dlc-jjJa^^ ^jjJaj^i C5^ 1 ^ CJ^^ <>** dLo^^lx-o

cJ'J"6^1^ .^xJ V) aLoIS ( 3^^ ij'^* ^ J' cJ^*-^^

■iaj'JJ


ljj^jA\ J:j^t ^JJ^

^iIVmI) J;i^rt£V) SjjJ LjIj
" J^l JJill - 2019 iaj|j

"^i lVul l ^jlutAil — 2013 Jrf**"**^!” J


^l^)Sil Ajjudillj ti lJ3^ CJ’^ Ajj£^^ l^Ij£JI




^Lii) JLdaj
2019 ‘sj^


5

















6




VBA


JjSl) J-aill

Excel VBA .1


\/B/\ 4 ^ Ajtl cHjIjjujIjujI (_] l.^

1^ a.^^IjujVI

_(JjjL^aall (JliijVI ci^ lil (Jjj^^aall uij 1 ^j\c. AaJxJj

? VBA ^ 0^1

^ jj^^ c> ^^ c^j Visual Basic for Application -S ^ VBA ^

AiLjaVtj djWnJaJ ^ ^ ^^JaiaiJ

^\ ^ x>\l (^IjA 4(J;ljaiS^ ^Jjudillj t^llj ^1 ^ A\ cJ^

: Jid VBA ^ cjl

J djS^1 jL^aikV 6JJ^1 a 1I djlA<^l AldS)

_ (JjIjujSV l-J

_djljl^l (Jj^joul ^iL
. J;ija£!>U Add-ins c Vill^j c-Uiul

d^Ul^l (JxojVI A^^^juoll ^ajuol AiLjal

_
jjj£j| Ia J






7




(JjVl


VBA ^ j^\j»l\ ^\

VBA Module

IjjJj bl^^A (jl^i ‘VBA Module ^ VBA

J!li. (jA jjjA^j s^LIa (jS-aj .Macro Recorder jj^IaII CjI^j J^-uia J!li. < 3 ^ jl

"VB Editor ^'jSVl jj^"

"VBE jlyS'^l i^IJj^LimI" i^jLiJI ^J-iiaAll^ VBE j/yS'^ljjj>exJ (^^jJslt! fO>^if-ui

Jjj jVj Sub Procedures ^js VBA Module

.Functions

Procedures

l(j^

Sub-Procedures ^jai' -1

Function J'j-^' -2

Jl jii Jl jaIjVi (> c> »jW^ J : Sub Procedure ^ J'

lc._Ja ^1 ^ Sub '^^tlia.all 4-al£]lj 4^I^)^V1 (jl ‘- . .e.i.la-a 'CaJ Jl

^Jj Jlj^l J& Jlla Jj LJ .End Sub

:"Hello VBA" Jll aJLaj

Sub Hello_VBA()

MsgBox ("Hello VBA")

End Sub

^j.’^' jj^l' Laj End Sub -i Sub citl.^
."Hello VBA" j'j^ (_p3^)aj ^ Jj .i Jll ' ''''^' *«. J (j.aLaJl

((JjIoiaII (jjj Jlj^l) Substandard WtJ ^^JaII J£jilb ^liill jJij V Jill Jlj^V'


8




VBA


^jU L_fl!>Lkj j ^Ij^l bP !Function

^jjaiL lJ^I^ (3^ jl ^1^1 ^Ic-JILujI (^l£^b _6A

Function ^aKIU 5J1a1I Ia^ .Built-in functions J^VI Jlj^ ^lAkio^l ^jla

5J1a 1I Akb y) J .End Function j ^IaU ^j£H ^ ^IaII lx.

Jl(Function parameters/arguments cA!>LkA^

^ ^AkjjoiAll Akb
:W 3t-A^ *\

Function Add2Nunnbers(numl, nunn2)


Add2Numbers = numl + num2


End Function


I ^ A\A


b^ « X>^ c>j nunn2 j numl biA Akb ^

I 5JIa1I Lk


^ ^



.aJIaII ^jujI ^)bCb ^ ^ b)l


NINA tl^M -


2j[pl jJkjl 3 I 4 iJjb ^ o[;bcUJlj 4^l3Jl Oj-SxJl jJkjl 40[;bcUJl Juj-xJL)

biS (jljji (jkb (b-^)bl AjIa^^I (_^A^I (Jx>x>>
.(_^^)k.l (Jlj^ jl Clilbl^)^Vl (jkb (b-


9



























(JjVl


TestAdd ^Mj=^V' t> Add2Numbers ^1-^' -^j^'

Sub TestAddO
argl = 10
arg2 = 20

result = Add2Numbers(argl, arg2)

MsgBox result
End Sub


Object oriented Programming

Object Oriented cF- “j'-F" Jl

^l£ cjUU ^LaiVl .Programming language

<1 ‘Objects (j
.Methods <.5-“^ cijLal, '^-su . i VI Properties ^ya

CjLLiai-all ‘Worksheets (jljji ‘Cells Jla ^ (JjlJuaSVI

Uj^j ‘Shapes Jl^Vl ‘Charts

a^ljuill A^LI] (4 _ul 3|^I SLl^l |_]^'»"''' La^ ' ^'^\l [_)■^'»‘i"'

C(_pajaJl tJjUl 4(jjill Properties

c {JiA IVl6thod (3^ A^jAyA 1-^j

j) AisjiA djLijuJI cJIa djLiJuall a 1 \^ Ev6nts ^x>

j) Trigg6r

.Event Handler c3^ ^


10




VBA


(J^)la/(jl dlljjl£Jl ^j>i LSjP^^^ (JIaIIj (JjjuoSVI

Features/Properties ^ \ l

Open ^ Vu^ x >\ l ^ Ji Size Nanne ^ Vu^ x >\ l ^1 JL^

JlLft .Save ^ Jai^^

qUi.OX>\l


Object Model

^aJJ ^\ (JS 4(J;ljalSVI VBA L-lWl {JjiJudSV dlljjlSJl

djUjlSlU cIjUjISJI jAj ,c!jUjI£JI (j!^ 0^ VBA

.lj!>LkJlJ djliUaill 4(J-
diljjlSJl 0^ a^ja^a (Jjju^Vl LiJ .Object cS^ o^

djlijisll (J-
_^)jj£JI i'*)\

iSj^ cijUjI^II (jiaxj ji Hierarchical arrangement ^^ cijUjI^II

O^tS 0^
djliL^I jLi cijUjIS ^ L?j^ Application .Application

JIa c:jUjI£ ^ Workbook ^ Vu^ x >\ l o^l£ ji^ .Add-Ins ^t^V' j Workbooks
(^ iSj^ Worksheet cUd' ^jj .Charts Worksheets lU^' ci'jj^

^Likx^l .PivotTables ^Cells ^Ranges djliLkill JLi cijUjI£

,(J;ixjaSI (J^l^ diUjlSlI "Object Model cijUjISJI


Application

-^

Workbooks

->

Workshets

- >

Ranges

- >

Cells


11























(JjVl


Collections

''" (Jjj‘■' ^"Collection ^**'jU'“^ l>^ t•*'i“'M^\i

JjljLaJI <■ Vu^J I (Ji>.l:ij jjljjl "Worksheets collection

jrj-^ > Vu^J I JjliaJl
■Objects (jc. SjUc. Igjlj .la^ ^ Collections u'

‘'-i."'. ,il j i-'l 3-uiIuu]l f^jC- La d^Lu^l ^oJJ

:(> Workbook.xisx <• Vu^J I sjLiVI jlaj 3liaa

Application.Workbooks ("Workbookl.xlsx")

Workbook ‘■lllal.^aJl (j-ojJa Workbookl.xlsx <• Vu^J I SjUiVI tiioj LiAj

."Application object ^tlu=>. tcollection

C qUi.ox>\l ^j^
: J3Li. (> Workbookl.xlsx

Application. Workbooks("Workbookl.xlsx").Worksheets("Sheetl")

^d^LuoVt


Application. Workbooks("Workbookl.xlsx").Worksheets("Sheetl").Range("Al")

i yu^^W ^i£ lil .Activ6 objocts c 1 jUjI£JI aIc-

Sheetl ci^' ^jj A1 '-^■^j'j *• Vu^ .a \ l ja Workbookl.xlsx

I

Worksheets("Sheetl").Range("Al")

I (_jx e^LuoVI 4jajudj]| (_J.aaJI Sheetl CjjlS
>>>