Hawl-wadeennada VBA-da iyo Hawlaha La-dhisay

Odhaahyada VBA ee Excel

Markaad ku qorayso koodka VBA ee Excel, qayb ka mid ah hawlwadeenada gudaha ku jira ayaa loo isticmaalaa tallaabo kasta. Hawlwadeenadani waxa ay u qaybsamaan xisaab, xadhig, isbarbardhig iyo hawlwadeeno macquul ah. Marka xigta, waxaan si faahfaahsan u eegi doonaa koox kasta oo ka mid ah hawl-wadeennada.

Hawl-wadeennada Xisaabta

Hawl-wadeennada xisaabta VBA-da ee ugu muhiimsan waxay ku taxan yihiin shaxda hoose.

Tiirka midig ee jaantusku wuxuu muujinayaa horumarka hawlwadeenka caadiga ah ee maqnaanshaha qawska. Markaad ku darto jaantusyada tibaaxaha, waxaad bedeli kartaa sida ay u kala horreeyaan bayaanada VBA-da loo fuliyo sidaad rabto.

HawlwadeennadaActionmudnaanta

(1 - ugu sarreeya; 5 - ugu hooseeya)

^hawlwadeenka jibbaarada1
*hawlwadeenka isku dhufashada2
/hawlwadeenka qaybta2
Qaybinta aan ka hadhin - waxay soo celisaa natiijada qaybinta laba lambar iyada oo aan wax ka dhiman. Tusaale ahaan, 74 soo celin doona natiijada 13
CourageHawlwadeenka Modulo (haraadi) – waxa uu soo celiyaa inta hadhay ka dib marka uu qaybiyo laba lambar. Tusaale ahaan, 8 ka dhanka ah 3 soo celin doona natiijada 2.4
+Hawlwadeenka wax-ka-qabashada5
-hawlwadeenka kala goynta5

Wadayaasha Xargaha

Hawlwadeenka xargaha aasaasiga ah ee Excel VBA waa hawlwadeenka isku xidhka & (isku dar):

HawlwadeennadaAction
&hawlwadeenka isku xidhka. Tusaale ahaan, odhaahda "A" & "B" soo celin doona natiijada AB.

Wadayaasha Isbarbardhiga

Hawlwadeennada isbarbardhigga ayaa loo adeegsadaa in ay is barbar dhigaan laba tiro ama xargo oo ay ku soo celiyaan qiimaha boolean ee nooca ah Boolean (Run mise Been). Hawlwadeenada isbarbardhigga VBA-da ee ugu muhiimsan waxay ku taxan yihiin shaxdan:

HawlwadeennadaAction
=Si lamid ah
<>Uma sinna
<In ka yar
>Macluumaad intaas ka badan
<=In ka yar ama la siman
>=Ka weyn ama la siman

Hawl wadeenada caqliga

Hawlwadeennada macquulka ah, sida kuwa isbarbardhigga ah, waxay soo celiyaan qiimaha boolean ee nooca ah Boolean (Run mise Been). Hawl-wadeennada macquulka ah ee Excel VBA waxay ku taxan yihiin shaxda hoose:

HawlwadeennadaAction
Oohawlgalka isku xirka, hawlwadeen macquul ah И. Tusaale ahaan, odhaahda A iyo B soo laaban doona True, haddii A и B labaduba waa siman yihiin True, haddii kale soo noqo Beenta.
OrHawlgalka kala qaybinta, hawlwadeen macquul ah OR. Tusaale ahaan, odhaahda A ama B soo laaban doona True, haddii A or B waa isku mid True, wuuna soo laaban doonaa Beenta, haddii A и B labaduba waa siman yihiin Beenta.
Ma ahaHawlgalka diidmada, hawlwadeen macquul ah MA. Tusaale ahaan, odhaahda Ma aha A soo laaban doona True, haddii A si siman Beenta, ama soo noqo Beenta, haddii A si siman True.

Shaxda sare kuma taxna dhammaan hawl-wadeennada macquulka ah ee laga heli karo VBA. Liis dhamaystiran oo ah hawl wadeenada macquulka ah ayaa laga heli karaa Xarunta Horumarinta Visual Basic.

Hawlaha lagu dhex dhisay

Waxaa jira hawlo badan oo gudaha ku dhex jira oo laga heli karo VBA kuwaas oo la isticmaali karo marka kood la qorayo. Hoos waxaa ku qoran qaar ka mid ah kuwa ugu badan ee la isticmaalo:

functionAction
maankuSoo celisa qiimaha saxda ah ee lambarka la bixiyay.

Tusaale:

  • Abs (-20) soo celisa qiimaha 20;
  • Abs(20) soo celisa qiimihii 20.
TaariikhdaSoo celisa jilaha ANSI ee u dhigma qiimaha nambarada cabbirka.

Tusaale:

  • Taariikh (10) soo celisa xariiqda jaban;
  • Taariikh (97) soo celiyaa dabeecad a.
TaariikhdaSoo celisa taariikhda nidaamka hadda jira.
TaariikhdaAddWuxuu ku darayaa waqti go'an taariikhda la bixiyay. Shaqada syntax:

DateAdd(интервал, число, дата)

Aaway dooda bareeg ayaa go'aamisa nooca u dhexeeya wakhtiga lagu daray inta la bixiyay taariikhda qaddarka lagu sheegay doodda tirada.

Argagax bareeg waxay qaadan kartaa mid ka mid ah qiyamka soo socda:

WaqtiQiimaha
haasanadkii
qrubuc
mbisha
ymaalinta sanadka
dmaalintii
wmaalinta toddobaadka
wwtoddobaad
hsaacad
ndaqiiqo
slabaad

Tusaale:

  • TaariikhdaAdd («d», 32, «01/01/2015») wuxuu ku darayaa 32 maalmood taariikhda 01/01/2015 wuxuuna sidaas ku soo celinayaa taariikhda 02/02/2015.
  • TaariikhdaAdd («ww», 36, «01/01/2015») wuxuu ku darayaa 36 toddobaad taariikhda 01/01/2015 wuxuuna soo celinayaa taariikhda 09/09/2015.
DateDiffWuxuu xisaabiyaa tirada waqtiyada cayiman ee u dhexeeya labada taariikhood ee la bixiyay.

Tusaale:

  • DateDiff(«d», «01/01/2015», «02/02/2015») xisaabiyaa tirada maalmaha u dhexeeya 01/01/2015 iyo 02/02/2015, soo laabtay 32.
  • DateDiff(«ww», «01/01/2015», «03/03/2016») xisaabiyaa tirada toddobaadyada u dhexeeya 01/01/2015 iyo 03/03/2016, soo laabtay 61.
MaalintaSoo celisaa tiro u dhiganta maalinta bisha ee taariikhda la cayimay.

Tusaale: Maalinta («29/01/2015») soo celisa lambarka 29.

saacadSoo celisa tiro u dhiganta tirada saacadaha wakhtiga la siiyay.

Tusaale: Saacad («22:45:00») soo celisa lambarka 22.

Gudaha StrWaxay qaadataa halbeeg iyo laba xadhig oo dood ahaan ah. Soo celiyaa booska uu ka dhacay xadhigga labaad gudaha kan hore, isagoo ka bilaabaya raadinta booska uu bixiyo tirooyin.

Tusaale:

  • InStr (1, "Halkan waa ereyga raadinta", "ereyga") soo celisa lambarka 13.
  • InStr (14, "Halkan waa ereyga raadinta, oo halkan waa kelmed kale", "erey") soo celisa lambarka 38.

Fiiro gaar ah: Doodda tirada lama cayimi karo, taas oo ay xaaladdu tahay raadintu waxay ka bilaabataa xarafka ugu horreeya ee xadhigga lagu qeexay doodda labaad ee shaqada.

IntWaxay soo celisaa qaybta isugeynta lambarka la siiyay.

Tusaale: Int (5.79) soo celinta natiijada 5.

Isdatedib Truehaddii qiimaha la siiyay ay tahay taariikh, ama Beenta - haddii aysan taariikhda ahayn.

Tusaale:

  • Taariikhdu («01/01/2015») celinta True;
  • Taariikhda(100) celinta Beenta.
Khaladdib Truehaddii qiimaha la siiyay uu yahay qalad, ama Beenta - haddii aysan ahayn qalad.
MaqanMagaca dooda habraaca ikhtiyaarka ah waxa loo gudbiyaa dood ahaan shaqada. Maqan celinta Truehaddii aan wax qiimo ah loo gudbin dooda habraaca su'aasha.
Is Numberdib Truehaddii qiimaha la siiyay loo dawayn karo sida lambar, haddii kale soo noqda Beenta.
LeftWaxay soo celisaa tirada la cayimay ee xarfaha laga bilaabo bilowga xargaha la bixiyay. Shaqada syntax waa sidan:

Left(строка, длина)

halkaas oo line waa xariggii asalka ahaa, iyo dhererka waa tirada xarfaha soo laabanaya, laga bilaabo bilowga xargaha.

Tusaale:

  • Bidix ("abvgdejziklmn", 4) soo celisa xadhigga "abcg";
  • Bidix ("abvgdejziklmn", 1) soo celisa xadhigga "a".
LenSoo celisa tirada xarfaha xargaha.

Tusaale: Len ("abcdej") soo celisa lambarka 7.

MonthSoo celisaa tiro u dhiganta bisha taariikhda la bixiyay.

Tusaale: Bisha(«29/01/2015») soo celisa qiimihii 1.

MidWuxuu ka soo celiyaa tirada la cayimay ee xarfaha bartamaha xargaha la siiyay. Shaqada syntax:

Mid (line, bilow, dhererka)

halkaas oo line waa xadhigii asalka ahaa bilow - booska bilowga xadhigga la soo saarayo, dhererka waa tirada xarfaha la soo saarayo.

Tusaale:

  • Mid ("abvgdejziklmn", 4, 5) soo celiyaa xadhigga "halka";
  • Mid ("abvgdejziklmn", 10, 2) soo celisa xadhigga "cl".
daqiiqoSoo celisa tiro u dhiganta tirada daqiiqadaha ee wakhtiga la siiyay. Tusaale: Daqiiqado («22:45:15») soo celisa qiimihii 45.
HaddaSoo celisa nidaamka hadda jira taariikhda iyo wakhtiga.
XuquuqdaWuxuu soo celiyaa tirada la cayimay ee xarfaha laga soo bilaabo dhamaadka xargaha la siiyay. Shaqada syntax:

Midigline, dhererka)

Halkee line waa xariggii asalka ahaa, iyo dhererka waa tirada xarfaha la soo saarayo, laga soo bilaabo dhamaadka xargaha la siiyay.

Tusaale:

  • Midig («abvgdezhziklmn», 4) soo celiyaa xadhigga "clmn";
  • Midig («abvgdezhziklmn», 1) soo celisa xadhigga "n".
labaadSoo celisa tiro u dhiganta tirada ilbiriqsi ee wakhtiga la siiyay.

Tusaale: Labaad («22:45:15») soo celisa qiimihii 15.

SqrWaxay soo celisaa xidid laba jibaaran ee qiimaha tirada ee lagu gudbiyay dooda.

Tusaale:

  • Sqr (4) soo celisa qiimaha 2;
  • Sqr (16) soo celisa qiimihii 4.
TimeSoo celisa wakhtiga nidaamka hadda jira.
UboundSoo celiyaa qoraalka sare ee cabbirka habaynta la cayimay.

Fiiro gaar ah: Arraysyada kala duwan, dood ikhtiyaari ah ayaa laga yaabaa inay noqoto tusaha cabbirka la soo celinayo. Haddii aan la cayimin, default waa 1.

sanadkiiSoo celisaa tiro u dhiganta sanadka taariikhda la bixiyay. Tusaale: Sannad («29/01/2015») soo celisa qiimihii 2015.

Liiskaan waxaa ku jira oo kaliya xulashada kuwa ugu badan ee lagu dhex dhisay Excel Visual Basic functional. Liis dhammaystiran oo ah hawlaha VBA ee diyaarka u ah in lagu isticmaalo macros Excel waxa laga heli karaa Xarunta Horumarinta Visual Basic.

Leave a Reply