VBA Operators uye Akavakwa-mukati Mabasa

Excel VBA zvinyorwa

Paunenge uchinyora VBA kodhi muExcel, seti yeakavakirwa-mukati maopareta anoshandiswa pamatanho ese. Aya maopareta akakamurwa kuita masvomhu, tambo, kuenzanisa uye zvine musoro. Zvadaro, tichatarisa boka rega rega revashandi zvakadzama.

Mathematics Operators

Iwo makuru eVBA math operators akanyorwa patafura pazasi.

Koramu yekurudyi yetafura inoratidza iyo default opareta kutangira mukushaikwa kwevabereki. Nekuwedzera maparentheses kune chirevo, unogona kushandura kurongeka uko zvirevo zveVBA zvinoitwa sezvaunoda.

OperatorActionchikosheshwa

(1 - yepamusorosoro; 5 - yakaderera)

^exponentiation operator1
*kuwanza mushandisi2
/division operator2
Kupatsanura pasina kusara - inodzosera mhedzisiro yekukamura nhamba mbiri pasina chasara. Semuyenzaniso, 74 ichadzorera mhedzisiro 13
chivindiModulo (yasara) opareta - inodzosa zvakasara mushure mekupatsanura nhamba mbiri. Semuyenzaniso, 8 Kupesana ne3 ichadzorera mhedzisiro 2.4
+Kuwedzera opareta5
-kubvisa mushandisi5

Vashandi veTambo

Iyo yakakosha tambo opareta muExcel VBA ndiye concatenation opareta & (batanidza):

OperatorAction
&concatenation operator. Somuenzaniso, chirevo "A" uye "B" ichadzorera mhedzisiro AB.

Kufananidza Vashandisi

Vashandisi vekuenzanisa vanoshandiswa kuenzanisa nhamba mbiri kana tambo uye kudzorera kukosha kweboolean yemhando boolean (Chokwadi kana Nhema). Iwo makuru eExcel VBA ekuenzanisa anoshanda akanyorwa mune iyi tafura:

OperatorAction
=Zvakaenzana
<>Kwete kuenzana
<Kuderera
>Mamwe mashoko
<=Zvishoma pane kana kuenzana
>=Mukuru pane kana kuenzana

Vashandisi vane musoro

Vanoshandisa zvine musoro, sevashandisi vekuenzanisa, vanodzosa kukosha kweboolean yemhando boolean (Chokwadi kana Nhema). Iwo makuru ane musoro anoshanda eExcel VBA akanyorwa patafura pazasi:

OperatorAction
uyemubatanidzwa kushanda, zvine musoro mushandisi И. Somuenzaniso, chirevo A uye B vachadzoka chokwadi, kana A и B zvose zvakaenzana chokwadi, zvikasadaro dzokai venhema.
OrDisjunction operation, logic operator OR. Somuenzaniso, chirevo A kana B vachadzoka chokwadi, kana A or B vakaenzana chokwadi, uye ndichadzoka venhema, kana A и B zvose zvakaenzana venhema.
kweteNegation operation, logic operator NOT. Somuenzaniso, chirevo Kwete A vachadzoka chokwadi, kana A zvakaenzana venhema, kana kudzoka venhema, kana A zvakaenzana chokwadi.

Tafura iri pamusoro hainyore vese vane musoro vanoshanda muVBA. Rondedzero yakazara yevashandisi vane musoro inogona kuwanikwa paVisual Basic Developer Center.

Yakavakwa-mukati Mabasa

Kune akawanda akavakirwa-mukati mabasa anowanikwa muVBA anogona kushandiswa pakunyora kodhi. Zvinyorwa pasi apa ndezvimwe zvezvinonyanya kushandiswa:

basaAction
absInodzosa kukosha kweiyo nhamba yakapihwa.

muenzaniso:

  • Abs(-20) inodzorera kukosha kwe20;
  • Abs(20) inodzorera kukosha kwe20.
ChrInodzosa mavara eANSI anoenderana nenhamba kukosha kweparameter.

muenzaniso:

  • Chr(10) inodzorera kutyora kwemutsara;
  • Chr(97) inodzorera hunhu a.
zuvaInodzosa zuva rehurongwa hwazvino.
DateAddInowedzera nguva yakatarwa kune zuva rakapihwa. Sintax yebasa:

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

Nharo iripi chinguva inotara mhando yenguva yenguva yakawedzerwa kune yakapihwa zuva mumari yakataurwa munharo nhamba.

Argument chinguva inogona kutora chimwe chezvinotevera zvakakosha:

Kupindiraukoshi
yyyygore
qkota
mmwedzi
yzuva regore
dzuva
wzuva revhiki
wwvhiki
hawa
nMineti
schepiri

muenzaniso:

  • DateAdd(«d», 32, «01/01/2015») anowedzera mazuva makumi matatu nemaviri kumusi wa 32/01/01 uye nokudaro anodzosera zuva 2015/02/02.
  • DateAdd(«ww», 36, «01/01/2015») anowedzera mavhiki makumi matatu nematanhatu kumusi wa36/01/01 uye anodzosera zuva 2015/09/09.
DateDiffInoverengera nhamba yenguva yakatarwa pakati pemazuva maviri akapihwa.

muenzaniso:

  • DateDiff(«d», «01/01/2015», «02/02/2015») inoverenga nhamba yemazuva pakati pe01/01/2015 na 02/02/2015, inodzorera 32.
  • DateDiff(«ww», «01/01/2015», «03/03/2016») inoverenga nhamba yemavhiki pakati pe01/01/2015 uye 03/03/2016, inodzorera 61.
zuvaInodzosa nhamba inoenderana nezuva remwedzi muzuva rakapihwa.

muenzaniso: Zuva(«29/01/2015») inodzosera nhamba 29.

awaInodzosa nhamba inoenderana nenhamba yemaawa panguva yakapihwa.

muenzaniso: Awa(«22:45:00») inodzosera nhamba 22.

InStrZvinotora nhamba netambo mbiri senharo. Inodzosa nzvimbo yekuitika kwetambo yechipiri mukati mekutanga, kutanga kutsvaga panzvimbo yakapihwa nenhamba.

muenzaniso:

  • InStr (1, "Heino izwi rekutsvaga", "izwi") inodzosera nhamba 13.
  • InStr(14, "Heino izwi rekutsvaga, uye heino rimwe izwi rekutsvaga", "izwi") inodzosera nhamba 38.

Cherechedza: Nharo yenhamba haigone kutaurwa, apa kutsvaga kunotanga kubva pamavara ekutanga etambo inotsanangurwa munharo yechipiri yebasa.

IntInodzosa chikamu chizere chenhamba yakapihwa.

muenzaniso: Int(5.79) inodzosera mhinduro 5.

IsdateReturns chokwadikana kukosha kwakapihwa iri zuva, kana venhema - kana zuva risiri.

muenzaniso:

  • Zuva(«01/01/2015») inodzoka chokwadi;
  • Zuva(100) inodzoka venhema.
IsErrorReturns chokwadikana kukosha kwakapihwa kuri kukanganisa, kana venhema - kana isiri kukanganisa.
IsMissingZita resarudzo yemaitiro nharo rinopfuudzwa senharo kubasa. IsMissing inodzoka chokwadikana pasina kukosha kwakapfuudzwa kune iyo nzira gakava iri mubvunzo.
IsNumericReturns chokwadikana kukosha kwakapihwa kunogona kubatwa senhamba, zvikasadaro inodzoka venhema.
RuboshweInodzosa nhamba yakatarwa yemavara kubva panotangira tambo yakapihwa. Basa re syntax rakaita seizvi:

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

apo mutsetse ndiyo tambo yepakutanga, uye Kureba inhamba yemavara ekudzoka, kuverenga kubva panotangira tambo.

muenzaniso:

  • Kuruboshwe(“abvgdejziklmn”, 4) inodzorera tambo "abcg";
  • Kuruboshwe(“abvgdejziklmn”, 1) inodzorera tambo "a".
LenInodzosa nhamba yemavara mutambo.

muenzaniso: Len ("abcdej") inodzosera nhamba 7.

MwedziInodzosa nhamba inoenderana nemwedzi wezuva rakapihwa.

muenzaniso: Mwedzi(«29/01/2015») inodzorera kukosha kwe1.

MidInodzosa nhamba yakatarwa yemavara kubva pakati petambo yakapihwa. Sintax yebasa:

Pakati (mutsetse, kutanga, Kureba)

apo mutsetse ndiyo tambo yepakutanga kutanga - nzvimbo yepakutanga kwetambo inofanira kubudiswa, Kureba inhamba yemavara achatorwa.

muenzaniso:

  • Mid(“abvgdejziklmn”, 4, 5) inodzorera tambo "kupi";
  • Mid(“abvgdejziklmn”, 10, 2) inodzorera tambo "cl".
MinetiInodzosa nhamba inoenderana nenhamba yemaminetsi panguva yakapihwa. Muenzaniso: Miniti(«22:45:15») inodzorera kukosha kwe45.
ikozvinoInodzosa yazvino system date uye nguva.
RudyiInodzosa nhamba yakatarwa yemavara kubva kumagumo etambo yakapihwa. Sintax yebasa:

Rudyi(mutsetse, Kureba)

apo mutsetse ndiyo tambo yepakutanga, uye Kureba inhamba yemavara ekutora, kuverenga kubva kumagumo etambo yakapihwa.

muenzaniso:

  • Kurudyi(«abvgdezhziklmn», 4) inodzorera tambo "clmn";
  • Kurudyi(«abvgdezhziklmn», 1) inodzorera tambo "n".
ChepiriInodzosa nhamba inoenderana nenhamba yemasekonzi panguva yakapihwa.

muenzaniso: Chechipiri(«22:45:15») inodzorera kukosha kwe15.

SqrInodzosa sikweya midzi yenhamba yeukoshi hwapfuura munharo.

muenzaniso:

  • Sqr(4) inodzorera kukosha kwe2;
  • Sqr(16) inodzorera kukosha kwe4.
nguvaInodzosa ikozvino system nguva.
UboundInodzosa zvinyorwa zvepamusoro zvearongedzero yakataurwa.

Cherechedza: Kumadimensional arrays, nharo yesarudzo inogona kunge iri indekisi yekuti ndeupi udzoke. Kana zvisina kutaurwa, iyo default ndeye 1.

goreInodzosa nhamba inoenderana negore rezuva rakapihwa. Muenzaniso: Gore(«29/01/2015») inodzorera kukosha kwe2015.

Iyi rondedzero inosanganisira chete sarudzo yeanonyanya kushandiswa akavakirwa-muExcel Visual Basic mabasa. Rondedzero yakazara yeVBA mabasa anowanikwa kuti ashandiswe muExcel macros anogona kuwanikwa paVisual Basic Developer Center.

Leave a Reply