Qalabka barnaamijka VBA^{(VBA programming)} ee ka shaqeeya ku dhawaad ​​dhammaan alaabta Microsoft Office waa mid ka mid ah qalabka ugu awoodda badan ee qof kastaa u isticmaali karo si uu u wanaajiyo isticmaalkooda alaabtaas.

Hagahan VBA^(VBA) - da ee bilawga ah ayaa ku tusi doona sida loogu daro menu^(Developer) -ka soo-saare ee codsigaaga Xafiiska^(Office) , sida loo galo daaqada tifaftiraha VBA , iyo sida weedhaha ^(VBA)VBA -da aasaasiga ah u shaqeeyaan si aad u bilowdo isticmaalka VBA ee Excel , Word , Powerpoint , Muuqaalka^(Outlook) , iyo OneNote .

Hagaha VBA^(VBA) -da waxa uu isticmaalayaa noocii ugu dambeeyay ee alaabta Microsoft Office . Haddii aad haysato nooc hore waxaad arki kartaa xoogaa farqi ah oo ka mid ah sawirada shaashadda.

Sida Loo Awood & Isticmaalo Tifaftiraha VBA^{(How To Enable & Use The VBA Editor)}

Mid kasta oo ka mid ah alaabada Xafiiska^(Office) ee loo isticmaalo hagahan, waxa laga yaabaa inaad dareento inaadan haysanin menu -ka soo-saare ee la tixraacay. ^(Developer)Liiska horumariyaha waxa lagu heli karaa oo keliya Excel , Word , Outlook , iyo Powerpoint . OneNote ma bixiso qalab lagu daabaco koodka VBA gudaha arjiga, laakiin waxaad weli tixraaci kartaa OneNote API si aad ula falgasho OneNote^(OneNote) barnaamijyada kale ee Xafiiska^(Office) . 

Waxaad ku baran doontaa sida tan loo sameeyo hagahayaga Sare ee VBA^{(Advanced VBA)} ee soo socda .

• Si aad awood ugu yeelatid menu-ka horumariyaha^(Developer) ee badeecad kasta oo xafiis, dooro menu-ka Faylka^(File) , oo ka dooro Options menu navigation ee bidix. 
• Waxaad arki doontaa liiska Options pop-up ah. Ka dooro Customize Ribbon ka menu navigation ee bidix.

Liistada bidix waxa ku jira dhammaan menu-yada la heli karo iyo amarrada menu ee laga heli karo arjiga Xafiiskaas . ^(Office)Liiska midigta ku yaal waa kuwa hadda la heli karo ama shaqeeya.

• Waa inaad ku aragto horumariyaha^(Developer) liiska dhanka midig, laakiin lama hawlgelin doono. Kaliya^(Just) dooro sanduuqa hubinta si aad u dhaqaajiso menu horumariyaha .^(Developer)

• Haddii aadan arkin horumariyaha^(Developer) oo laga heli karo dhanka midig, ka dibna u beddel bidixda Dooro amarrada^{(Choose commands)} hoos u dhaca una gudub dhammaan amarrada^{(All Commands)} . Ka hel horumariyaha^(Developer) liiska oo dooro Add>> bartamaha si aad ugu darto menu-ka Ribbon -ka .
• Dooro OK marka aad dhammayso
• Marka liiska horumariyaha^(Developer) uu firfircoon yahay, waxaad dib ugu noqon kartaa daaqada codsigaaga oo ka dooro horumariye^(Developer) liiska sare. 
• Kadibna dooro View Code ka kooxda kantaroolka^(Controls) ee ribbon si aad u furto daaqada tafatiraha VBA .

• Tani waxay furi doontaa daaqada tifaftiraha VBA halkaasoo aad ku qori karto koodka aad ku baran doonto qaybaha soo socda.

• Isku day inaad ku darto menu^(Developer) -ka soo- saare ee dhawr ka mid ah codsiyada Xafiiska^(Office) ee aad isticmaasho maalin kasta. Markaad ku raaxaysato furitaanka daaqada tafatiraha VBA , sii wad qaybta xigta ee hagahan.

Talooyinka guud ee barnaamijka VBA ee bilowga ah^{(General VBA Programming Tips for Beginners)}

Waxaad ogaan doontaa marka tifaftiruhu furmo VBA -da, ikhtiyaarrada hagidda ee guddida bidix waxay u ekaan doonaan mid ka duwan hal codsi oo xafiiska kan kale ah.^(Office)

Tani waa sababta oo ah walxaha la heli karo ee aad ku dhejin karto code VBA waxay ku xiran yihiin walxaha ku jira codsiga. Tusaale ahaan, gudaha Excel , waxaad ku dari kartaa koodka VBA buugga shaqada ama walxaha xaashida. Gudaha Word , waxaad ku dari kartaa koodka VBA dukumentiyada. Gudaha Powerpoint , kaliya modules-yada.

Markaa, ha la yaabin menu-yada kala duwan. Qaab dhismeedka iyo ereyga VBA -da waa isku mid dhammaan codsiyada. Farqiga kaliya ayaa ah shayada aad tixraaci karto iyo ficilada aad ku samayn karto shayadaas iyada oo loo marayo code VBA .

Kahor inta aynaan u dhex galin walxaha iyo ficilada kala duwan ee aad ku qaadi karto iyaga oo isticmaalaya code VBA , aynu marka hore eegno qaab dhismeedka VBA ee ugu caansan iyo syntax aad isticmaali karto marka aad qorto code VBA .

Halkee La Dhigaa Koodhka VBA^{(Where To Put VBA Code)}

Markaad ku jirto tifaftiraha VBA , waxaad u baahan tahay inaad isticmaasho labada sanduuq ee hoos u dhaca ee ku yaal xagga sare ee daaqada tafatirka si aad u doorato shayga aad rabto inaad ku dhejiso koodka, iyo marka aad rabto in koodku socdo.

Tusaale ahaan, gudaha Excel , haddii aad dooratid Xaashida Shaqada^(Worksheet) oo Dhaqdhaqaaq^(Activate) , koodka ayaa shaqayn doona mar kasta oo xaashida shaqada la furo. 

Ficilada kale ee xaashida shaqada ee aad isticmaali karto si aad u kiciso koodka VBA -gaaga waxaa ka mid ah marka xaashida shaqadu is beddesho, marka ay xidhan tahay (dami), marka xisaabinta xaashida shaqada la wado, iyo in ka badan.

Markaad ku darto koodka VBA ee tifaftiraha, had iyo jeer iska hubi inaad dhigato koodka VBA shayga oo aad isticmaasho tallaabada saxda ah ee aad rabto inaad isticmaasho si aad u kiciso koodkaas.

VBA HADDII Bayaanada^{(VBA IF Statements)}

Oraahda IF waxay u shaqeysaa VBA sida ay ugu shaqeyso luqadaha barnaamijyada kale.

Qaybta hore ee bayaanka IF waxay eegaysaa in xaalad ama shuruudo dejisan ay run yihiin. Shuruudahan waxa ku biiri kara AND ama AMA hawlwadeen si ay isugu xidhaan.

Hal tusaale ayaa noqon kara in la hubiyo in darajada xaashida daabacan tahay uu ka sareeyo ama ka hooseeyo darajada “ku-gudbida”, iyo u meelaynta ka gudubka ama heerka guuldarada unug kale.

If Cells(2, 2) > 75 Then Cells(2, 3) = “Pass” Else Cells(2, 3) = “Fail”

Haddii aadan rabin dhammaan bayaannada hal sadar, waxaad u kala qaybin kartaa sadar badan adigoo ku daraya calaamad "_" dhammaadka sadarrada.

If Cells(2, 2) > 75 Then _
Cells(2, 3) = “Pass”  Else _
Cells(2, 3) = “Fail”

Isticmaalka farsamadan waxay inta badan ka dhigi kartaa kood si aad u fudud in la akhriyo lagana saaro.

VBA ee Loops soo socda^{(VBA For Next Loops)}

HADDII ay odhaahyadu ku fiican yihiin isbarbardhigga hal, sida tusaalaha sare ee eegaya hal unug. Laakiin ka waran haddii aad rabto in aad dhex mariso unugyo kala duwan oo dhan oo aad samayso isla HADDII bayaan mid kasta?

Xaaladdan oo kale waxaad u baahan doontaa loop  FOR .

Si aad tan u samayso, waxaad u baahan doontaa inaad isticmaasho dhererka kala duwan, oo ku dhufo dhererkaas tirada safafka ay ku jiraan xogta.

Si tan loo sameeyo, waxaad u baahan tahay inaad qeexdo kala duwanaanshaha iyo doorsoomayaasha unugyada, oo aad dhex geliso iyaga. Waxaad sidoo kale u baahan doontaa inaad qeexdo miiska si aad natiijada ugu soo saarto safka ku habboon. Markaa koodhkaaga VBA ayaa marka hore lahaan doona khadkan.

Dim rng Sida Range, unugga Sida Range ^{(Dim rng As Range, cell As Range)}
Dim rowCounter sida Iskudarka^{(Dim rowCounter as Integer)}

U qeex cabbirka baaxadda sida soo socota. 

Set rng = Range(“B2:B7”)
rowCounter = 2

Ugu dambeyntii, waxaad abuuri kartaa loop-kaaga FOR si aad uga gudubto unug kasta oo xadkaas ku yaal oo aad sameyso isbarbardhigga.

For Each cell In rng
   If cell.Value > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"

   rowCounter = rowCounter + 1

Next cell

Marka qoraalka VBA^{(VBA script)} -da la shaqeeyo, waxaad natiijada ku arki doontaa xaashida saxda ah.

VBA Halka Loops^{(VBA While Loops)}

A Halka Loop uu sidoo kale wareego iyada oo loo marayo tibaaxo taxane ah, si la mid ah FOR loop, laakiin xaaladda si loo sii wado waa xaalad weli jirta oo run ah.

Tusaale ahaan, waxaad ku qori kartaa isla FOR loop-ka sare, sida WHILE loop, adigoo si fudud u isticmaalaya doorsoomaha safka sida soo socota.

While rowCounter < rng.Count + 2
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   rowCounter = rowCounter + 1
Wend

Fiiro gaar ah: xadka joojinta rng.Count + 2 ayaa loo baahan yahay sababtoo ah miiska safku wuxuu ka bilaabmaa 2 wuxuuna u baahan yahay inuu ku dhamaado safka 7 halkaasoo xogtu ku dhamaato. Si kastaba ha ahaatee, tirinta kala duwanaanshaha (B2:B7) waa 6 kaliya, halka loop-ku uu dhamaan doono oo kaliya marka miiska uu ka WAYN^(GREATER) yahay miiska - markaa qiimaha safka ugu dambeeya wuxuu u baahan yahay inuu ahaado 8 (ama rng.Count + 2).

Waxa kale oo aad u dejisan kartaa halka loop sida soo socota:

While rowCounter <= rng.Count + 1

Waxaad kaliya ku kordhin kartaa tirada tirada (6) 1, sababtoo ah marka doorsoomaha Counter-ka uu gaaro dhamaadka xogta ( safka 7), wareeggu wuu dhammeyn karaa.

VBA Samee inta Samee oo Samee Ilaa Loops^{(VBA Do While and Do Until Loops)}

Samee oo Samee Ilaa inta wareegyadu ay ku dhow yihiin inay la mid yihiin halka wareegyada, laakiin shaqeeyaan wax yar ka duwan.

• Halka Loop^{(While Loop)} - ku uu hubinayo in xaaladdu run tahay bilowga wareegga.
• Do-While Loop wuxuu hubiyaa in xaaladdu run tahay ka dib marka la fuliyo weedhaha wareegga.
• Do-Ilaa Loop-ku^{(Do-Until Loop)} wuxuu hubiyaa in xaaladdu weli been tahay ka dib marka la fuliyo wareegga.

Xaaladdan oo kale waxaad dib ugu qori doontaa halka loop-ka sare sida soo socota, sida loop-Do-While.

Do
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   
   rowCounter = rowCounter + 1
Loop While rowCounter < rng.Count + 2

Xaaladdan oo kale caqli-galku aad uma beddelo, laakiin haddii aad rabto inaad hubiso in isbarbardhigga macquulka ahi uu dhaco ka dib markii dhammaan weedhaha la ordo (oo u oggolaanaya dhammaantood inay ordaan wax kasta oo ay tahay ugu yaraan hal mar), ka dibna Do-While ama Samee-ilaa loopku yahay doorashada saxda ah.

VBA Dooro Bayaanada Kiiska^{(VBA Select Case Statements)}

Nooca ugu dambeeya ee bayaan macquul ah oo aad u baahan doonto inaad fahanto si aad u bilowdo habaynta koodka VBA -gaaga waa weedha ^(VBA)Xulasho^{(Select Case)} .

Marka la eego tusaalaha sare, aynu nidhaahno waxaad doonaysaa in aad hesho hab shahaado-bixineed oo aan ahayn mid guul-darraystay. Taa beddelkeeda, waxaad doonaysaa inaad ku meelayso darajada xarafka A ilaa F.

Waxaad tan ku samayn kartaa bayaanka soo socda ee Doorashada^{(Select Case)} :

For Each cell In rng
   
   Select Case cell

   Case 95 To 100
      Cells(rowCounter, 3) = "A"
   Case 85 To 94
      Cells(rowCounter, 3) = "B"
   Case 75 To 84
      Cells(rowCounter, 3) = "C"
   Case 65 To 74
      Cells(rowCounter, 3) = "D"
   Case 0 To 64
      Cells(rowCounter, 3) = "F"
   End Select

   rowCounter = rowCounter + 1

Next cell

Xaashida faafinta ee ka dhalatay ka dib markii qoraalka VBA -da socdaa wuxuu u eg yahay kan hoose.

Hadda waxaad taqaanaa wax kasta oo aad u baahan tahay inaad ogaato si aad u bilowdo isticmaalka VBA ee codsiyadaada Microsoft Office .

The Best VBA Guide (For Beginners) You’ll Ever Need

The VBA programming platform that runs throughout nearly all Microsoft Office products is one of the most powerful tools anyone can use to enhance their use of those products.

This VBA guide for beginners will show you how to add the Developer menu to your Office application, how to enter the VBA editor window, and how basic VBA statements and loops work so that you can get started using VBA in Excel, Word, Powerpoint, Outlook, and OneNote.

This VBA guide uses the latest version of Microsoft Office products. If you have an earlier version you may see some slight differences from the screenshots.

How To Enable & Use The VBA Editor

In any of the Office products used in this guide, you may notice that you don’t have the referenced Developer menu. The developer menu is only available in Excel, Word, Outlook, and Powerpoint. OneNote doesn’t offer a tool to edit VBA code from inside the application, but you can still reference the OneNote API to interact with OneNote from other Office programs. 

You’ll learn how to do this in our upcoming Advanced VBA guide.

• To enable the Developer menu in any office product, select the File menu, and select Options from the left navigation menu. 
• You’ll see an Options menu pop-up. Select Customize Ribbon from the left navigation menu.

The left list includes all available menus and menu commands available in that Office application. The list on the right are those that are currently available or activated.

• You should see Developer in the list on the right, but it will not be activated. Just select the checkbox to activate the Developer menu.

• If you don’t see Developer available on the right, then change the left Choose commands from dropdown to All Commands. Find Developer from the list and select Add>> in the center to add that menu to the Ribbon.
• Select OK when you’re done.
• Once the Developer menu is active, you can go back to your main application window and select Developer from the top menu. 
• Then select View Code from the Controls group in the ribbon to open the VBA editor window.

• This will open the VBA editor window where you can type the code you’ll learn in the next few sections.

• Try adding the Developer menu to a few of the Office applications you use every day. Once you’re comfortable opening the VBA editor window, continue on to the next section of this guide.

General VBA Programming Tips for Beginners

You’ll notice when the VBA editor opens, the navigation options in the left panel look different from one Office application to the other.

This is because the available objects where you can place VBA code depend on what objects there are in the application. For example, in Excel, you can add VBA code to workbook or sheet objects. In Word, you can add VBA code to documents. In Powerpoint, only to modules.

So, don’t be surprised by the different menus. The structure and syntax of the VBA code is the same across all applications. The only difference are the objects you can reference and the actions you can take on those objects through the VBA code.

Before we dive into the different objects and actions you can take on them via VBA code, let’s first look at the most common VBA structure and syntax you can use when you write VBA code.

Where To Put VBA Code

When you’re in the VBA editor, you need to use the two dropdown boxes at the top of the editing window to choose which object you want to attach the code to, and when you want the code to run.

For example, in Excel, if you choose Worksheet and Activate, the code will run whenever the worksheet is opened. 

Other worksheet actions you can use to trigger your VBA code include when the worksheet changes, when it’s closed (deactivated), when the worksheet calculation is run, and more.

When you add VBA code in the editor, always make sure to place your VBA code on the object and using the correct action that you want to use to trigger that code.

VBA IF Statements

An IF statement works in VBA just like it works in any other programming language.

The first part of the IF statement looks at whether a condition or set of conditions is true. These conditions can be joined by an AND or OR operator to link them together.

One example would be to check if a grade in a spreadsheet is above or below a “passing” grade, and assigning the pass or fail status to another cell.

If Cells(2, 2) > 75 Then Cells(2, 3) = “Pass” Else Cells(2, 3) = “Fail”

If you don’t want the entire statement on a single line, you can split it into multiple lines by adding a “_” symbol at the end of the lines.

If Cells(2, 2) > 75 Then _
Cells(2, 3) = “Pass”  Else _
Cells(2, 3) = “Fail”

Using this technique can often make code much easier to read and debug.

VBA For Next Loops

IF statements are great for single comparisons, like the example above of looking at a single cell. But what if you want to loop through an entire range of cells and do the same IF statement on each?

In this case you’d need a FOR loop. 

To do this, you’d need to use the length of a range, and loop through that length by the number of rows that contain data.

To do this, you need to define the range and cell variables, and loop through them. You’ll also need to define a counter so you can output the results to the appropriate row. So your VBA code would first have this line.

Dim rng As Range, cell As Range
Dim rowCounter as Integer

Define the range size as follows. 

Set rng = Range(“B2:B7”)
rowCounter = 2

Finally, you can create your FOR loop to step through every cell in that range and do the comparison.

For Each cell In rng
   If cell.Value > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"

   rowCounter = rowCounter + 1

Next cell

Once this VBA script is run, you see the results in the actual spreadsheet.

VBA While Loops

A While Loop also loops through a series of statements, just like the FOR loop, but the condition of the looping to continue is a condition remaining true.

For example, you could write the same FOR loop above, as a WHILE loop, by simply using the rowCounter variable as follows.

While rowCounter < rng.Count + 2
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   rowCounter = rowCounter + 1
Wend

Note: the rng.Count + 2 terminating limit is required because the row counter starts at 2 and needs to end on row 7 where the data ends. However, the count of the range (B2:B7) is only 6, and the While loop will only end once the counter is GREATER than the counter – so the last rowCounter value needs to be 8 (or rng.Count + 2).

You could also set up the While loop as follows:

While rowCounter <= rng.Count + 1

You can only increment the range count (6) by 1, because once the rowCounter variable reaches the end of the data (row 7), the loop can finish.

VBA Do While and Do Until Loops

Do While and Do Until loops are nearly identical to While loops, but work slightly different.

• The While Loop checks whether a condition is true at the beginning of the loop.
• The Do-While Loop checks whether a condition is true after executing the statements in the loop.
• The Do-Until Loop checks whether a condition is still false after executing the loop.

In this case you would rewrite the While loop above as follows, as a Do-While loop.

Do
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   
   rowCounter = rowCounter + 1
Loop While rowCounter < rng.Count + 2

In this case the logic doesn’t change very much, but if you want to make sure the logic comparison takes place after all of the statements are run (allowing them all to run no matter what at least once), then a Do-While or Do-Until loop is the right option.

VBA Select Case Statements

The final type of logical statement you’ll need to understand to start structuring your VBA code are Select Case statements.

Given the example above, let’s say you want to have a grading method that isn’t just pass fail. Instead, you want to assign a letter grade from A through F.

You could do this with the following Select Case statement:

For Each cell In rng
   
   Select Case cell

   Case 95 To 100
      Cells(rowCounter, 3) = "A"
   Case 85 To 94
      Cells(rowCounter, 3) = "B"
   Case 75 To 84
      Cells(rowCounter, 3) = "C"
   Case 65 To 74
      Cells(rowCounter, 3) = "D"
   Case 0 To 64
      Cells(rowCounter, 3) = "F"
   End Select

   rowCounter = rowCounter + 1

Next cell

The resulting spreadsheet after this VBA script runs looks like the one below.

Now you know everything you need to know to start using VBA in your Microsoft Office applications.

Related posts

• Hagaha VBA Sare ee MS Excel

• 13-ka Hab-gaab ee Kiiboodhka ugu Fiican ee Kooxaha Microsoft

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Waa maxay Microsoft Publisher? Hagaha bilowga

• Sida loo hagaajiyo Bookmark aan lagu qeexin qalad gudaha Word

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loogu daro Qoraallada Footnotes ee Word

• Sida loo sameeyo dukumeenti PDF gudaha Microsoft Office

• Hel Xogta Iimaylka Outlook adoo isticmaalaya StatView

• Sida loo lakabka walxaha ee bandhigga PowerPoint

• Faylka Xogta Muuqaalka Lama Geli karo: 4 Hagaajin La Isku Dayo

• Sida loo isticmaalo Flash Fill ee Excel

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida Loo nadiifiyo Muuqaalka Cache

• Hagaaji MS Office "Ma Xaqiijin Karo Shatiga" Fariinta Khaladka

• Sida loo abuuro oo loo socodsiiyo Macro gudaha Word

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Sida loo soo ceshado iimaylada la tirtiray ee Office 365