Microsoft Excel waxa ay u saamaxdaa isticmaalayaasha in ay si otomaatig ah u sameeyaan sifooyinka iyo amarada iyaga oo isticmaalaya macros iyo Visual Basic for Applications ( VBA ). VBA waa luqadda barnaamijka ee Excel^{(VBA is the programming language Excel)} uu isticmaalo si uu u abuuro makros. Waxa kale oo ay fulin doontaa amarro toos ah oo ku salaysan shuruudo gaar ah.

Macros waa amarro taxane ah oo horay loo duubay. Waxay si toos ah u shaqeeyaan marka amar gaar ah la bixiyo. Haddii aad ku leedahay hawlo Microsoft Excel ah oo aad si isdaba joog ah u qabato, sida xisaabaadka, maaraynta mashruuca, ama mushahar bixinta, hab-raacyada habraacan ayaa kaa badbaadin kara waqti badan.

Marka la eego tab horumariyaha ee ku yaal ^(Developer)Ribbon ee Excel , isticmaalayaashu waxay duubi karaan gujisyada jiirka iyo furayaasha furayaasha (macro). Si kastaba ha ahaatee, hawlaha qaarkood waxay u baahan yihiin qoraal qoto dheer oo ka badan inta macros ay bixin karaan. Tani waa halka qorista VBA ay noqoto faa'iido weyn. Waxay u ogolaataa isticmaalayaasha inay abuuraan qoraallo aad u adag.

Maqaalkan, waxaan ku sharxi doonaa kuwa soo socda:

• Awoodsiinta Qoraalada & Macros
• Sida loo abuuro Macro gudaha Excel
• Tusaalaha Gaarka ah ee Macro
• Wax badan ka baro VBA
• U samee badhanka^(Button) si aad ugu bilowdo VBA^(VBA)
• Ku dar koodka^{(Add Code)} si aad u siiso badhanka shaqaynta^{(Button Functionality)}
• Ma Shaqaysay?

Awoodsiinta Qoraalada & Macros^{(Enabling Scripts & Macros)}

Kahor intaadan abuurin macros ama VBA scripts gudaha Excel , waa inaad awood u yeelataa tabka horumariyaha ee liiska ^{(Developer )}Ribbon . Tab-^(Developer) sameeyaha looma hawlgelin si caadi ah. Si aad awood ugu yeelato:

• Fur xaashida shaqada ee Excel.
• Guji File > Options > Customize Ribbon.

• Sax ku dheji sanduuqa ku xiga horumariyaha^(Developer) .

• Guji tab horumariyaha^(Developer) ka menu -ka Ribbon .

• Marka xigta, dhagsii Amniga Macro^{(Macro Security)} oo sax sanduuqa ku xiga si aad awood ugu yeelatid makrosyada oo dhan (lama talinayo; koodka khatarta ah ayaa socon kara). ^{(Enable all macros (not recommended; potentially dangerous code can run). )}
• Kadibna ku dhufo OK.

Sababta aan macros-ku si caadi ah loo damin oo ay ula iman waayaan digniin waa in ay yihiin kood kombuyuutar oo ka koobnaan kara malware.

Hubi^(Make) in dukumeentigu ka yimid ilo lagu kalsoon yahay haddii aad ka shaqaynayso mashruuc la wadaago ee Excel iyo barnaamijyada kale ee Microsoft .

Marka aad dhammayso adeegsiga qoraalladaada iyo macroskaaga, dami dhammaan macros-yada si aad uga hortagto koodka xaasidnimada leh inuu faafiyo dukumentiyada kale.

Ku samee Macro gudaha Excel^{(Create a Macro in Excel)}

Dhammaan ficillada aad ku sameyso Excel marka aad duubeyso makro ayaa lagu daraa. 

• Laga soo bilaabo tab Developer, guji Record Macro .

• Geli magaca Macro^{(Macro name)} , Furaha Gaaban^{(Shortcut key)} , iyo Sharaxaada. ^{(Description. )}Magacyada Macro^(Macro) waa inay ku bilowdaan xaraf mana yeelan karaan meelo bannaan. Furaha gaaban waa in uu ahaado xaraf.

Go'aanso meesha aad rabto inaad ku kaydiso makro-ka xulashooyinka soo socda:

• Buug-shaqeedka Macro-shaqsiyeedka^{(Personal Macro Workbook)} : Tani waxay abuuri doontaa dukumeenti qarsoon oo Excel ah oo leh macros kaydsan si loogu isticmaalo dukumeenti kasta oo Excel ah.
• Buug- hawleed Cusub^{(New Workbook)} : Wuxuu abuuri doonaa dukumeenti cusub oo Excel si uu u kaydiyo macros-yada la abuuray.
• Buug-hawleedkan^{(This Workbook)} : Kani waxa kaliya oo lagu dabaqi doonaa dukumeentiga aad hadda tafatirayso.

Marka la sameeyo, dhagsii OK . 

• Ku^(Run) socodsii tallaabooyinka aad rabto inaad otomaatig u sameyso. Marka aad dhammayso, dhagsii Jooji Duubista^{(Stop Recording)} . 
• Markaad rabto inaad gasho macro-gaaga, isticmaal kiiboodhka gaaban ee aad siisay.

Tusaalaha Gaarka ah ee Macro^{(Specific Example Of a Macro)}

Aan ku bilowno xaashiyaha fudud ee macaamiisha iyo inta lagu leeyahay. Waxaan ku bilaabi doonaa abuurista makro si loo qaabeeyo xaashida shaqada.

Aynu ka soo qaadno inaad go'aansatey in dhammaan xaashida faafinta ay isticmaalaan qaab ka duwan sida ku dhejinta magaca hore iyo kan dambe ee tiirar kala duwan. 

Waxaad gacanta ku bedeli kartaa tan. Ama waxaad samayn kartaa barnaamij adigoo isticmaalaya makro si aad si toos ah kuugu habayso si sax ah.

Diiwaangeli Macro^{(Record The Macro)}

• Guji Diiwaanka Macro^{(Record Macro)} . Aan u wacno Format_Customer_Data oo guji OK . 
• Si aan u helno qaabaynta aan rabno, waxaan u bedeli doonaa magaca tiirka koowaad Magaca koowaad^{(First Name)} . 
• Kadib geli tiir ku xigta A oo u wac Magaca Dambe^{(Last Name)} . 
• Calaamadee^(Highlight) dhammaan magacyada tiirka koowaad (kuwaaso wali ku jira magaca hore iyo kan dambe), oo guji Xogta^(Data) ka socota marinka ribbon.
• Guji qoraalka ku socda tiirarka^{(Text to Columns)} .

• Sax ku xaddid^(Delimited) > Xiga^{(Next )} > Kala saar meel bannaan^{(Separate by Space)} > Xiga^{(Next )} > Dhammays^(Finish) . Hoos ka eeg sawirka sawirka iyo sida magacyadii hore iyo kuwii dambe loo kala saaray habka sare.

• Si aad u qaabayso goobta ku haboon ee hadhaaga^{(Balance Due)} , muuji cadadka. Guji^(Click) Guriga > Qaabka ^{(Home )}Shuruuda^{(Conditional Formatting)} ah > Xeerarka Unugga Iftiimi^{(Highlight Cell Rules)} > Ka Weyn^{(Greater Than )} > 0 .

Tani waxay muujin doontaa unugyada leh dheelitirnaanta. Waxaan ku darnay dhowr macaamiil oo aan lahayn dheelitirnaan sababtoo ah sii muujinta qaabaynta.  

• Ku laabo Developer oo guji Jooji Duubista^{(Stop Recording)} .

Codso Macro^{(Apply The Macro)}

Aan ku bilowno xaashida asalka ah ka hor inta aanan duubin makro si aan u qaabaynno si sax ah. Guji ^(Click)Macros , dooro oo socodsii macro^(Run) -ga aad hadda abuurtay.

Markaad socodsiiso makro, dhammaan qaabaynta waa laguu sameeyay. Macro-kan aan hadda abuurnay waxa lagu kaydiyaa Tifaftiraha Aasaasiga ah ee Visual^{(Visual Basic Editor)} .

Isticmaalayaashu waxay ku socodsiin karaan macros siyaabo kala duwan. Akhri orod makro^{(Run a macro)} si aad wax badan u barato.  

Wax badan ka baro VBA^{(Learn More About VBA)}

Si aad u ogaato wax ku saabsan VBA, dhagsii Macro ka tab horumariyaha . ^(Developer)Hel mid aad samaysay oo guji Edit.

Koodhka aad ku aragto sanduuqa sare waa waxa la sameeyay markii aad duubtay macro-gaaga. 

Sidoo kale waa waxa aad socodsiin doonto marka aad rabto inaad u qaabayso xaashida lacag bixinta macaamiisha kale si la mid ah.

Samee badhan si aad ugu bilowdo VBA^{(Create a Button To Get Started With VBA)}

Isticmaalka isla xaashida sare ee macaamiisha iyo inta lagu leeyahay, aynu abuurno beddelka lacagta.

• Si aad u geliso curiye badhanka, u gudub tab  horumariyaha .^{(Developer )}
• Ka dooro batoonka ActiveX Command^{(ActiveX Command Button )} ka hoos u dhaca ku xiga Geli^(Insert) qaybta Kontaroolada ^(Controls).

• Jiid^(Drag) badhanka meel kasta oo ka mid ah xaashida xaashida si aad si fudud u geli karto oo u beddelo hadhow haddii aad rabto.

• Si aad ugu dhejiso koodka, midig-guji badhanka oo dooro Properties . Waxaan u haysan doonaa magaca^(Name) sida CommandButton iyo qoraalka qoraalka^{(Caption )} si loo beddelo^{( Convert )} (tani waa qoraalka badhanka).

Ku dar koodka si aad u siiso badhanka shaqaynta^{(Add Code To Give The Button Functionality)}

Codaynta VBA^(VBA) kama dhacdo interface -ka Excel . Waxaa lagu sameeyaa deegaan gaar ah.  

• Tag tab horumariyaha^(Developer) oo hubi in Habka Nakhshadu^{(Design Mode)} uu firfircoon yahay.

• Si aad u gasho furaha badhanka aan hadda abuurnay, ku dhufo midigta oo dooro Koodhka Muuqaalka^{(View Code)} .

• Markaad eegto koodka sawirka hoose, ogaysii bilawga ( Private Sub ) iyo dhamaadka ( Dhammaadka Sub^{(End Sub)} ) ee koodka ayaaba jira.

• Koodhka hoose ayaa dhaqaajin doona habka beddelka lacagta.

ActiveCell.Value = (ActiveCell * 1.28)

Ujeedada aan ka leenahay qaybtan waa in aan beddelno lacagta ku jirta xaashida faafintayada. Qoraalka kore wuxuu ka tarjumayaa heerka sarrifka GBP ilaa USD . Qiimaha cusub ee unuggu waxa uu noqonayaa waxa hadda jira oo lagu dhufto 1.28.

Sawirka hoose wuxuu ku tusayaa sida koodku u ekaado daaqada VBA ka dib markaad geliso .

• Tag File in navigation sare oo guji Xir oo ku soo celi Microsoft Excel^{(click on Close and Return to Microsoft Excel)} si aad ugu noqoto interface ugu weyn ee Excel .

Ma Shaqaysay?^{(Did It Work?)}

Kahor intaadan tijaabin koodhkaaga, waa inaad marka hore damisaa Qaabka Naqshadeynta^{(Design Mode)} (guji dusha) si aad uga fogaato wax ka bedel kale oo aad siiso badhanka shaqayntiisa.

• Ku qor^(Type) nambar kasta xaashidaada ka dibna dhagsii badhanka Beddelka^(Convert) . Haddii qiimaha lambarkaagu uu kordho ku dhawaad ​​rubuc-meel, wuu shaqeeyay.

Tusaalahan, waxaan dhigay lambarka 4 unug. Kadib markaad riixdo Beddel^(Convert) , nambarku wuxuu isu beddelay 5.12. Maadaama 4 jeer 1.28 ay tahay 5.12, koodhka ayaa si sax ah loo fuliyay.

Hadda oo aad fahamtay sida loo abuuro makro ama qoraal gudaha Excel , waxaad u isticmaali kartaa si otomaatig ah ficillo badan oo Excel ah .

How To Create a VBA Macro Or Script In Excel

Micrоsoft Excel enablеs users to automate features and commands using macros and Visual Basic for Applicatіons (VBA) scripting. VBA is the programming language Excel uses to create macros. It will also execute automated commands based on specific conditions.

Macros are a series of pre-recorded commands. They run automatically when a specific command is given. If you have tasks in Microsoft Excel that you repeatedly do, such as accounting, project management, or payroll, automating these processes can save a lot of time.

Under the Developer tab on the Ribbon in Excel, users can record mouse clicks and keystrokes (macros).  However, some functions require more in-depth scripting than macros can provide. This is where VBA scripting becomes a huge benefit. It allows users to create more complex scripts.

In this article, we will explain the following:

• Enabling Scripts & Macros
• How to Create a Macro in Excel
• Specific Example of a Macro
• Learn More About VBA
• Create a Button to Get Started with VBA
• Add Code to Give the Button Functionality
• Did it Work?

Enabling Scripts & Macros

Before you can create macros or VBA scripts in Excel, you must enable the Developer tab on the Ribbon menu. The Developer tab is not enabled by default. To enable it:

• Open an Excel worksheet.
• Click on File > Options > Customize Ribbon.

• Put a tick in the box next to Developer.

• Click on the Developer tab from the Ribbon menu.

• Next, click on Macro Security and tick the box next to Enable all macros (not recommended; potentially dangerous code can run). 
• Then click OK.

The reason macros are not turned on by default and come with a warning is that they are computer code that could contain malware.

Make sure the document is from a trusted source if you are working on a shared project in Excel and other Microsoft programs.

When you are done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.

Create a Macro in Excel

All the actions you take in Excel while recording a macro are added to it. 

• From the Developer tab, click on Record Macro.

• Enter a Macro name, a Shortcut key, and a Description. Macro names must begin with a letter and can’t have any spaces. The shortcut key must be a letter.

Decide where you want to store the macro from the following options:

• Personal Macro Workbook: This will create a hidden Excel document with stored macros to be used with any Excel documents.
• New Workbook: Will create a new Excel document to store the created macros.
• This Workbook: This will only be applied to the document you are currently editing.

When done, click OK. 

• Run through the actions you want to automate. When you are finished, click Stop Recording. 
• When you want to access your macro, use the keyboard shortcut you gave it.

Specific Example Of a Macro

Let’s start with a simple spreadsheet for customers and how much they owe. We will begin by creating a macro to format the worksheet.

Let’s assume that you decide that all spreadsheets should use a different format such as putting first and last name in separate columns. 

You can manually change this. Or you can create a program using a macro to automatically format it correctly for you.

Record The Macro

• Click on Record Macro. Let’s call it Format_Customer_Data and click OK. 
• To get the formatting we want, we will change the first column name to First Name. 
• Then insert a column next to A and call it Last Name. 
• Highlight all the names in the first column (which still include first and last name), and click on Data from the ribbon navigation.
• Click on Text to Columns.

• Tick Delimited > Next > Separate by Space > Next > Finish. See screenshot below and how the first and last names were separated by the process above.

• To format the Balance Due field, highlight the amounts. Click on Home > Conditional Formatting > Highlight Cell Rules > Greater Than > 0.

This will highlight the cells that have a balance due. We added a few customers with no balance due to further illustrate the formatting.  

• Go back to Developer and click Stop Recording.

Apply The Macro

Let’s start with the original spreadsheet before we recorded the macro to format it correctly. Click on Macros, select and Run the macro you just created.

When you run a macro, all the formatting is done for you. This macro we just created is stored in the Visual Basic Editor.

Users can run macros in several different ways. Read Run a macro to learn more.  

Learn More About VBA

To learn about VBA, click on Macro from the Developer tab. Find one you have created and click Edit.

The code you see in the box above is what was created when you recorded your macro. 

It is also what you will run when you want to format other customer payment spreadsheets in the same way.

Create a Button To Get Started With VBA

Using the same spreadsheet above with customers and how much they owe, let’s create a currency converter.

• To insert a button element, navigate to the Developer tab. 
• Select ActiveX Command Button from the dropdown next to Insert in the Controls section.

• Drag the button anywhere on the spreadsheet so you can easily access it and change it later if you want.

• To attach the code, right-click on the button and select Properties. We will keep the Name as CommandButton and the Caption to Convert (this is the button text).

Add Code To Give The Button Functionality

VBA coding doesn’t take place in the Excel interface. It is done in a separate environment.  

• Go to the Developer tab and make sure Design Mode is active.

• To access the code of the button we just created, right-click on it and select View Code.

• Looking at the code in the screenshot below, notice the beginning (Private Sub) and end (End Sub) of the code is already there.

• The code below will drive the currency conversion procedure.

ActiveCell.Value = (ActiveCell * 1.28)

Our purpose in this section is to convert the currency in our spreadsheet. The script above reflects the exchange rate from GBP to USD. The new value of a cell will be what is currently there multiplied by 1.28.

The screenshot below shows you how the code looks in the VBA window after you insert it .

• Go to File in the top navigation and click on Close and Return to Microsoft Excel to return to the main Excel interface.

Did It Work?

Before you can test your code, you must first disable Design Mode (click on it) to avoid further modifications and give the button functionality.

• Type any number into your spreadsheet and then click the Convert button. If the value of your number increases by approximately one-quarter, it worked.

For this example, I put the number 4 into a cell. After clicking Convert, the number changed to 5.12. Since 4 times 1.28 is 5.12, the code was carried out correctly.

Now that you understand how to create a macro or script in Excel, you can use them to automate a multitude of actions in Excel.

Related posts

• Hagaha VBA Sare ee MS Excel

• 5 Shaqooyinka Qoraalka Xaashiyaha Google ee aad u baahan tahay inaad ogaato

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida Looga Saaro Safka Kooban Ee Excel

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida loo isticmaalo Excel's What-If Analysis

• Sida loo xisaabiyo Z-Score gudaha Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Sida loo Furo Word iyo Excel si Badbaado ah

• Sida loo sameeyo shaxda Pie ee Excel