Contents
Akwai yanayi lokacin da ake buƙatar shirin VBA don aiwatar da saiti iri ɗaya sau da yawa a jere (wato maimaita wannan toshe lambar sau da yawa). Ana iya yin wannan ta amfani da madaukai VBA.
VBA madaukai sun haɗa da:
Na gaba, za mu kalli kowane ɗayan waɗannan zagayowar.
Don Ma'aikacin Loop a Visual Basic
Tsarin madaidaicin ma'aikacin The a cikin Visual Basic za a iya shirya shi a ɗayan nau'i biyu: azaman madauki Don… Na gaba ko a matsayin madauki Ga Kowanne.
Zagayowar “Don… na gaba”
Tsarin Don… Na gaba yana amfani da maɓalli wanda a jere yana ɗaukar ƙima daga kewayon da aka bayar. Tare da kowane canji na ƙimar ma'auni, ana yin ayyukan da aka haɗa a cikin jikin zagayowar. Wannan yana da sauƙin fahimta daga misali mai sauƙi:
Domin i = 1 Zuwa 10 Total = Total + iArray(i) Na gaba i
A cikin wannan madauki mai sauƙi Don… Na gaba ana amfani da m i, wanda a jere yana ɗaukar ƙimar 1, 2, 3, … 10, kuma ga kowane ɗayan waɗannan dabi'u, ana aiwatar da lambar VBA a cikin madauki. Don haka, wannan madauki yana taƙaita abubuwan da ke cikin tsararru. iArray a m Jimlar.
A cikin misalin da ke sama, ba a ƙayyadadden ƙarar madauki ba, don haka don ƙara mai canzawa i daga 1 zuwa 10, tsoho shine haɓakawa 1Koyaya, a wasu lokuta ya zama dole a yi amfani da ƙimar haɓaka daban-daban don madauki. Ana iya yin wannan ta amfani da kalmar maɓalli Matakikamar yadda aka nuna a cikin misali mai sauƙi mai zuwa.
Don d = 0 zuwa 10 Mataki 0.1 dTotal = dTotal + d Na gaba d
Tun da a cikin misalin da ke sama, an saita matakin ƙara daidai da 0.1, sannan mai canzawa dTotal ga kowane maimaita sake zagayowar yana ɗaukar ƙimar 0.0, 0.1, 0.2, 0.3,… 9.9, 10.0.
Don ƙayyade matakin madauki a cikin VBA, zaku iya amfani da ƙimar mara kyau, misali, kamar wannan:
Don i = 10 Zuwa 1 Mataki -1 iArray(i) = i Na gaba i
Anan karuwar shine -1, don haka mai canzawa i tare da kowane maimaita zagayowar yana ɗaukar ƙimar 10, 9, 8,… 1.
Maɗaukaki "Don Kowa"
Tsarin Ga Kowanne kama da zagayowar Don… Na gaba, amma maimakon maimaita kan jerin dabi'u don ma'aunin ƙima, madauki Ga Kowanne yana aiwatar da saitin ayyuka don kowane abu a cikin ƙayyadadden rukunin abubuwa. A cikin misali mai zuwa, yin amfani da madauki Ga Kowanne Yana ƙididdige duk zanen gado a cikin littafin aikin Excel na yanzu:
Dim wSheet A Matsayin Taswirar Aiki Ga Kowane wSheet a cikin Tassoshin Aiki MsgBox "Найден лист:" & wSheet.Sunan wSheet na gaba
Madauki katse bayanin "Fita don"
Operator Fita Domin amfani da su katse zagayowar. Da zarar an ci karo da wannan bayanin a cikin lambar, shirin ya ƙare aiwatar da madauki kuma ya ci gaba zuwa aiwatar da bayanan da ke cikin lambar nan da nan bayan wannan madauki. Ana iya amfani da wannan, misali, don nemo takamaiman ƙima a cikin tsararru. Don yin wannan, ta amfani da madauki, kowane nau'i na tsararru ana duba su. Da zarar an samo abin da ake buƙata, babu buƙatar duba ta sauran - an katse sake zagayowar.
Aikace-aikacen mai aiki Fita Domin aka nuna a cikin misali mai zuwa. Anan madauki yana maimaita shigarwar tsararru sama da 100 kuma yana kwatanta kowanne tare da ƙimar ma'auni dVal… Idan an sami ashana, to an ƙare madauki:
Domin i = 1 To 100 Idan dValues(i) = dVal Sai IndexVal = i Fita Don Ƙarshe Idan Na gaba i
The Do while Loop in Visual Basic
Tsarin Yi lokaci yana aiwatar da toshe na lamba idan dai an cika ƙayyadadden yanayin. Mai zuwa shine misalin hanya sub, wanda ake amfani da madauki Yi lokaci Lambobin Fibonacci waɗanda ba su wuce 1000 ana nunawa a jere ba:
'Tsarin hanya yana fitar da lambobin Fibonacci waɗanda ba su wuce 1000 Sub Fibonacci () Dim i As Integer 'counter don nuna matsayi na kashi a cikin jerin Dim iFib As Integer' yana adana darajar halin yanzu na jerin Dim iFib_Next Kamar yadda Integer' ke adana darajar gaba. na jerin Dim iStep As Integer 'yana adana girman girman haɓaka na gaba' fara masu canji i da iFib_Next i = 1 iFib_Next = 0 'Yi yayin da madauki zai aiwatar har sai darajar' lambar Fibonacci na yanzu ya fi 1000 Do Yayin iFib_Next Idan iFib_Next Idan <1000 1 Sa'an nan 'harka na musamman don kashi na farko iStep = 1 iFib = 0 In ba haka ba' ajiye girman girman na gaba kafin a sake rubutawa 'ƙimar halin yanzu na jerin iStep = iFib iFib = iFib_Next End Idan' buga lambar Fibonacci na yanzu a shafi na A na takardar aiki mai aiki 'a cikin jere tare da index i Cells(i , 1) .Value = iFib' ƙididdige lambar Fibonacci na gaba kuma ƙara ma'aunin matsayi ta 1 iFib_Next = iFib + iStep i = i + 1 Madauki Ƙarshen Sub.
A cikin misalin da aka bayar, yanayin iFib_Na gaba <1000 duba a farkon madauki. Saboda haka, idan darajar farko iFib_Na gaba Idan akwai fiye da 1000, to ba za a taɓa aiwatar da madauki ba.
Wata hanyar aiwatar da madauki Yi lokaci - sanya yanayin ba a farkon ba, amma a ƙarshen madauki. A wannan yanayin, za a aiwatar da madauki aƙalla sau ɗaya, ba tare da la'akari da ko an cika yanayin ba.
Tsarin tsari, irin wannan sake zagayowar Yi lokaci tare da yanayin da za a duba a ƙarshe zai yi kama da haka:
Yi ... Madauki Yayin iFib_Na gaba <1000
Цикл «Yi Har» в Visual Basic
Tsarin Yi Har yayi kama da zagayowar Yi lokaci: ana aiwatar da toshe lambar a jikin madauki akai-akai har sai an cika ƙayyadadden yanayin (sakamakon yanayin yanayin shine Gaskiya). A cikin hanya ta gaba sub amfani da sake zagayowar Yi Har maido da ƙima daga duk sel a cikin ginshiƙi A takardar aiki har sai ginshiƙi ya ci karo da tantanin halitta mara komai:
iRow = 1 Yi Har sai IsEmpty (Cells (iRow, 1)) 'An adana darajar tantanin halitta a cikin tsararrun dCellValues dCellValues(iRow) = Kwayoyin (iRow, 1) .Value iRow = iRow + 1 Loop
A cikin misalin da ke sama, yanayin IsEmpty (Cells (iRow, 1)) located a farkon tsarin Yi Har, don haka za a aiwatar da madauki aƙalla sau ɗaya idan cell ɗin farko da aka ɗauka ba komai bane.
Koyaya, kamar yadda aka nuna a cikin misalan madauki Yi lokaci, a wasu yanayi ya zama dole a aiwatar da madauki aƙalla sau ɗaya, ba tare da la'akari da sakamakon farko na yanayin yanayin ba. A wannan yanayin, ya kamata a sanya yanayin yanayin a ƙarshen madauki, kamar haka:
Yi ... Maɗaukaki Har Zuwa Baci (Cells (iRow, 1))