Ma'aikatan VBA da Ayyukan Gina

Bayanin VBA na Excel

Lokacin rubuta lambar VBA a cikin Excel, ana amfani da saitin ginannen masu aiki a kowane mataki. An raba waɗannan masu aiki zuwa lissafi, kirtani, kwatantawa da masu aiki masu ma'ana. Na gaba, za mu dubi kowane rukuni na masu aiki daki-daki.

Ma'aikatan Lissafi

An jera manyan ma'aikatan lissafin VBA a cikin tebur da ke ƙasa.

Rukunin dama na tebur yana nuna fifikon ma'aikaci a cikin rashi bayanan ƙira. Ta ƙara baƙar fata zuwa magana, zaku iya canza tsarin da aka aiwatar da maganganun VBA kamar yadda kuke so.

OperatorActionfifiko

(1 - mafi girma; 5 - mafi ƙasƙanci)

^ma'aikacin karin magana1
*mai aiki da yawa2
/ma'aikacin rabo2
Rarraba ba tare da saura ba - yana dawo da sakamakon raba lambobi biyu ba tare da saura ba. Misali, 74 zai mayar da sakamakon 13
JaruntakanModulo (rago) afareta – yana mayar da ragowar bayan raba lambobi biyu. Misali, 8 da 3 zai mayar da sakamakon 2.4
+Mai aiki da ƙari5
-mai aikin ragi5

Ma'aikatan Kirtani

Babban ma'aikacin kirtani a cikin Excel VBA shine ma'aikacin haɗin gwiwa & (haɗe):

OperatorAction
&ma'aikacin concatenation. Misali, magana "A" & "B" zai mayar da sakamakon AB.

Kwatanta Masu Aiki

Ana amfani da masu yin kwatancen don kwatanta lambobi biyu ko kirtani da dawo da nau'in ƙimar boolean Boolean (Gaskiya ko Karya). An jera manyan ma'aikatan kwatancen Excel VBA a cikin wannan tebur:

OperatorAction
=Daidai
<>Ba daidai ba
<Kadan
>Karin bayani
<=Kasa da ko daidai
>=Girma ko daidai

Masu amfani da hankali

Masu aiki masu ma'ana, kamar masu aikin kwatance, suna dawo da nau'in ƙimar boolean Boolean (Gaskiya ko Karya). An jera manyan ma'aikata masu ma'ana na Excel VBA a cikin tebur da ke ƙasa:

OperatorAction
kumaaikin haɗin gwiwa, ma'aikacin ma'ana И. Misali, magana A da B zai dawo Gaskiya, idan A и B duka biyu daidai suke Gaskiya, in ba haka ba koma arya.
OrAyyukan rarrabawa, ma'aikacin ma'ana OR. Misali, magana A ko B zai dawo Gaskiya, idan A or B daidai suke Gaskiya, kuma zai dawo arya, idan A и B duka biyu daidai suke arya.
baNegation aiki, ma'aikacin ma'ana BA. Misali, magana Ba A zai dawo Gaskiya, idan A daidai arya, ko dawowa arya, idan A daidai Gaskiya.

Teburin da ke sama bai lissafa duk masu aiki da ma'ana da ke cikin VBA ba. Ana iya samun cikakken jerin ma'aikata masu ma'ana a Cibiyar Haɓaka Kayayyakin Kayayyakin Kayayyakin Kayayyakin.

Ayyukan da aka Gina

Akwai ayyuka da yawa da aka gina a cikin VBA waɗanda za a iya amfani da su lokacin rubuta lamba. An jera a ƙasa wasu daga cikin waɗanda aka fi amfani da su:

aikiAction
AbsYana dawo da cikakkiyar ƙimar lambar da aka bayar.

Example:

  • Ba (-20) ya dawo da darajar 20;
  • ab (20) dawo da darajar 20.
ChrYana dawo da harafin ANSI daidai da ƙimar lamba na siga.

Example:

  • Tarihi (10) ya dawo hutun layi;
  • Tarihi (97) mayar da hali a.
RanaYana dawo da tsarin kwanan wata.
Kwanan wata ƘaraYana ƙara ƙayyadadden tazarar lokaci zuwa kwanan wata da aka bayar. Tsarin aiki:

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

Ina hujjar lokaci lokaci yana ƙayyade nau'in tazarar lokaci da aka ƙara zuwa abin da aka bayar date a cikin adadin da aka ƙayyade a cikin hujja lambar.

Hujjar lokaci lokaci zai iya ɗaukar ɗaya daga cikin dabi'u masu zuwa:

Tazaradarajar
yyyyshekara
qkwata
mwatan
yranar shekara
drana
wranar mako
wwmako
hhour
nminute
sbiyu

Example:

  • Kwanan wataAdd («d», 32, «01/01/2015») ya kara kwanaki 32 zuwa ranar 01/01/2015 don haka ya dawo ranar 02/02/2015.
  • Kwanan wataAdd («ww», 36, «01/01/2015») ya kara makonni 36 zuwa ranar 01/01/2015 kuma ya dawo da ranar 09/09/2015.
DateDiffYana ƙididdige adadin ƙayyadaddun tazara tsakanin kwanakin da aka bayar.

Example:

  • DateDiff(«d», «01/01/2015», «02/02/2015») yana lissafin adadin kwanakin tsakanin 01/01/2015 da 02/02/2015, ya dawo 32.
  • DateDiff(«ww», «01/01/2015», «03/03/2016») yana lissafin adadin makonni tsakanin 01/01/2015 da 03/03/2016, ya dawo 61.
RanaYana dawo da lamba ta daidai da ranar wata a cikin kwanan wata da aka bayar.

Example: Rana («29/01/2015») ya dawo da lamba 29.

hourYana dawo da lamba daidai da adadin sa'o'i a lokacin da aka bayar.

Example: Sa'a ("22:45:00") ya dawo da lamba 22.

InStrYana ɗaukar lamba da kirtani biyu azaman muhawara. Yana mayar da matsayin abin da ya faru na kirtani na biyu a cikin na farko, yana fara bincike a matsayin da lamba ta bayar.

Example:

  • InStr (1, "Ga kalmar nema", "kalmar") ya dawo da lamba 13.
  • InStr (14, "Ga kalmar nema, kuma ga wata kalmar nema", "kalmar") ya dawo da lamba 38.

lura: Ƙila ba za a iya ƙayyade hujjar lamba ba, a cikin wannan yanayin binciken yana farawa daga farkon harafin kirtani da aka ƙayyade a cikin hujja na biyu na aikin.

IntYana dawo da sashin lamba na lambar da aka bayar.

Example: Int (5.79) mayar da sakamakon 5.

Isdatedawo Gaskiyaidan darajar da aka bayar kwanan wata ne, ko arya – idan kwanan wata ba.

Example:

  • Ranar («01/01/2015») dawo Gaskiya;
  • Ranar (100) dawo arya.
Kuskuredawo Gaskiyaidan darajar da aka bayar kuskure ne, ko arya – idan ba kuskure ba.
Yana BacewaAn wuce sunan hujjar hanya ta zaɓi azaman hujja ga aikin. Yana Bacewa dawo Gaskiyaidan ba a wuce kima ba don gardamar hanya da ake tambaya.
IsNumericdawo Gaskiyaidan darajar da aka bayar za a iya bi da ita azaman lamba, in ba haka ba ta dawo arya.
HaguYana dawo da ƙayyadadden adadin haruffa daga farkon kirtan da aka bayar. Tsarin aikin shine kamar haka:

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

inda line shine asalin kirtani, kuma tsawon shine adadin haruffan da za a dawo, ana ƙirgawa daga farkon kirtani.

Example:

  • Hagu ("abvgdejziklmn", 4) ya dawo da kirtani "abcg";
  • Hagu ("abvgdejziklmn", 1) ya dawo da igiyar "a".
LenYana dawo da adadin haruffa a cikin kirtani.

Example: Len ("abcdej") ya dawo da lamba 7.

WatanYana dawo da lamba daidai da watan da aka bayar kwanan wata.

Example: Watan («29/01/2015») dawo da darajar 1.

TsakaninYana dawo da ƙayyadadden adadin haruffa daga tsakiyar kirtan da aka bayar. Tsarin aiki:

Tsaki(line, farko, tsawon)

inda line shine asalin kirtani farko – matsayin farkon kirtani da za a ciro, tsawon shine adadin haruffan da za a ciro.

Example:

  • Mid ("abvgdejziklmn", 4, 5) ya dawo da kirtani "inda";
  • Mid ("abvgdejziklmn", 10, 2) ya dawo da kirtani "cl".
minuteYana dawo da lamba ɗaya daidai da adadin mintuna a cikin lokacin da aka bayar. Misali: Minti («22:45:15») dawo da darajar 45.
yanzuYana dawo da tsarin kwanan wata da lokaci.
damaYana dawo da ƙayyadadden adadin haruffa daga ƙarshen kirtan da aka bayar. Tsarin aiki:

Dama(line, tsawon)

ina line shine asalin kirtani, kuma tsawon shine adadin haruffan da za a cire, ana ƙirga daga ƙarshen kirtani da aka bayar.

Example:

  • Dama («abvgdezhziklmn», 4) ya dawo da kirtani "clmn";
  • Dama («abvgdezhziklmn», 1) ya dawo da kirtani "n".
Na biyuYana mayar da lamba daidai da adadin daƙiƙai a cikin lokacin da aka bayar.

Example: Na biyu ("22:45:15") dawo da darajar 15.

SqrYana dawo da tushen murabba'in ƙima na lamba da aka wuce a cikin gardama.

Example:

  • Sqr (4) ya dawo da darajar 2;
  • Sqr (16) dawo da darajar 4.
TimeYana dawo da lokacin tsarin yanzu.
UboundYana dawo da babban rubutun ƙayyadadden girman tsararrun.

lura: Don tsararraki masu girma dabam, hujjar zaɓi na iya zama fihirisar girman girman da zai dawo. Idan ba a bayyana ba, tsoho shine 1.

shekaraYana dawo da lamba daidai da shekarar da aka bayar kwanan wata. Misali: Shekara («29/01/2015») dawo da darajar 2015.

Wannan jeri ya ƙunshi zaɓi kawai na ginanniyar abubuwan da aka fi amfani da su a cikin ayyukan Kayayyakin gani na Excel. Za'a iya samun cikakken jerin ayyukan VBA don amfani a cikin macro na Excel akan Cibiyar Haɓaka Kayayyakin Kayayyakin Kaya.

Leave a Reply