Othandizira a VBA ndi Ntchito Zomangidwa

Mawu a Excel VBA

Polemba khodi ya VBA ku Excel, ma opareshoni omwe adamangidwa amagwiritsidwa ntchito pagawo lililonse. Ogwira ntchitowa amagawidwa m'masamu, zingwe, zofananitsa ndi zomveka. Kenaka, tiwona gulu lirilonse la ogwira ntchito mwatsatanetsatane.

Othandizira Masamu

Ogwiritsa ntchito masamu a VBA akulu adalembedwa patebulo pansipa.

Mzere wakumanja wa tebulo ukuwonetsa wotsogolera wokhazikika ngati palibe mabatani. Powonjezera mabatani ku mawu, mutha kusintha momwe mawu a VBA amachitira momwe mukufunira.

WoyendetsaActionchofunika kwambiri

(1 - apamwamba; 5 - otsika)

^wotsogolera exponentiation1
*wochulukitsa2
/woyendetsa magawo2
Kugawa popanda chotsalira - kumabweretsa zotsatira za kugawa manambala awiri popanda chotsalira. Mwachitsanzo, 74 adzabwezera zotsatira 13
mtimaWogwiritsa ntchito Modulo (otsalira) - amabwezera chotsalira pambuyo pogawa manambala awiri. Mwachitsanzo, 8 pa 3 adzabwezera zotsatira 2.4
+Wothandizira wowonjezera5
-wothandizira kuchotsa5

Ogwiritsa Ntchito zingwe

Woyambira zingwe mu Excel VBA ndiye wogwiritsa ntchito concatenation & (kuphatikiza):

WoyendetsaAction
&wogwiritsa ntchito concatenation. Mwachitsanzo, mawu "A" & "B" adzabwezera zotsatira AB.

Ofanizira Oyerekeza

Ofananitsa amagwiritsidwa ntchito kuyerekeza manambala awiri kapena zingwe ndikubweza mtengo wa boolean wa mtundu Boolean (Zoona kapena Zonama). Ogwiritsa ntchito kwambiri a Excel VBA alembedwa patebulo ili:

WoyendetsaAction
=Mofanana
<>Osafanana
<Zochepa
>Zambiri
<=Zocheperapo kapena zofanana
>=Chachikulu kuposa kapena chofanana

Ogwira ntchito zomveka

Ogwiritsa ntchito mwanzeru, monga ofananitsa, amabwezera mtundu wa boolean mtengo Boolean (Zoona kapena Zonama). Ogwiritsa ntchito mwanzeru a Excel VBA adalembedwa patebulo ili pansipa:

WoyendetsaAction
ndipontchito yolumikizana, wogwiritsa ntchito zomveka И. Mwachitsanzo, mawu A ndi B adzabwerera N'zoona, ngati A и B onse ndi ofanana N'zoona, apo ayi bwererani chonyenga.
OrNtchito yosokoneza, wogwiritsa ntchito zomveka OR. Mwachitsanzo, mawu A kapena B adzabwerera N'zoona, ngati A or B ndi ofanana N'zoona, ndipo adzabweranso chonyenga, ngati A и B onse ndi ofanana chonyenga.
osatiOpaleshoni yotsutsa, wogwiritsa ntchito zomveka OSATI. Mwachitsanzo, mawu Ayi A adzabwerera N'zoona, ngati A mofanana chonyenga, kapena kubwerera chonyenga, ngati A mofanana N'zoona.

Gome ili pamwambapa silinatchule onse ogwira ntchito zomveka omwe alipo mu VBA. Mndandanda wathunthu wa ogwiritsa ntchito mwanzeru ungapezeke pa Visual Basic Developer Center.

Ntchito Zomangidwa

Pali ntchito zambiri zomangidwa zomwe zikupezeka mu VBA zomwe zingagwiritsidwe ntchito polemba ma code. Pansipa pali ena mwa omwe amagwiritsidwa ntchito kwambiri:

ntchitoAction
MphImabweza mtengo wokwanira wa nambala yomwe wapatsidwa.

Chitsanzo:

  • Nsomba (-20) amabwezera mtengo 20;
  • Abs (20) amabwezera mtengo 20.
BCImabweza chilembo cha ANSI chogwirizana ndi mtengo wa nambala ya chizindikirocho.

Chitsanzo:

  • Chr(10) imabweretsa kutha kwa mzere;
  • Chr(97) amabwezeretsa khalidwe a.
DateIkubweza tsiku ladongosolo lapano.
DateAddImawonjezera nthawi yodziwika ku tsiku lomwe laperekedwa. Kalembedwe ka ntchito:

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

Mkangano uli kuti mpata imatsimikizira mtundu wa nthawi yowonjezeredwa ku zomwe zaperekedwa tsiku mu ndalama zomwe zafotokozedwa mumtsutso nambala.

Kutsutsana mpata ikhoza kutenga chimodzi mwazinthu zotsatirazi:

Pakatikatimtengo
yyyychaka
qkotala
mMwezi
ytsiku la chaka
dtsiku
wtsiku la sabata
wwsabata
hOra
nminiti
slachiwiri

Chitsanzo:

  • DateAdd(«d», 32, «01/01/2015») amawonjezera masiku 32 ku deti la 01/01/2015 ndipo motero amabwezera tsiku la 02/02/2015.
  • DateAdd(«ww», 36, «01/01/2015») imawonjezera masabata 36 ku deti la 01/01/2015 ndikubwezeretsa tsiku la 09/09/2015.
DateDiffImawerengetsera kuchuluka kwa nthawi yodziwika pakati pa masiku awiri operekedwa.

Chitsanzo:

  • DateDiff(«d», «01/01/2015», «02/02/2015») imawerengera kuchuluka kwa masiku pakati pa 01/01/2015 ndi 02/02/2015, kubwezera 32.
  • DateDiff(«ww», «01/01/2015», «03/03/2016») imawerengera kuchuluka kwa masabata pakati pa 01/01/2015 ndi 03/03/2016, imabwerera 61.
tsikuImabweza nambala yonse yolingana ndi tsiku la mwezi womwe waperekedwa.

Chitsanzo: Tsiku («29/01/2015») akubweza nambala 29.

OraImabwezera nambala yolingana ndi kuchuluka kwa maola pa nthawi yomwe yaperekedwa.

Chitsanzo: Ola («22:45:00») akubweza nambala 22.

InStrZimatengera chiwerengero chokwanira ndi zingwe ziwiri monga zotsutsana. Imabwezeranso pomwe chingwe chachiwiri chikachitika pakati pa koyamba, kuyambira kusaka pamalo operekedwa ndi nambala yonse.

Chitsanzo:

  • InStr(1, “Nali mawu osakira”, “mawu”) akubweza nambala 13.
  • InStr(14, "Nawa mawu osakira, ndipo nali liwu lina lofufuzira", "mawu") akubweza nambala 38.

Zindikirani: Kutsutsana kwa nambala sikungatchulidwe, pomwe kusaka kumayambira pamtundu woyamba wa chingwe chomwe chafotokozedwa mumtsutso wachiwiri wa ntchitoyi.

IntImabweza gawo lalikulu la nambala yoperekedwa.

Chitsanzo: Int (5.79) zotsatira 5.

IsdateKubwerera N'zoonangati mtengo womwe wapatsidwa ndi tsiku, kapena chonyenga - ngati tsikulo siliri.

Chitsanzo:

  • Tsiku («01/01/2015») akadzabweranso N'zoona;
  • Tsiku (100) akadzabweranso chonyenga.
IsErrorKubwerera N'zoonangati mtengo womwe wapatsidwa ndi cholakwika, kapena chonyenga - ngati sikulakwa.
PalibeDzina la mkangano wosankha umaperekedwa ngati mtsutso ku ntchitoyo. Palibe akadzabweranso N'zoonangati palibe phindu lomwe linaperekedwa pa ndondomeko yomwe ikufunsidwa.
NdiNumericKubwerera N'zoonangati mtengo womwe wapatsidwa ukhoza kuwonedwa ngati nambala, apo ayi kubwereranso chonyenga.
kumanzereImabweza chiwerengero cha zilembo zomwe zatchulidwa kuyambira pachiyambi cha chingwe chomwe chaperekedwa. Syntax ya ntchito ili motere:

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

kumene mzere ndi chingwe choyambirira, ndi Kutalika ndi chiwerengero cha zilembo zobwerera, kuwerengera kuyambira pachiyambi cha chingwe.

Chitsanzo:

  • Left(“abvgdejziklmn”, 4) amabweretsa chingwe "abcg";
  • Left(“abvgdejziklmn”, 1) imabweretsanso chingwe "a".
LenKubweza chiwerengero cha zilembo mu chingwe.

Chitsanzo: Len ("abcdej") akubweza nambala 7.

mweziImabweza nambala yonse yolingana ndi mwezi wa tsiku lomwe laperekedwa.

Chitsanzo: Mwezi («29/01/2015») amabwezera mtengo 1.

MiyeziImabweza chiwerengero cha zilembo zomwe zatchulidwa pakati pa zingwe zomwe zaperekedwa. Kalembedwe ka ntchito:

Pakati (mzere, chiyambi, Kutalika)

kumene mzere ndiye chingwe choyambirira chiyambi - malo a chiyambi cha chingwe chomwe chiyenera kuchotsedwa, Kutalika ndi chiwerengero cha zilembo zomwe ziyenera kuchotsedwa.

Chitsanzo:

  • Mid(“abvgdejziklmn”, 4, 5) imabweretsanso chingwe "komwe";
  • Mid(“abvgdejziklmn”, 10, 2) imabweretsanso chingwe "cl".
MinuteImabwezera nambala yolingana ndi nambala ya mphindi mu nthawi yoperekedwa. Chitsanzo: Mphindi («22:45:15») amabwezera mtengo 45.
TsopanoIkubweza deti ndi nthawi yadongosolo.
ChabwinoImabweza chiwerengero cha zilembo zomwe zatchulidwa kumapeto kwa chingwe chomwe chaperekedwa. Kalembedwe ka ntchito:

Kumanja (mzere, Kutalika)

Kodi mzere ndi chingwe choyambirira, ndi Kutalika ndi chiwerengero cha zilembo zochotsedwa, kuwerengera kuchokera kumapeto kwa chingwe choperekedwa.

Chitsanzo:

  • Kumanja(«abvgdezhziklmn», 4) amabweretsa chingwe "clmn";
  • Kumanja(«abvgdezhziklmn», 1) imabweretsanso chingwe "n".
ChachiwiriImabweza chiwerengero chofanana ndi chiwerengero cha masekondi mu nthawi yoperekedwa.

Chitsanzo: Chachiwiri («22:45:15») amabwezera mtengo 15.

SqrImabweza sikweya mizu ya nambala yomwe yadutsa mumtsutso.

Chitsanzo:

  • Sqr(4) amabwezera mtengo 2;
  • Sqr(16) amabwezera mtengo 4.
TimeImabwezeranso nthawi yadongosolo.
UboundImabwezeranso mawu apamwamba amtundu womwe wasankhidwa.

Zindikirani: Pamagulu ambiri, mkangano wosankha ukhoza kukhala mlozera woti mubwererenso. Ngati sichinatchulidwe, chokhazikika ndi 1.

chakaImabweza chiwerengero chofanana ndi chaka cha deti loperekedwa. Chitsanzo: Chaka («29/01/2015») amabwezera mtengo 2015.

Mndandandawu umaphatikizapo kusankha kwa ntchito zomwe zimagwiritsidwa ntchito kwambiri mu Excel Visual Basic. Mndandanda wokwanira wa ntchito za VBA zomwe zingagwiritsidwe ntchito mu Excel macros zitha kupezeka pa Visual Basic Developer Center.

Siyani Mumakonda