Basebelisi ba VBA le Mesebetsi e Hahiloeng

Tse ka Hare

Lipolelo tsa Excel VBA
Mesebetsi e hahiloeng

Lipolelo tsa Excel VBA

Ha u ngola khoutu ea VBA ho Excel, ho sebelisoa li-operators tse hahelletsoeng mohatong o mong le o mong. Basebelisi bana ba arotsoe ka lipalo, likhoele, papiso le li-logic operators. Ka mor'a moo, re tla sheba sehlopha ka seng sa basebetsi ka botlalo.

Basebelisi ba Lipalo

Basebelisi ba lipalo ba VBA ba ka sehloohong ba thathamisitsoe tafoleng e ka tlase.

Kholomo e ka ho le letona ea tafole e bonts'a moetapele oa kamehla ha ho se na masaka. Ka ho kenya li-parentheses polelong, u ka fetola tatellano eo lipolelo tsa VBA li etsoang ka eona kamoo u lakatsang kateng.

Operator	Action	ntho ea bohlokoa ho (1 - holimo; 5 - tlase)
^	mopalami wa exponentiation	1
*	mosebeletsi wa katiso	2
/	mosebeletsi oa karohano	2
Karohano ntle le ho sala - e khutlisa sephetho sa ho arola linomoro tse peli ntle le ho sala. Ka mohlala, 74 e tla khutlisa sephetho 1	3
mphe sebete e le	Opareitara ya Modulo (salla) – e kgutlisa se setseng ka mora ho arola dinomoro tse pedi. Ka mohlala, 8 Khahlanong le 3 e tla khutlisa sephetho 2.	4
+	Mokhanni oa tlatsetso	5
-	mosebeletsi wa ho ntsha	5

Basebelisi ba likhoele

Mokhanni oa likhoele oa mantlha ho Excel VBA ke opareitara ea concatenation & (kopanya):

Operator	Action
&	motsamaisi oa concatenation. Ka mohlala, polelo "A" le "B" e tla khutlisa sephetho AB.

Bapisa Basebelisi

Basebelisi ba papiso ba sebelisoa ho bapisa linomoro kapa likhoele tse peli le ho khutlisa boleng ba mofuta oa boolean boolean (Nnete kapa Bohata). Basebelisi ba ka sehloohong ba papiso ea Excel VBA ba thathamisitsoe tafoleng ena:

Operator	Action
=	Ka ho lekana
<>	Ha e lekane
<	Nyane
>	Lintlha tse ling
<=	Ka tlase ho kapa ho lekana
>=	E kholo hofeta kapa ho lekana

Basebelisi ba utloahalang

Basebelisi ba bohlale, joalo ka lipapiso, ba khutlisa boleng ba mofuta oa boolean boolean (Nnete kapa Bohata). Basebelisi ba ka sehloohong ba bohlale ba Excel VBA ba thathamisitsoe tafoleng e ka tlase:

Operator	Action
le	ts'ebetso e kopanetsoeng, mosebeletsi ea utloahalang И. Ka mohlala, polelo A le B e tla khutla Ke 'nete hore, haeba A и B bobedi di a lekana Ke 'nete hore, ho seng joalo khutla ba bohata.
Or	Ts'ebetso ea disjunction, opareitara e utloahalang OR. Ka mohlala, polelo A kapa B e tla khutla Ke 'nete hore, haeba A or B baa lekana Ke 'nete hore, ’me ke tla khutla ba bohata, haeba A и B bobedi di a lekana ba bohata.
eseng	Ts'ebetso ea ho nyenyefatsa, mosebeletsi ea nang le kelello SE. Ka mohlala, polelo Eseng A e tla khutla Ke 'nete hore, haeba A ka ho lekana ba bohata, kapa khutla ba bohata, haeba A ka ho lekana Ke 'nete hore.

Tafole e ka holimo ha e thathamise bohle ba sebetsang hantle ba fumanehang VBA. Lenane le felletseng la basebelisi ba nang le kelello le ka fumanoa Setsing sa Visual Basic Developer.

Mesebetsi e hahiloeng

Ho na le mesebetsi e mengata e hahelletsoeng ka har'a VBA e ka sebelisoang ha u ngola khoutu. Ka tlaase mona ho thathamisitsoe tse ling tsa tse sebelisoang haholo:

mosebetsi Action

Abs

E khutlisa boleng bo felletseng ba nomoro e fanoeng.

mohlala:

Abs(-20) e khutlisa boleng ba 20;
Abs(20) e khutlisa boleng ba 20.

Moq

E khutlisa litlhaku tsa ANSI tse tsamaellanang le boleng ba linomoro tsa paramethara.

mohlala:

Chr(10) e khutlisa khefu ea mola;
Chr(97) e kgutlisa motho a.

letsatsi E khutlisa letsatsi la sistimi ea hajoale.

DateAdd

E eketsa nako e behiloeng ho letsatsi le fanoeng. Syntax ea mosebetsi:

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

Khang e kae nako e khetha mofuta oa nako ea nako e kenyellelitsoeng ho e fanoeng letsatsi ka chelete e boletsoeng khang palo.

Ho ngangisana nako e ka nka e 'ngoe ea litekanyetso tse latelang:

Nako	latela boleng ba
yyyy	selemo
q	kotara
m	Khoeli
y	letsatsi la selemo
d	Ka letsatsi le
w	letsatsi la beke
ww	beke
h	hora
n	motsotso
s	ea bobeli

mohlala:

DateAdd(«d», 32, «01/01/2015») e eketsa matsatsi a 32 ho letsatsi la 01/01/2015 mme kahoo e khutlisa letsatsi la 02/02/2015.
DateAdd(«ww», 36, «01/01/2015») e eketsa libeke tse 36 ho letsatsi la 01/01/2015 mme e khutlisa letsatsi la 09/09/2015.

DateDiff

E lekanya palo ea linako tse boletsoeng pakeng tsa matsatsi a mabeli a fanoeng.

mohlala:

DateDiff(«d», «01/01/2015», «02/02/2015») e bala palo ea matsatsi pakeng tsa 01/01/2015 le 02/02/2015, e khutlisetsa 32.
DateDiff(«ww», «01/01/2015», «03/03/2016») e bala palo ea libeke pakeng tsa 01/01/2015 le 03/03/2016, e khutlisetsa 61.

Day

E khutlisetsa palo e tsamaisanang le letsatsi la khoeli letsatsing le fanoeng.

mohlala: Letsatsi(«29/01/2015») e khutlisa nomoro ea 29.

hora

E khutlisa nomoro e tsamaisanang le palo ea lihora ka nako e fanoeng.

mohlala: Hora(«22:45:00») e khutlisa nomoro ea 22.

InStr

E nka palo e felletseng le likhoele tse peli e le likhang. E khutlisa sebaka sa ho hlaha ha khoele ea bobeli ka har'a ea pele, ho qala patlo sebakeng se fanoeng ke palo e felletseng.

mohlala:

InStr(1, “Lentsoe la ho batla ke lena”, “lentsoe”) e khutlisa nomoro ea 13.
InStr(14, "Lentsoe la ho batlisisa ke lena, 'me lentsoe le leng ke lena", "lentsoe") e khutlisa nomoro ea 38.

Hlokomela: Khang ea linomoro e ka 'na ea se ke ea hlalosoa, moo ho batla ho qalang ho tloha tlhakung ea pele ea khoele e boletsoeng khang ea bobeli ea mosebetsi.

Int

E khutlisa karolo e felletseng ea nomoro e fanoeng.

mohlala: Int(5.79) e khutlisetsa sephetho 5.

Idate

Returns Ke 'nete horehaeba boleng bo fanoeng ke letsatsi, kapa ba bohata – haeba letsatsi le se teng.

mohlala:

Letsatsi(«01/01/2015») kgutlang Ke 'nete hore;
Letsatsi(100) kgutlang ba bohata.

IsError Returns Ke 'nete horehaeba boleng bo fanoeng ke phoso, kapa ba bohata – haeba e se phoso.

Ha e eo Lebitso la phehisano ea boikhethelo le fetisitsoe e le khang mosebetsing. Ha e eo kgutlang Ke 'nete horehaeba ha ho boleng bo fetisitsoeng bakeng sa khang ea ts'ebetso e potsoeng.

Ke Numerec Returns Ke 'nete horehaeba boleng bo fanoeng bo ka nkoa e le palo, ho seng joalo boa khutla ba bohata.

Ka ho le letšehali

E khutlisa palo e boletsoeng ea litlhaku ho tloha qalong ea khoele e fanoeng. Syntax ea ts'ebetso e tjena:

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

moo moleng oa ke khoele ea pele, le bolelele ke palo ea litlhaku tse lokelang ho khutla, ho bala ho tloha qalong ea khoele.

mohlala:

Left(“abvgdejziklmn”, 4) e khutlisetsa khoele "abcg";
Left(“abvgdejziklmn”, 1) e kgutlisa kgwele “a”.

Len

E khutlisa palo ea litlhaku ka khoele.

mohlala: Len ("abcdej") e khutlisa nomoro ea 7.

khoeli

E khutlisa nomoro e tsamaisanang le khoeli ea letsatsi le fanoeng.

mohlala: Kgwedi(«29/01/2015») e khutlisa boleng ba 1.

Bohareng

E khutlisa palo e boletsoeng ea litlhaku ho tloha bohareng ba khoele e fanoeng. Syntax ea mosebetsi:

Mahareng(moleng oa, qale, bolelele)

moo moleng oa ke khoele ea pele qale - boemo ba ho qala ha khoele e tla ntšoa; bolelele ke palo ea litlhaku tse tla ntšoa.

mohlala:

Mahareng(“abvgdejziklmn”, 4, 5) e khutlisetsa khoele "kae";
Mahareng(“abvgdejziklmn”, 10, 2) e khutlisetsa khoele “cl”.

Minute E khutlisetsa palo e tsamaisanang le palo ea metsotso nakong e fanoeng. Mohlala: Motsotso («22:45:15») e khutlisa boleng ba 45.

hona joale E khutlisa letsatsi le nako ea sistimi ea hajoale.

Right

E khutlisa palo e boletsoeng ea litlhaku ho tloha qetellong ea khoele e fanoeng. Syntax ea mosebetsi:

Ka ho le letona(moleng oa, bolelele)

Where moleng oa ke khoele ea pele, le bolelele ke palo ea litlhaku tse tla ntšoa, ho baloa ho tloha pheletsong ea khoele e fanoeng.

mohlala:

Ka ho le letona(«abvgdezhziklmn», 4) e khutlisetsa khoele "clmn";
Ka ho le letona(«abvgdezhziklmn», 1) e khutlisetsa khoele “n”.

ea bobeli

E khutlisa nomoro e tsamaellanang le palo ea metsotsoana nakong e fanoeng.

mohlala: Ea bobeli («22:45:15») e khutlisa boleng ba 15.

Sqr

E khutlisa "square root" ea boleng bo fetisitsoeng ho ngangisano.

mohlala:

Sqr(4) e khutlisa boleng ba 2;
Sqr(16) e khutlisa boleng ba 4.

Time E khutlisa nako ea sistimi ea hajoale.

Ubound

E khutlisa mongolo o ka holimo oa boholo bo boletsoeng.

Hlokomela: Bakeng sa li-arrays tse ngata, khang ea boikhethelo e ka ba index ea hore na ho khutlisetsoa tekanyo efe. Haeba e sa hlalosoa, kamehla ke 1.

selemo E khutlisa nomoro e tsamaellanang le selemo sa letsatsi le fanoeng. Mohlala: Selemo(«29/01/2015») e khutlisa boleng ba 2015.

Lethathamo lena le kenyelletsa feela khetho ea mesebetsi e atisang ho sebelisoa e hahelletsoeng ka hare ho Excel Visual Basic. Lenane le felletseng la mesebetsi ea VBA e fumanehang bakeng sa ho sebelisoa ho Excel macros le ka fumanoa Setsing sa Visual Basic Developer.

Lipolelo tsa Excel VBA

Basebelisi ba Lipalo

Basebelisi ba likhoele

Bapisa Basebelisi

Basebelisi ba utloahalang

Mesebetsi e hahiloeng

Leave a Reply