შინაარსი
მაშინაც კი, თუ თქვენ არ იცით როგორ დაპროგრამოთ, არის მრავალი ადგილი (წიგნები, ვებსაიტები, ფორუმები), სადაც შეგიძლიათ იპოვოთ მზა VBA მაკრო კოდი Excel-ში ტიპიური ამოცანების დიდი რაოდენობით. ჩემი გამოცდილებით, მომხმარებელთა უმეტესობა ადრე თუ გვიან აგროვებს მაკროების პერსონალურ კოლექციას რუტინული პროცესების ავტომატიზაციისთვის, იქნება ეს ფორმულების მნიშვნელობებად თარგმნა, ჯამების ჩვენება სიტყვებით თუ უჯრედების შეჯამება ფერის მიხედვით. და აქ ჩნდება პრობლემა - ვიზუალ ბაისიკში მაკრო კოდი სადმე უნდა იყოს შენახული, რათა მოგვიანებით გამოიყენოს სამუშაოში.
უმარტივესი ვარიანტია მაკრო კოდის შენახვა პირდაპირ სამუშაო ფაილში Visual Basic რედაქტორზე კლავიატურის მალსახმობის გამოყენებით. Alt+F11 და მენიუს მეშვეობით ახალი ცარიელი მოდულის დამატება ჩასმა – მოდული:
თუმცა, ამ მეთოდს აქვს რამდენიმე უარყოფითი მხარე:
- თუ ბევრი სამუშაო ფაილია და ყველგან საჭიროა მაკრო, მაგალითად მაკრო ფორმულების მნიშვნელობებად გადაქცევისთვის, მაშინ მოგიწევთ კოდის კოპირება ყველა წიგნში.
- არ უნდა დაგვავიწყდეს შეინახეთ ფაილი მაკრო ჩართული ფორმატში (xlsm) ან ორობითი წიგნის ფორმატში (xlsb).
- ასეთი ფაილის გახსნისას მაკრო დაცვა ყოველ ჯერზე გამოსცემს გაფრთხილებას, რომელიც უნდა იყოს აღიარებული (კარგად, ან მთლიანად გამორთეთ დაცვა, რაც შეიძლება ყოველთვის არ იყოს სასურველი).
უფრო ელეგანტური გადაწყვეტა იქნება შექმნა თქვენი საკუთარი დანამატი (Excel Add-in) – სპეციალური ფორმატის ცალკე ფაილი (xlam), რომელიც შეიცავს ყველა თქვენს „საყვარელ“ მაკროს. ამ მიდგომის უპირატესობები:
- საკმარისი იქნება დააკავშირეთ დანამატი ერთხელ Excel-ში – და შეგიძლიათ გამოიყენოთ მისი VBA პროცედურები და ფუნქციები ამ კომპიუტერის ნებისმიერ ფაილში. ამგვარად, თქვენი სამუშაო ფაილების xlsm- და xlsb-ფორმატებში გადარჩენა საჭირო არ არის, რადგან. წყაროს კოდი არ შეინახება მათში, არამედ დამატებით ფაილში.
- დაცვის არც მაკროები შეგაწუხებთ. დანამატები, განსაზღვრებით, სანდო წყაროებია.
- შეუძლია ცალკე ჩანართი Excel-ის ლენტაზე ლამაზი ღილაკებით დამატებითი მაკროების გასაშვებად.
- დანამატი ცალკე ფაილია. მისი მარტივი განსახორციელებლად კომპიუტერიდან კომპიუტერამდე, გაუზიარე კოლეგებს ან თუნდაც გაყიდე 😉
მოდით გავიაროთ ეტაპობრივად თქვენი საკუთარი Microsoft Excel დანამატის შექმნის მთელი პროცესი.
ნაბიჯი 1. შექმენით დამატებითი ფაილი
გახსენით Microsoft Excel ცარიელი სამუშაო წიგნით და შეინახეთ იგი ნებისმიერი შესაფერისი სახელით (მაგალითად MyExcelAddin) დანამატის ფორმატში ბრძანებით ფაილი – Save As ან გასაღებები F12, ფაილის ტიპის მითითებით Excel-ის დანამატი:
გთხოვთ გაითვალისწინოთ, რომ ნაგულისხმევად Excel ინახავს დანამატებს C:UsersYour_nameAppDataRoamingMicrosoftAddIns საქაღალდეში, მაგრამ, პრინციპში, შეგიძლიათ მიუთითოთ თქვენთვის მოსახერხებელი ნებისმიერი სხვა საქაღალდე.
ნაბიჯი 2. ჩვენ ვუკავშირდებით შექმნილ დანამატს
ახლა ჩვენ მიერ შექმნილი დანამატი ბოლო ეტაპზე MyExcelAddin უნდა იყოს დაკავშირებული Excel-თან. ამისათვის გადადით მენიუში ფაილი – ოფციები – დანამატები (ფაილი - ოფციები - დანამატები), დააჭირეთ ღილაკს მომხმარებლის (წადი) ფანჯრის ბოლოში. ფანჯარაში, რომელიც იხსნება, დააჭირეთ ღილაკს მიმოხილვა (დათვალიერება) და მიუთითეთ ჩვენი დანამატის ფაილის მდებარეობა.
თუ ყველაფერი სწორად გააკეთე, მაშინ ჩვენი MyExcelAddin ხელმისაწვდომი დანამატების სიაში უნდა გამოჩნდეს:
ნაბიჯი 3. დაამატეთ მაკრო დანამატს
ჩვენი დანამატი დაკავშირებულია Excel-თან და წარმატებით მუშაობს, მაგრამ მასში ჯერ ერთი მაკრო არ არის. მოდით შევავსოთ იგი. ამისათვის გახსენით Visual Basic რედაქტორი კლავიატურის მალსახმობით Alt+F11 ან ღილაკით Visual Basic tab დეველოპერი (დეველოპერი). თუ ჩანართები დეველოპერი არ ჩანს, მისი ჩვენება შესაძლებელია ფაილი – ოფციები – ლენტის დაყენება (ფაილი - ოფციები - ლენტის მორგება).
უნდა იყოს ფანჯარა რედაქტორის ზედა მარცხენა კუთხეში პროექტი (თუ ის არ ჩანს, მაშინ ჩართეთ მენიუდან ხედი — Project Explorer):
ამ ფანჯარაში ნაჩვენებია ყველა ღია სამუშაო წიგნი და გაშვებული Microsoft Excel დანამატები, მათ შორის ჩვენი. VBAProject (MyExcelAddin.xlam) შეარჩიეთ იგი მაუსით და დაამატეთ მას ახალი მოდული მენიუს საშუალებით ჩასმა – მოდული. ამ მოდულში ჩვენ ვინახავთ ჩვენი დანამატების მაკროების VBA კოდს.
თქვენ შეგიძლიათ ან აკრიფოთ კოდი ნულიდან (თუ იცით დაპროგრამება), ან დააკოპიროთ ის სადმე მზა ადგილიდან (რაც ბევრად უფრო ადვილია). მოდით, ტესტირებისთვის, შევიტანოთ მარტივი, მაგრამ სასარგებლო მაკროს კოდი დამატებულ ცარიელ მოდულში:
კოდის შეყვანის შემდეგ არ დაგავიწყდეთ დააწკაპუნოთ შენახვის ღილაკზე (დისკეტზე) ზედა მარცხენა კუთხეში.
ჩვენი მაკრო FormulasToValuesროგორც ადვილად წარმოიდგენთ, ფორმულებს გარდაქმნის მნიშვნელობებად წინასწარ შერჩეულ დიაპაზონში. ზოგჯერ ამ მაკროებსაც უწოდებენ პროცედურები. მის გასაშვებად, თქვენ უნდა აირჩიოთ უჯრედები ფორმულებით და გახსნათ სპეციალური დიალოგური ფანჯარა Macros ჩანართიდან დეველოპერი (დეველოპერი - მაკრო) ან კლავიატურის მალსახმობი Alt+F8. ჩვეულებრივ, ეს ფანჯარა აჩვენებს ხელმისაწვდომ მაკროებს ყველა ღია სამუშაო წიგნიდან, მაგრამ დანამატი მაკრო აქ არ ჩანს. ამის მიუხედავად, ჩვენ შეგვიძლია შევიყვანოთ ჩვენი პროცედურის სახელი ველში მაკრო სახელი (მაკრო სახელი)და შემდეგ დააჭირეთ ღილაკს გასაშვებად (გაშვება) – და ჩვენი მაკრო იმუშავებს:
| |
აქ ასევე შეგიძლიათ მინიჭოთ კლავიატურის მალსახმობი მაკროს სწრაფად გასაშვებად - ღილაკი პასუხისმგებელია ამაზე პარამეტრები (Პარამეტრები) წინა ფანჯარაში მაკრო:
კლავიშების მინიჭებისას გაითვალისწინეთ, რომ ისინი მგრძნობიარეა ასოზე და კლავიატურის განლაგებაზე. ასე რომ, თუ თქვენ მიანიჭებთ კომბინაციას, როგორიცაა Ctrl+Й, მაშინ, ფაქტობრივად, მომავალში მოგიწევთ დარწმუნდეთ, რომ ჩართული გაქვთ განლაგება და დააჭიროთ დამატებით გადაიტანოსდიდი ასოს მისაღებად.
მოხერხებულობისთვის, ჩვენ ასევე შეგვიძლია დავამატოთ ღილაკი ჩვენი მაკროსთვის სწრაფი წვდომის ხელსაწყოთა ზოლში ფანჯრის ზედა მარცხენა კუთხეში. ამისათვის აირჩიეთ ფაილი – ოფციები – სწრაფი წვდომის ინსტრუმენტთა პანელი (ფაილი — ოფციები — სწრაფი წვდომის ხელსაწყოთა ზოლის მორგება)და შემდეგ ჩამოსაშლელ სიაში ფანჯრის ზედა ნაწილში არის ვარიანტი Macros. ამის შემდეგ ჩვენი მაკრო FormulasToValues შეიძლება განთავსდეს პანელზე ღილაკით დამატება (დამატება) და აირჩიეთ მისთვის ხატულა ღილაკით შეცვლა (რედაქტირება):
ნაბიჯი 4. დაამატეთ ფუნქციები დანამატს
მაგრამ მაკროპროცედურებიასევე არსებობს ფუნქციონალური მაკროები ან როგორც მათ უწოდებენ UDF (მომხმარებლის განსაზღვრული ფუნქცია = მომხმარებლის განსაზღვრული ფუნქცია). მოდით შევქმნათ ცალკე მოდული ჩვენს დანამატში (მენიუ ბრძანება ჩასმა – მოდული) და ჩასვით იქ შემდეგი ფუნქციის კოდი:
ადვილი მისახვედრია, რომ ეს ფუნქცია საჭიროა დღგ-ს ამოსაღებად დღგ-ს ჩათვლით. რა თქმა უნდა, არ არის ნიუტონის ბინომი, მაგრამ ის ჩვენთვის სამაგალითო იქნება ძირითადი პრინციპების საჩვენებლად.
გაითვალისწინეთ, რომ ფუნქციის სინტაქსი განსხვავდება პროცედურისგან:
- მშენებლობა გამოიყენება ფუნქცია…. დასრულების ფუნქცია ნაცვლად ქვე … დასასრული ქვე
- ფუნქციის სახელის შემდეგ, მისი არგუმენტები მითითებულია ფრჩხილებში
- ფუნქციის სხეულში ხდება საჭირო გამოთვლები და შემდეგ შედეგი ენიჭება ცვლადს ფუნქციის სახელწოდებით
ასევე გაითვალისწინეთ, რომ ეს ფუნქცია არ არის საჭირო და შეუძლებელია წინა მაკრო პროცედურის მსგავსად გაშვება დიალოგური ფანჯრის მეშვეობით Macros და ღილაკი გასაშვებად. ასეთი მაკრო ფუნქცია უნდა იყოს გამოყენებული, როგორც სტანდარტული სამუშაო ფურცლის ფუნქცია (SUM, IF, VLOOKUP…), ანუ უბრალოდ შეიყვანეთ ნებისმიერ უჯრედში, არგუმენტად მიუთითეთ თანხის მნიშვნელობა დღგ-ით:
… ან შეიყვანეთ სტანდარტული დიალოგური ფანჯრის მეშვეობით ფუნქციის ჩასართავად (ღილაკი fx ფორმულების ზოლში), აირჩიეთ კატეგორია მომხმარებლის განსაზღვრა (მომხმარებლის განსაზღვრული):
ერთადერთი უსიამოვნო მომენტი აქ არის ფანჯრის ბოლოში ფუნქციის ჩვეულებრივი აღწერილობის არარსებობა. მის დასამატებლად მოგიწევთ შემდეგი რამის გაკეთება:
- გახსენით Visual Basic რედაქტორი კლავიატურის მალსახმობით Alt+F11
- აირჩიეთ დანამატი პროექტის პანელში და დააჭირეთ ღილაკს F2ობიექტის ბრაუზერის ფანჯრის გასახსნელად
- აირჩიეთ თქვენი დამატებითი პროექტი ფანჯრის ზედა ჩამოსაშლელი სიიდან
- დააწკაპუნეთ მაუსის მარჯვენა ღილაკით იმ ფუნქციაზე, რომელიც გამოჩნდება და აირჩიეთ ბრძანება განცხადებები.
- ფანჯარაში შეიყვანეთ ფუნქციის აღწერა აღწერა
- შეინახეთ დამატებითი ფაილი და გადატვირთეთ Excel.
გადატვირთვის შემდეგ ფუნქციამ უნდა აჩვენოს ჩვენ მიერ შეყვანილი აღწერა:
ნაბიჯი 5. შექმენით დანამატის ჩანართი ინტერფეისში
საბოლოო, თუმცა არა სავალდებულო, მაგრამ სასიამოვნო შეხება იქნება ცალკე ჩანართის შექმნა ღილაკით ჩვენი მაკროს გასაშვებად, რომელიც გამოჩნდება Excel-ის ინტერფეისში ჩვენი დანამატის დაკავშირების შემდეგ.
ნაგულისხმევად ნაჩვენები ჩანართების შესახებ ინფორმაცია შეიცავს წიგნს და უნდა იყოს ფორმატირებული სპეციალურ XML კოდში. ასეთი კოდის დაწერისა და რედაქტირების უმარტივესი გზაა სპეციალური პროგრამების - XML რედაქტორების დახმარებით. ერთ-ერთი ყველაზე მოსახერხებელი (და უფასო) არის მაქსიმ ნოვიკოვის პროგრამა Ribbon XML რედაქტორი.
მასთან მუშაობის ალგორითმი შემდეგია:
- დახურეთ Excel-ის ყველა ფანჯარა ისე, რომ არ იყოს ფაილების კონფლიქტი, როდესაც ჩვენ დავამატებთ XML კოდს.
- გაუშვით Ribbon XML Editor პროგრამა და გახსენით ჩვენი MyExcelAddin.xlam ფაილი მასში
- ღილაკით ჩანართების ზედა მარცხენა კუთხეში დაამატეთ კოდის ნაწყვეტი ახალი ჩანართისთვის:
- თქვენ უნდა ჩაწეროთ ცარიელი ბრჭყალები id ჩვენი ჩანართი და ჯგუფი (ნებისმიერი უნიკალური იდენტიფიკატორი) და in ეტიკეტების – ჩვენი ჩანართის სახელები და მასზე ღილაკების ჯგუფი:
- ღილაკით ღილაკს მარცხენა პანელზე, დაამატეთ ცარიელი კოდი ღილაკისთვის და დაამატეთ მას ტეგები:
- ეტიკეტი არის ტექსტი ღილაკზე
- image Mso — ეს არის სურათის პირობითი სახელი ღილაკზე. მე გამოვიყენე წითელი ღილაკის ხატულა სახელწოდებით AnimationCustomAddExitDialog. ყველა ხელმისაწვდომი ღილაკის სახელები (და მათგან რამდენიმე ასეულია!) შეგიძლიათ იხილოთ ინტერნეტის უამრავ საიტზე, თუ მოძებნით საკვანძო სიტყვებს "imageMso". დამწყებთათვის, შეგიძლიათ აქ წასვლა.
- მოქმედებაზე – ასე ჰქვია გამოძახების პროცედურას – სპეციალური მოკლე მაკრო, რომელიც აწარმოებს ჩვენს მთავარ მაკროს FormulasToValues. თქვენ შეგიძლიათ დაარქვით ამ პროცედურას, როგორც გსურთ. ცოტა მოგვიანებით დავამატებთ.
- თქვენ შეგიძლიათ შეამოწმოთ შესრულებული ყველაფრის სისწორე ღილაკის გამოყენებით ინსტრუმენტთა პანელის ზედა ნაწილში მწვანე გამშვები ნიშნით. იმავე ადგილას დააწკაპუნეთ ღილაკზე ფლოპი დისკით ყველა ცვლილების შესანახად.
- დახურეთ Ribbon XML რედაქტორი
- გახსენით Excel, გადადით Visual Basic რედაქტორში და დაამატეთ გამოძახების პროცედურა ჩვენს მაკროში KillFormulasისე, რომ ის აწარმოებს ჩვენს მთავარ მაკროს ფორმულების მნიშვნელობებით ჩანაცვლებისთვის.
- ჩვენ ვინახავთ ცვლილებებს და, Excel-ში დაბრუნებისას, ვამოწმებთ შედეგს:
სულ ეს არის - დანამატი მზად არის გამოსაყენებლად. შეავსეთ იგი თქვენი საკუთარი პროცედურებითა და ფუნქციებით, დაამატეთ ლამაზი ღილაკები – და ბევრად გაგიადვილდებათ მაკროების გამოყენება თქვენს სამუშაოში.
- რა არის მაკროები, როგორ გამოვიყენოთ ისინი თქვენს მუშაობაში, სად მივიღოთ მაკრო კოდი Visual Basic-ში.
- როგორ გავაკეთოთ სპელშ ეკრანი Excel-ში სამუშაო წიგნის გახსნისას
- რა არის პერსონალური მაკრო წიგნი და როგორ გამოვიყენოთ იგი