დამატებითი VBA სახელმძღვანელო MS Excel– ისთვის

თუ თქვენ ახლახან იწყებთ VBA– ს, მაშინ გსურთ დაიწყოთ ჩვენი სწავლა VBA სახელმძღვანელო დამწყებთათვის. მაგრამ თუ თქვენ ხართ გამოცდილი VBA ექსპერტი და ეძებთ უფრო მოწინავე ნივთებს, რომელთა გაკეთებაც შეგიძლიათ VBA– ში Excel– ში, განაგრძეთ კითხვა.

Excel– ში VBA კოდირების გამოყენების შესაძლებლობა ხსნის ავტომატიზაციის მთელ სამყაროს. თქვენ შეგიძლიათ ავტომატიზიროთ გამოთვლები Excel- ში, ღილაკებზე და ელ.ფოსტის გაგზავნაც კი. VBA– სთან თქვენი ყოველდღიური მუშაობის ავტომატიზაციის მეტი შესაძლებლობა არსებობს, ვიდრე თქვენ წარმოგიდგენიათ.

მოწინავე VBA სახელმძღვანელო Microsoft Excel– ისთვის

Excel– ში VBA კოდის წერის მთავარი მიზანია, რომ მიიღოთ ცხრილებიდან ინფორმაცია, შეასრულოთ მასზე სხვადასხვა გამოთვლები და შემდეგ ჩაწეროთ შედეგები ცხრილში

ქვემოთ მოცემულია VBA– ს ყველაზე გავრცელებული გამოყენება Excel– ში.

• მონაცემების იმპორტი და გამოთვლების გაკეთება
• გამოთვალეთ შედეგები მომხმარებლის მიერ ღილაკზე დაჭერით
• ელფოსტის გაანგარიშების შედეგები ვინმესთვის

ამ სამი მაგალითით, თქვენ უნდა შეეძლოთ დაწეროთ თქვენი საკუთარი მოწინავე Excel VBA კოდი.

მონაცემების იმპორტი და გამოთვლების შესრულება

ერთ -ერთი ყველაზე გავრცელებული რამ, რისთვისაც ადამიანები Excel- ს იყენებენ არის გამოთვლების გაკეთება მონაცემებზე, რომლებიც არსებობს Excel- ის გარეთ. თუ თქვენ არ იყენებთ VBA– ს, ეს ნიშნავს რომ თქვენ უნდა მოახდინოთ მონაცემების ხელით იმპორტი, განახორციელოთ გამოთვლები და გამოაქვეყნოთ ეს მნიშვნელობები სხვა ფურცელზე ან ანგარიშში.

VBA– ს საშუალებით შეგიძლიათ მთელი პროცესის ავტომატიზაცია. მაგალითად, თუ თქვენ გაქვთ ახალი CSV ფაილი გადმოწერილი თქვენს კომპიუტერში, ყოველ ორშაბათს, შეგიძლიათ დააკონფიგურიროთ თქვენი VBA კოდი იმუშაოს, როდესაც პირველად გახსნით თქვენს ცხრილს სამშაბათს დილით.

შემდეგი იმპორტის კოდი გაუშვებს და შემოიტანს CSV ფაილს თქვენს Excel ცხრილში.

Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook. ფურცლები ("ფურცელი 1") უჯრედები. ClearContents strFile = “c: \ temp \ buyes.csv” ws. QueryTables. დამატება (კავშირი: = "TEXT;" & strFile, დანიშნულება: = ws. დიაპაზონი ("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = მართალია .ახალი განახლება. დასრულება ერთად

გახსენით Excel VBA რედაქტირების ინსტრუმენტი და შეარჩიეთ Sheet1 ობიექტი. ობიექტისა და მეთოდის ჩამოსაშლელი ყუთებიდან აირჩიეთ სამუშაო ფურცელი და გააქტიურეთ. ეს გაუშვებს კოდს ყოველ ჯერზე, როდესაც გახსნით ცხრილს.

ეს შექმნის ა ქვე სამუშაო ფურცელი_აქტივაცია () ფუნქცია. ჩასვით კოდი ზემოთ აღნიშნულ ფუნქციაში.

ეს ადგენს აქტიურ სამუშაო ფურცელს ფურცელი 1, ასუფთავებს ფურცელს, უკავშირდება ფაილს ფაილური ბილიკის გამოყენებით, რომელიც თქვენ განსაზღვრეთ strFile ცვლადი და შემდეგ თან ციკლი ციკლდება ფაილის ყველა სტრიქონში და ათავსებს მონაცემებს ფურცელში A1 უჯრედიდან დაწყებული.

თუ ამ კოდს გაუშვებთ, ნახავთ, რომ CSV ფაილის მონაცემები იმპორტირებულია თქვენს ცარიელ ცხრილში ფურცელი 1.

იმპორტი მხოლოდ პირველი ნაბიჯია. შემდეგი, თქვენ გსურთ შექმნათ ახალი სათაური სვეტისთვის, რომელიც შეიცავს თქვენს გამოთვლის შედეგებს. ამ მაგალითში, ვთქვათ, რომ გსურთ გამოთვალოთ თითოეული ნივთის გაყიდვაზე გადახდილი გადასახადი 5%.

თქვენი კოდის მოქმედებების თანმიმდევრობა არის:

1. შექმენით ახალი შედეგების სვეტი სახელწოდებით გადასახადები.
2. მარყუჟი მეშვეობით გაიყიდა ერთეული სვეტი და გამოთვალეთ გაყიდვების გადასახადი.
3. ჩაწერეთ გამოთვლის შედეგები ფურცელში შესაბამის სტრიქონში.

შემდეგი კოდი შეასრულებს ყველა ამ ნაბიჯს.

Dim LastRow As Long
Dim StartCell როგორც დიაპაზონი
Dim rowCounter როგორც მთელი რიცხვი
Dim rng როგორც დიაპაზონი, უჯრედი როგორც დიაპაზონი
Dim fltTax როგორც ორმაგი
დააყენეთ StartCell = დიაპაზონი ("A1")
'იპოვე ბოლო რიგი და სვეტი
LastRow = ws. უჯრედები (ws. რიგები. დათვლა, StartCell. სვეტი). დასასრული (xlUp). რიგი
დააყენეთ rng = ws. დიაპაზონი (ws უჯრედები (2, 4), ws. უჯრედები (ბოლო რიგი, 4))
rowCounter = 2
უჯრედები (1, 5) = "გადასახადები"
რენგის თითოეული უჯრედისთვის
fltTax = უჯრედი. ღირებულება * 0.05
უჯრედები (rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
შემდეგი უჯრედი

ეს კოდი პოულობს ბოლო სტრიქონს თქვენი მონაცემების ფურცელში და შემდეგ ადგენს უჯრედების დიაპაზონს (სვეტი გაყიდვების ფასებით) მონაცემთა პირველი და ბოლო რიგის მიხედვით. შემდეგ კოდი გადადის თითოეულ ამ უჯრედში, ასრულებს გადასახადის გამოთვლას და ჩაწერს შედეგებს თქვენს ახალ სვეტში (სვეტი 5).

ჩასვით ზემოთ მოყვანილი VBA კოდი წინა კოდის ქვემოთ და გაუშვით სკრიპტი. თქვენ ნახავთ შედეგებს, რომლებიც გამოჩნდება სვეტში E.

ახლა, ყოველ ჯერზე, როდესაც გახსნით თქვენს Excel– ს, ის ავტომატურად გამოვა და მიიღებს მონაცემების უახლეს ასლს CSV ფაილიდან. შემდეგ, ის შეასრულებს გამოთვლებს და შედეგებს ჩაწერს ფურცელზე. თქვენ აღარ გჭირდებათ არაფრის გაკეთება ხელით!

გამოთვალეთ შედეგები ღილაკზე დაჭერით

თუ გირჩევნიათ უფრო პირდაპირი კონტროლი გაანგარიშების დროს, ვიდრე ავტომატურად გაშვება ფურცლის გახსნისას, შეგიძლიათ გამოიყენოთ საკონტროლო ღილაკი.

კონტროლის ღილაკები სასარგებლოა, თუ გსურთ გააკონტროლოთ რომელი გამოთვლები გამოიყენება. მაგალითად, იმავე შემთხვევაში, როგორც ზემოთ, რა მოხდება, თუ გსურთ გამოიყენოთ 5% –იანი გადასახადი ერთ რეგიონში, ხოლო 7% –იანი გადასახადი სხვა რეგიონში?

თქვენ შეგიძლიათ დაუშვათ იგივე CSV იმპორტის კოდის ავტომატურად გაშვება, მაგრამ დატოვეთ საგადასახადო გაანგარიშების კოდი, როდესაც დააჭირეთ შესაბამის ღილაკს.

იგივე ცხრილის გამოყენებით, როგორც ზემოთ, აირჩიეთ დეველოპერი ჩანართი და აირჩიეთ ჩასმა დან კონტროლი ჯგუფი ლენტში. აირჩიეთ დააჭირეთ ღილაკს ActiveX კონტროლი ჩამოსაშლელი მენიუდან.

დახაზეთ ღილაკი ფურცლის ნებისმიერ ნაწილზე, იქიდან სადაც ნებისმიერი მონაცემი წავა.

დააჭირეთ ღილაკს მარჯვენა ღილაკით და აირჩიეთ Თვისებები. თვისებების ფანჯარაში შეცვალეთ წარწერა იმაზე, რისი ჩვენებაც გსურთ მომხმარებლისთვის. ამ შემთხვევაში შეიძლება იყოს გამოთვალეთ 5% გადასახადი.

თქვენ ნახავთ ამ ტექსტს, რომელიც აისახება თავად ღილაკზე. დახურეთ თვისებები ფანჯარა და ორჯერ დააწკაპუნეთ თავად ღილაკზე. ეს გახსნის კოდის რედაქტორის ფანჯარას და თქვენი კურსორი იქნება იმ ფუნქციის შიგნით, რომელიც გააქტიურდება, როდესაც მომხმარებელი დააჭერს ღილაკს.

ჩასვით საგადასახადო გაანგარიშების კოდი ზემოთ მოცემული განყოფილებიდან ამ ფუნქციაში და შეინარჩუნეთ საგადასახადო განაკვეთის მულტიპლიკატორი 0.05 -ზე. გახსოვდეთ, რომ ჩართოთ შემდეგი 2 სტრიქონი აქტიური ფურცლის დასადგენად.

Dim ws როგორც სამუშაო ფურცელი, strFile როგორც სიმებიანი
დააყენეთ ws = ActiveWorkbook. ფურცლები ("ფურცელი 1")

ახლა გაიმეორეთ პროცესი, შექმენით მეორე ღილაკი. გააკეთეთ წარწერა გამოთვალეთ 7% გადასახადი.

ორჯერ დააწკაპუნეთ ამ ღილაკზე და ჩასვით იგივე კოდი, მაგრამ გადასახადის გამრავლება გახადეთ 0.07.

ახლა იმისდა მიხედვით, თუ რომელ ღილაკს დააჭერთ, გადასახადების სვეტი შესაბამისად გამოითვლება.

დასრულების შემდეგ, თქვენს ფურცელზე გექნებათ ორივე ღილაკი. თითოეული მათგანი იწყებს სხვადასხვა საგადასახადო გაანგარიშებას და შედეგების სვეტში ჩაწერს სხვადასხვა შედეგს.

ამის დასაწერად აირჩიეთ დეველოპერი მენიუ და აირჩიეთ დიზაინის რეჟიმი შექმენით Controls ჯგუფი ლენტში, რომ გამორთოთ დიზაინის რეჟიმი. ეს გაააქტიურებს ღილაკებს.

სცადეთ აირჩიოთ თითოეული ღილაკი, რომ ნახოთ როგორ იცვლება "გადასახადების" შედეგების სვეტი.

ელფოსტის გაანგარიშების შედეგები ვინმესთვის

რა მოხდება, თუ გსურთ ელცხრილის შედეგები გაუგზავნოთ ვინმეს ელექტრონული ფოსტით?

თქვენ შეგიძლიათ შექმნათ სხვა ღილაკი სახელწოდებით ელფოსტის ფურცელი უფროსს იგივე პროცედურის გამოყენებით ზემოთ. ამ ღილაკის კოდი მოიცავს Excel CDO ობიექტის გამოყენებას SMTP ელ.ფოსტის პარამეტრების კონფიგურაციისთვის და შედეგების გაგზავნას მომხმარებლისთვის წაკითხული ფორმატით.

ამ ფუნქციის გასააქტიურებლად, თქვენ უნდა აირჩიოთ ინსტრუმენტები და მითითებები. გადაახვიეთ ქვემოთ Microsoft CDO Windows 2000 ბიბლიოთეკისთვის, ჩართეთ და აირჩიეთ კარგი.

კოდის სამი ძირითადი ნაწილია, რომელიც თქვენ უნდა შექმნათ ელ.ფოსტის გასაგზავნად და ცხრილების შედეგების ჩასაყენებლად.

პირველი არის ცვლადების დაყენება საგანი, მისამართები და მისამართები და ელ.ფოსტის სხეული.

დაბურული CDO_Mail როგორც ობიექტი
Dim CDO_Config როგორც ობიექტი
დაბალ SMTP_Config როგორც ვარიანტი
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc როგორც სიმებიანი
Dim strBcc როგორც სიმებიანი
Dim strBody როგორც სიმებიანი
Dim LastRow As Long
Dim StartCell როგორც დიაპაზონი
Dim rowCounter როგორც მთელი რიცხვი
Dim rng როგორც დიაპაზონი, უჯრედი როგორც დიაპაზონი
Dim fltTax როგორც ორმაგი

დააყენეთ ws = ActiveWorkbook. ფურცლები ("ფურცელი 1")
strSubject = "გადასახადები გადაიხადა ამ კვარტალში"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "ქვემოთ მოცემულია გაყიდვებისას გადახდილი გადასახადების განაწილება ამ კვარტალში."

რა თქმა უნდა, სხეული უნდა იყოს დინამიური იმისდა მიხედვით, თუ რა შედეგები მოაქვს ფურცელზე, ასე რომ თქვენ აქ უნდა დაამატოთ მარყუჟი, რომელიც გადის დიაპაზონში, ამოიღებს მონაცემებს და წერს ხაზს ერთდროულად სხეული.

დააყენეთ StartCell = დიაპაზონი ("A1") 'იპოვეთ ბოლო სტრიქონი და სვეტი. LastRow = ws. უჯრედები (ws. რიგები. დათვლა, StartCell. სვეტი). დასასრული (xlUp). რიგი. დააყენეთ rng = ws. დიაპაზონი (ws უჯრედები (2, 4), ws. უჯრედები (ბოლო რიგი, 4)) rowCounter = 2. strBody = strBody & vbCrLf თითოეული უჯრედისათვის rng strBody = strBody & vbCrLf strBody = strBody & "ჩვენ გავყიდეთ" & უჯრედები (rowCounter, 3). ღირებულება & "of" & უჯრედები (rowCounter, 1). ღირებულება _ & "for" & Cells (rowCounter, 4). ღირებულება & "და გადახდილი გადასახადები" & Cells (rowCounter, 5). ღირებულება & "." rowCounter = rowCounter + 1. შემდეგი უჯრედი

შემდეგი განყოფილება მოიცავს SMTP პარამეტრების დაყენებას, ასე რომ თქვენ შეგიძლიათ გააგზავნოთ ელ.წერილი თქვენი SMTP სერვერის საშუალებით. თუ თქვენ იყენებთ Gmail- ს, ეს ჩვეულებრივ არის თქვენი Gmail ელ.ფოსტის მისამართი, თქვენი Gmail პაროლი და Gmail SMTP სერვერი (smtp.gmail.com).

დააყენეთ CDO_Mail = CreateObject ("CDO.Message") Error GoTo Error_Handling. დააყენეთ CDO_Config = CreateObject ("CDO.Configuration") CDO_ კონფიგურაცია. დატვირთვა -1. დააყენეთ SMTP_Config = CDO_Config. ველები SMTP_Config.. ელემენტი (" http://schemas.microsoft.com/cdo/configuration/sendusing") = 2.. ელემენტი (" http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com". ელემენტი (" http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1.. ელემენტი (" http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]". ელემენტი (" http://schemas.microsoft.com/cdo/configuration/sendpassword") = "პაროლი". ელემენტი (" http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465.. ელემენტი (" http://schemas.microsoft.com/cdo/configuration/smtpusessl") = მართალია. .განახლება დაასრულეთ CDO_Mail კომპლექტით .კონფიგურაცია = CDO_Config. დასრულება ერთად

შეცვალეთ [email protected] და პაროლი თქვენი ანგარიშის დეტალებით.

დაბოლოს, ელ.ფოსტის გაგზავნის დასაწყებად, ჩადეთ შემდეგი კოდი.

CDO_ ფოსტა. საგანი = strSubject
CDO_ ფოსტა. მდებარეობა = strFrom
CDO_ ფოსტა. დან = strTo
CDO_ ფოსტა. TextBody = strBody
CDO_ ფოსტა. CC = strCc
CDO_ ფოსტა. BCC = strBcc
CDO_ ფოსტა. გაგზავნა
შეცდომა_მუშავება:
თუ Err. აღწერა <> "" შემდეგ MsgBox Err. აღწერა

შენიშვნა: თუ თქვენ ხედავთ სატრანსპორტო შეცდომას ამ კოდის გაშვების მცდელობისას, სავარაუდოა, რომ თქვენი Google ანგარიში ბლოკავს „ნაკლებად უსაფრთხო პროგრამების“ გაშვებას. თქვენ უნდა ეწვიოთ ნაკლებად უსაფრთხო პროგრამების პარამეტრების გვერდი და ჩართე ეს ფუნქცია.

ამის ჩართვის შემდეგ თქვენი ელ.ფოსტა გაიგზავნება. ასე გამოიყურება ის ადამიანი, ვინც იღებს თქვენს ავტომატურად გენერირებულ შედეგებს ელ.

როგორც ხედავთ, ბევრი რამ შეგიძლიათ რეალურად ავტომატიზიროთ Excel VBA– ით. სცადეთ ითამაშოთ ამ კოდის ამონაწერით, რომლის შესახებაც თქვენ ისწავლეთ ამ სტატიაში და შექმენით თქვენი უნიკალური VBA ავტომატიზაცია.