Ama-Opharetha e-VBA kanye Nemisebenzi eyakhelwe ngaphakathi

Izitatimende ze-Excel VBA

Lapho ubhala ikhodi ye-VBA ku-Excel, isethi yama-opharetha akhelwe ngaphakathi isetshenziswa kuzo zonke izinyathelo. Lawa ma-opharetha ahlukaniswe ngama-opharetha ezibalo, iyunithi yezinhlamvu, iziqhathaniso kanye nezinengqondo. Okulandelayo, sizobheka iqembu ngalinye labasebenzisi ngokuningiliziwe.

Abasebenzi Bezibalo

Ama-opharetha ezibalo e-VBA amakhulu afakwe ohlwini lwethebula elingezansi.

Ikholomu yesokudla yethebula ibonisa ukwendulela komsebenzisi okuzenzakalelayo lapho kungekho abakaki. Ngokungeza abakaki kusisho, ungashintsha indlela izitatimende ze-VBA ezisetshenziswa ngayo ngendlela ofisa ngayo.

I-OperatorActionkuqala

(1 - eliphakeme kakhulu; 5 - eliphansi kakhulu)

^umqhubi we-exponentiation1
*opharetha wokuphindaphinda2
/opharetha besigaba2
Ukwahlukanisa ngaphandle kwensali - ibuyisela umphumela wokuhlukanisa izinombolo ezimbili ngaphandle kwensali. Ngokwesibonelo, 74 izobuyisela umphumela 13
IsibindiU-opharetha weModulo (osele) – ubuyisela okusele ngemva kokuhlukanisa izinombolo ezimbili. Ngokwesibonelo, 8 ngokumelene 3 izobuyisela umphumela 2.4
+U-opharetha wokwengeza5
-ukususa opharetha5

Ama-String Opharetha

I-opharetha yeyunithi yezinhlamvu eyisisekelo ku-Excel VBA i-concatenation operator & (hlanganisa):

I-OperatorAction
&umqhubi we-concatenation. Ngokwesibonelo, inkulumo "A" & "B" izobuyisela umphumela AB.

Ukuqhathanisa Opharetha

Ama-opharetha wokuqhathanisa asetshenziselwa ukuqhathanisa izinombolo ezimbili noma amayunithi ezinhlamvu futhi abuyisele inani le-boolean lohlobo Boolean (Iqiniso noma Amanga). Ama-opharetha wokuqhathanisa we-Excel VBA abhalwe kuleli thebula:

I-OperatorAction
=Ngokulinganayo
<>Ayilingani
<Ngaphansi
>Okuningi
<=Ngaphansi noma kulingana
>=Okukhulu noma kuyalingana

Ama-opharetha anengqondo

Ama-opharetha anengqondo, njengama-opharetha wokuqhathanisa, abuyisela inani le-boolean lohlobo Boolean (Iqiniso noma Amanga). Ama-opharetha anengqondo e-Excel VBA abhalwe etafuleni elingezansi:

I-OperatorAction
Futhiukusebenza ngokuhlanganyela, opharetha okunengqondo И. Ngokwesibonelo, inkulumo A futhi B uzobuya Yiqiniso, uma A и B bobabili bayalingana Yiqiniso, kungenjalo buyela Amanga.
OrUkusebenza kwe-dijunction, opharetha onengqondo OR. Ngokwesibonelo, inkulumo A noma B uzobuya Yiqiniso, uma A or B bayalingana Yiqiniso, futhi ngizobuya Amanga, uma A и B bobabili bayalingana Amanga.
NotUkusebenza kokuphika, u-opharetha onengqondo HHAYI. Ngokwesibonelo, inkulumo Hhayi u-A uzobuya Yiqiniso, uma A ngokulinganayo Amanga, noma buyela Amanga, uma A ngokulinganayo Yiqiniso.

Ithebula elingenhla alibafaki bonke opharetha abanengqondo abatholakala ku-VBA. Uhlu oluphelele lwabasebenzisi abanengqondo lungatholakala ku-Visual Basic Developer Center.

Imisebenzi eyakhelwe ngaphakathi

Kunemisebenzi eminingi eyakhelwe ngaphakathi etholakala ku-VBA engasetshenziswa lapho kubhalwa ikhodi. Okubhalwe ngezansi ezinye ezisetshenziswa kakhulu:

UmsebenziAction
AbsIbuyisela inani eliphelele lenombolo enikeziwe.

Isibonelo:

  • I-Abs(-20) ibuyisela inani 20;
  • I-Abs(20) ibuyisela inani elingu-20.
I-ChrIbuyisela uhlamvu lwe-ANSI oluhambisana nenani lenombolo yepharamitha.

Isibonelo:

  • Chr(10) ibuyisela ikhefu lomugqa;
  • Chr(97) ibuyisela umlingiswa a.
UsukuIbuyisela idethi yesistimu yamanje.
IdethiYengezaYengeza isikhawu sesikhathi esishiwo ngosuku olunikeziwe. I-syntax yomsebenzi:

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

Iphi ingxabano isikhathi inquma uhlobo lwesikhawu sesikhathi esingezwe kulokho okunikeziwe Usuku enanini elishiwo kumpikiswano inombolo.

Ukungqubuzana isikhathi ingathatha elinye lamanani alandelayo:

IsikhashanaValue
yyyyNgonyaka
qikota
minyanga
yusuku lonyaka
dusuku
wusuku lwesonto
wwisonto
hihora
niminithi
syesibili

Isibonelo:

  • I-DateAdd(«d», 32, «01/01/2015») yengeza izinsuku ezingama-32 osukwini lwango-01/01/2015 futhi ngaleyo ndlela ibuyisela usuku lwangomhla ka-02/02/2015.
  • DateAdd(«ww», 36, «01/01/2015») yengeza amasonto angama-36 osukwini luka-01/01/2015 futhi ibuyisela usuku lwangomhla zi-09/09/2015.
I-DateDiffIbala inani lezikhawu zesikhathi ezishiwo phakathi kwamadethi amabili anikeziwe.

Isibonelo:

  • DateDiff(«d», «01/01/2015», «02/02/2015») ibala inani lezinsuku phakathi kuka-01/01/2015 kanye no-02/02/2015, ibuyisela 32.
  • DateDiff(«ww», «01/01/2015», «03/03/2016») ibala inani lamasonto phakathi kuka-01/01/2015 kanye no-03/03/2016, ibuyisela 61.
DayIbuyisela inombolo ephelele ehambisana nosuku lwenyanga ngedethi enikeziwe.

Isibonelo: Usuku(«29/01/2015») ibuyisela inombolo 29.

ihoraIbuyisela inombolo ephelele ehambisana nenani lamahora ngesikhathi esinikeziwe.

Isibonelo: Ihora(«22:45:00») ibuyisela inombolo 22.

I-InStrKuthatha inombolo ephelele neyunithi yezinhlamvu ezimbili njengama-agumenti. Ibuyisela indawo yokwenzeka kweyunithi yezinhlamvu yesibili phakathi kweyokuqala, iqala ukusesha endaweni enikezwe inombolo ephelele.

Isibonelo:

  • I-InStr(1, “Nali igama lokusesha”, “igama”) ibuyisela inombolo 13.
  • I-InStr(14, “Nali igama lokusesha, nali elinye igama lokusesha”, “igama”) ibuyisela inombolo 38.

Qaphela: Ukungqubuzana kwenombolo kungase kungacaciswa, lapho ukusesha kuqala kuhlamvu lokuqala lweyunithi yezinhlamvu eshiwo ku-agumenti yesibili yomsebenzi.

IntIbuyisela ingxenye ephelele yenombolo enikeziwe.

Isibonelo: I-Int(5.79) ibuyisela umphumela 5.

I-IsdateIbuyisa Yiqinisouma inani elinikeziwe liwusuku, noma Amanga – uma usuku lungekho.

Isibonelo:

  • Usuku(«01/01/2015») ubuyela Yiqiniso;
  • I-IsDate(100) ubuyela Amanga.
IsErrorIbuyisa Yiqinisouma inani elinikeziwe liyiphutha, noma Amanga – uma kungelona iphutha.
AkukhoIgama le-agumenti yenqubo yokuzikhethela liphasiswa njengengxabano kumsebenzi. Akukho ubuyela Yiqinisouma lingekho inani eliphasisiwe kumpikiswano yenqubo okukhulunywa ngayo.
IsNumericIbuyisa Yiqinisouma inani elinikeziwe lingaphathwa njengenombolo, uma kungenjalo liyabuya Amanga.
KwesobunxeleIbuyisela inombolo eshiwo yezinhlamvu kusukela ekuqaleni kweyunithi yezinhlamvu enikeziwe. I-syntax yomsebenzi imi kanje:

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

lapho umugqa iyintambo yasekuqaleni, futhi ubude inombolo yezinhlamvu ezizobuya, kubalwa kusukela ekuqaleni kweyunithi yezinhlamvu.

Isibonelo:

  • Kwesokunxele(“abvgdejziklmn”, 4) ibuyisela iyunithi yezinhlamvu ethi “abcg”;
  • Kwesokunxele(“abvgdejziklmn”, 1) ibuyisela iyunithi yezinhlamvu “a”.
uLenIbuyisela inombolo yezinhlamvu kuyunithi yezinhlamvu.

Isibonelo: I-Len(“abcdej”) ibuyisela inombolo 7.

izinyangaIbuyisela inombolo ephelele ehambisana nenyanga yedethi enikeziwe.

Isibonelo: Inyanga(«29/01/2015») ibuyisela inani elingu-1.

PhakathiIbuyisela inombolo eshiwo yezinhlamvu ukusuka maphakathi neyunithi yezinhlamvu enikeziwe. I-syntax yomsebenzi:

Phakathi(umugqa, isiqalo, ubude)

lapho umugqa iyunithi yezinhlamvu yasekuqaleni isiqalo - indawo lapho uchungechunge luqala khona, ubude inombolo yezinhlamvu ezizokhishwa.

Isibonelo:

  • Mid(“abvgdejziklmn”, 4, 5) ibuyisela intambo ethi “kuphi”;
  • Mid(“abvgdejziklmn”, 10, 2) ibuyisela iyunithi yezinhlamvu “cl”.
iminithiIbuyisela inombolo ephelele ehambisana nenani lamaminithi ngesikhathi esinikeziwe. Isibonelo: Iminithi («22:45:15») ibuyisela inani elingu-45.
ManjeIbuyisela idethi nesikhathi sesistimu yamanje.
RightIbuyisela inombolo eshiwo yezinhlamvu kusukela ekugcineni kweyunithi yezinhlamvu enikeziwe. I-syntax yomsebenzi:

Kwesokudla(umugqa, ubude)

kuphi umugqa iyintambo yasekuqaleni, futhi ubude inombolo yezinhlamvu ezizokhishwa, kubalwa kusukela ekugcineni kweyunithi yezinhlamvu enikeziwe.

Isibonelo:

  • Kwesokudla(«abvgdezhziklmn», 4) ibuyisela intambo ethi “clmn”;
  • Kwesokudla(«abvgdezhziklmn», 1) ibuyisela iyunithi yezinhlamvu “n”.
OkwesibiliIbuyisela inombolo ephelele ehambisana nenani lamasekhondi esikhathi esinikeziwe.

Isibonelo: Okwesibili(«22:45:15») ibuyisela inani elingu-15.

SqrIbuyisela impande eyisikwele yenani lezinombolo eliphasiswe ku-agumenti.

Isibonelo:

  • Sqr(4) ibuyisela inani 2;
  • Sqr(16) ibuyisela inani elingu-4.
IsikhathiIbuyisela isikhathi sesistimu yamanje.
UboshiweIbuyisela umbhalo ongenhla wobukhulu bamalungu afanayo obushiwo.

Qaphela: Kumalungu afanayo ane-multidimensional, i-agumenti yokuzithandela ingase ibe inkomba okufanele kubuyiswe ubukhulu bayo. Uma kungashiwongo, okuzenzakalelayo kungu-1.

NgonyakaIbuyisela inombolo ephelele ehambisana nonyaka wedethi enikeziwe. Isibonelo: Unyaka(«29/01/2015») ibuyisela inani elingu-2015.

Lolu hlu luhlanganisa kuphela ukukhethwa kwemisebenzi evame ukusetshenziswa eyakhelwe ngaphakathi ye-Excel Visual Basic. Uhlu oluphelele lwemisebenzi ye-VBA etholakalayo ukuze isetshenziswe kuma-Excel macros lungatholakala ku-Visual Basic Developer Center.

shiya impendulo