Contents
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.
Operator | Action | fifiko (1 - mafi girma; 5 - mafi ƙasƙanci) |
---|---|---|
^ | ma'aikacin karin magana | 1 |
* | mai aiki da yawa | 2 |
/ | ma'aikacin rabo | 2 |
Rarraba ba tare da saura ba - yana dawo da sakamakon raba lambobi biyu ba tare da saura ba. Misali, 74 zai mayar da sakamakon 1 | 3 | |
Jaruntakan | Modulo (rago) afareta – yana mayar da ragowar bayan raba lambobi biyu. Misali, 8 da 3 zai mayar da sakamakon 2. | 4 |
+ | Mai aiki da ƙari | 5 |
- | mai aikin ragi | 5 |
Ma'aikatan Kirtani
Babban ma'aikacin kirtani a cikin Excel VBA shine ma'aikacin haɗin gwiwa & (haɗe):
Operator | Action |
---|---|
& | 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:
Operator | Action |
---|---|
= | 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:
Operator | Action |
---|---|
kuma | aikin 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. |
Or | Ayyukan 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. |
ba | Negation 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:
aiki | Action | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Abs | Yana dawo da cikakkiyar ƙimar lambar da aka bayar. Example:
| ||||||||||||||||||||||
Chr | Yana dawo da harafin ANSI daidai da ƙimar lamba na siga. Example:
| ||||||||||||||||||||||
Rana | Yana dawo da tsarin kwanan wata. | ||||||||||||||||||||||
Kwanan wata Ƙara | Yana ƙara ƙayyadadden tazarar lokaci zuwa kwanan wata da aka bayar. Tsarin aiki:
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:
Example:
| ||||||||||||||||||||||
DateDiff | Yana ƙididdige adadin ƙayyadaddun tazara tsakanin kwanakin da aka bayar. Example:
| ||||||||||||||||||||||
Rana | Yana 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. | ||||||||||||||||||||||
hour | Yana 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. | ||||||||||||||||||||||
InStr | Yana ɗ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:
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. | ||||||||||||||||||||||
Int | Yana dawo da sashin lamba na lambar da aka bayar. Example: Int (5.79) mayar da sakamakon 5. | ||||||||||||||||||||||
Isdate | dawo Gaskiyaidan darajar da aka bayar kwanan wata ne, ko arya – idan kwanan wata ba. Example:
| ||||||||||||||||||||||
Kuskure | dawo Gaskiyaidan darajar da aka bayar kuskure ne, ko arya – idan ba kuskure ba. | ||||||||||||||||||||||
Yana Bacewa | An 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. | ||||||||||||||||||||||
IsNumeric | dawo Gaskiyaidan darajar da aka bayar za a iya bi da ita azaman lamba, in ba haka ba ta dawo arya. | ||||||||||||||||||||||
Hagu | Yana dawo da ƙayyadadden adadin haruffa daga farkon kirtan da aka bayar. Tsarin aikin shine kamar haka:
inda line shine asalin kirtani, kuma tsawon shine adadin haruffan da za a dawo, ana ƙirgawa daga farkon kirtani. Example:
| ||||||||||||||||||||||
Len | Yana dawo da adadin haruffa a cikin kirtani. Example: Len ("abcdej") ya dawo da lamba 7. | ||||||||||||||||||||||
Watan | Yana dawo da lamba daidai da watan da aka bayar kwanan wata. Example: Watan («29/01/2015») dawo da darajar 1. | ||||||||||||||||||||||
Tsakanin | Yana 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:
| ||||||||||||||||||||||
minute | Yana dawo da lamba ɗaya daidai da adadin mintuna a cikin lokacin da aka bayar. Misali: Minti («22:45:15») dawo da darajar 45. | ||||||||||||||||||||||
yanzu | Yana dawo da tsarin kwanan wata da lokaci. | ||||||||||||||||||||||
dama | Yana 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:
| ||||||||||||||||||||||
Na biyu | Yana 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. | ||||||||||||||||||||||
Sqr | Yana dawo da tushen murabba'in ƙima na lamba da aka wuce a cikin gardama. Example:
| ||||||||||||||||||||||
Time | Yana dawo da lokacin tsarin yanzu. | ||||||||||||||||||||||
Ubound | Yana 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. | ||||||||||||||||||||||
shekara | Yana 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.