যদি আপনি VBA-র সাথে এখনও শুরু করছেন, তবে আপনার আমাদের বেগinners VBA গাইড পড়া উচিত। কিন্তু আপনি একজন অভিজ্ঞ VBA এক্সপার্ট হন এবং এক্সেলে VBA দিয়ে আরও এডভান্সড কাজ করতে চান, তবে পড়তে থাকুন।
এক্সেলে VBA কোডিং ব্যবহার করার ক্ষমতা অটোমেশনের একটি পুরো নতুন বিশ্ব খুলে দেয়। আপনি এক্সেলে ক্যালকুলেশন, পুশবাটন, এবং এমনকি ইমেইল পাঠানো অটোমেট করতে পারেন। VBA দিয়ে আপনার দৈনন্দিন কাজ অটোমেট করার সম্ভাবনা আপনার কল্পনার চেয়ে বেশি।
মাইক্রোসফট এক্সেলের জন্য এডভান্সড VBA গাইড
এক্সেলে VBA কোড লেখার মূল লক্ষ্য হলো স্প্রেডশীট থেকে তথ্য বের করা, সেটিতে নানা ধরণের ক্যালকুলেশন করা, এবং ফলাফলগুলো স্প্রেডশীটে ফিরে লিখা।
এক্সেলে VBA-এর সবচেয়ে সাধারণ ব্যবহারের ক্ষেত্রগুলো নিচে দেওয়া হলো:
- ডেটা ইম্পোর্ট করা এবং ক্যালকুলেশন করা
- ব্যবহারকারী বাটন চাপলে ফলাফল ক্যালকুলেট করা
- ক্যালকুলেশন ফলাফল ইমেইল করে কাউকে পাঠানো
এই তিনটি উদাহরণ দিয়ে আপনি নিজের নানান এডভান্সড এক্সেল VBA কোড লিখতে পারবেন।
ডেটা ইম্পোর্ট এবং ক্যালকুলেশন
এক্সেলের সবচেয়ে সাধারণ ব্যবহারetako একটি হলো এক্সেলের বাইরে থাকা ডেটার ওপর ক্যালকুলেশন করা। যদি আপনি VBA ব্যবহার না করেন, তবে আপনাকে ম্যানুয়ালি ডেটা ইম্পোর্ট করতে হবে, ক্যালকুলেশন চালাতে হবে এবং সেই মানগুলো অন্য শিট বা রিপোর্টে আউটপুট করতে হবে।
VBA দিয়ে আপনি সম্পূর্ণ প্রক্রিয়াটি অটোমেট করতে পারেন। উদাহরণস্বরূপ, যদি প্রতিবার সোমবারে আপনার কম্পিউটারের একটি ডিরেক্টরিতে নতুন একটি CSV ফাইল ডাউনলোড হয়, তবে আপনি আপনার VBA কোড কনফিগার করতে পারেন যাতে মঙ্গলবার সকালে আপনি স্প্রেডশীট খোলার সাথে সাথে এটি চলে।
নিচের ইম্পোর্ট কোডটি চলে এবং CSV ফাইলটি আপনার এক্সেল স্প্রেডশীটে ইম্পোর্ট করবে।
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents
strFile = "c:\temp\purchases.csv"
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
এক্সেল VBA এডিটিং টুল খুলুন এবং Sheet1 অবজেক্ট নির্বাচন করুন। অবজেক্ট এবং মেথড ড্রপডাউন বক্স থেকে Worksheet এবং Activate বেছে নিন। এটি স্প্রেডশীট খোলার সাথে সাথে কোডটি চালাবে।
এটি একটি Sub Worksheet_Activate() ফাংশন তৈরি করবে। উপরের কোডটি সেই ফাংশনে পেস্ট করুন।
এটি এক্টিভ ওয়ার্কশীটকে Sheet1-এ সেট করে, শিটটি ক্লিয়ার করে, strFile ভেরিয়েবল দিয়ে ডিফাইন করা ফাইল পাথ ব্যবহার করে ফাইলটির সাথে সংযোগ স্থাপন করে, এবং তারপর With লুপ ফাইলের প্রতিটি লাইন দিয়ে ঘুরে ডেটাটি সেল A1 থেকে শুরু করে শিটে রাখে।
যদি আপনি এই কোড চালান, তবে আপনি দেখবেন CSV ফাইলের ডেটা আপনার ব্ল্যাঙ্ক স্প্রেডশীতে, Sheet1-এ ইম্পোর্ট হয়ে গেছে।
ইম্পোর্ট শুধু প্রথম ধাপ। এরপর, আপনি ক্যালকুলেশন ফলাফল ধারণ করার কলামের জন্য একটি নতুন হেডার তৈরি করতে চাবেন। এই উদাহরণে, ধরা যাক আপনি প্রতিটি আইটেমের বিক্রয়ের ওপর ৫% সেলস ট্যাক্স ক্যালকুলেট করতে চান।
আপনার কোড যে ক্রমে কাজ করবে সেটি হলো:
- taxes নামে নতুন রেজাল্ট কলাম তৈরি করা।
- units sold কলামের মাধ্যমে লুপ চালিয়ে সেলস ট্যাক্স ক্যালকুলেট করা।
- ক্যালকুলেট করা ফলাফল শিটের উপযুক্ত সেলে লিখা।
নিচের কোডটি এই সব ধাপ সম্পন্ন করবে।
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
Cells(1, 5) = "taxes"
For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell
এই কোড আপনার ডেটা শিটের লাস্ট রো খুঁজে বের করে, এবং তারপর ডেটার প্রথম এবং লাস্ট রো অনুযায়ী সেলের রেঞ্জ (বিক্রয় মূল্যের কলাম) সেট করে। তারপর কোডটি সেই সেলগুলোর মাধ্যমে লুপ চলে, ট্যাক্স ক্যালকুলেশন করে এবং ফলাফল আপনার নতুন কলামে (কলাম 5) লিখে।
উপরের VBA কোডটি পূর্বের কোডের নিচে পেস্ট করুন, এবং স্ক্রিপ্টটি চালান। আপনি কলাম E-তে ফলাফল দেখতে পাবেন।
এখন, আপনি هرবার আপনার এক্সেল ওয়ার্কশীট খোললে, এটি স্বয়ংক্রিয়ভাবে CSV ফাইল থেকে তাজা ডেটা নিয়ে আসবে। তারপর, এটি ক্যালকুলেশন করবে এবং ফলাফল শিটে লিখবে। আপনাকে আর ম্যানুয়ালি কিছু করতে হবে না!
বাটন চাপে ফলাফল ক্যালকুলেট করা
যদি আপনি চান ক্যালকুলেশনটি কখন চলে সেটি সরাসরি নিয়ন্ত্রণ করুন, শিট খোলার সাথে সাথে স্বয়ংক্রিয়ভাবে না চলতে দিন, তবে আপনি একটি কন্ট্রোল বাটন ব্যবহার করতে পারেন।
কন্ট্রোল বাটন উপযোগী যদি আপনি চান কোন ক্যালকুলেশন ব্যবহার হবে সেটি নিয়ন্ত্রণ করেন। উদাহরণস্বরূপ, উপরের মতো একই কেসে, যদি আপনি একটি অঞ্চলের জন্য ৫% ট্যাক্স রেট এবং অন্য অঞ্চলের জন্য ৭% ট্যাক্স রেট ব্যবহার করতে চান?
আপনি একই CSV ইম্পোর্ট কোড স্বয়ংক্রিয়ভাবে চলে দিতে পারেন, কিন্তু ট্যাক্স ক্যালকুলেশন কোডটি উপযুক্ত বাটন চাপলে চলে সেটি ঠিক করতে পারেন।
উপরের মতো একই স্প্রেডশীট ব্যবহার করে, Developer ট্যাব নির্বাচন করুন, এবং রিবনে Controls গ্রুপ থেকে Insert নির্বাচন করুন। ড্রপডাউন মেনু থেকে push button ActiveX Control নির্বাচন করুন।
পুশবাটনটি শিটের যেকোনো খালি জায়গায় আঁকুন যেখানে ডেটা যাবে না।
পুশ বাটনে রাইট-ক্লিক করুন, এবং Properties নির্বাচন করুন। Properties উইন্ডোতে, Caption পরিবর্তন করে যা ইউজারকে দেখাতে চান। এই ক্ষেত্রে এটি হতে পারে Calculate 5% Tax।
আপনি পুশ বাটনটিতে এই টেক্সটটি দেখতে পাবেন। Properties উইন্ডো বন্ধ করুন, এবং পুশবাটনটিতে ডাবল-ক্লিক করুন। এটি কোড এডিটর উইন্ডো খুলবে, এবং আপনার কার্সর সেই ফাংশনের ভিতরে থাকবে যা ইউজার পুশবাটন চাপলে চলে।
উপরের সেকশন থেকে ট্যাক্স ক্যালকুলেশন কোডটি এই ফাংশনে পেস্ট করুন, ট্যাক্স রেট মাল্টিপ্লায়ার 0.05 রাখুন। নিচের ২ লাইনটি অন্তর্ভুক্ত করতে ভুলবেন না যাতে এক্টিভ শিট ডিফাইন হয়।
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
এখন, আবার একই প্রক্রিয়া दुহরান, একটি দ্বিতীয় পুশ বাটন তৈরি করুন। ক্যাপশন করুন Calculate 7% Tax।
সেই বাটনে ডাবল-ক্লিক করুন এবং একই কোড পেস্ট করুন, কিন্তু ট্যাক্স মাল্টিপ্লায়ার 0.07 করুন।
এখন, আপনি কোন বাটন চাপছেন তার উপর নির্ভর করে, taxes কলামটি সেই অনুযায়ী ক্যালকুলেট হবে।
যখন xog হয়, তবে আপনার শিটে দুটি পুশ বাটন থাকবে। প্রতিটি আলাদা ট্যাক্স ক্যালকুলেশন শুরু করবে এবং রেজাল্ট কলামে ভিন্ন ফলাফল লিখবে।
টেস্ট করতে, Developer মেনু নির্বাচন করুন, এবং রিবনে Controls গ্রুপ থেকে Design Mode নির্বাচন করে Design Mode ডিজেবল করুন। এটি পুশ বাটনগুলো এক্টিভ করবে।
প্রতিটি পুশ বাটন চাপে দেখুন "taxes" রেজাল্ট কলাম কীভাবে পরিবর্তন হয়।
ক্যালকুলেশন ফলাফল কাউকে ইমেইল করা
আপনি যদি স্প্রেডশীটের ফলাফল কাউকে ইমেইল করে পাঠাতে চান?
আপনি উপরের মতো একই প্রক্রিয়া অনুসরণ করে Email Sheet to Boss নামে একটি বাটন তৈরি করতে পারেন। এই বাটনের কোডে এক্সেল CDO অবজেক্ট ব্যবহার করে SMTP ইমেইল সেটিংস কনফিগার করা হবে, এবং ফলাফল ইউজার-রিডেবল ফরম্যাটে ইমেইল করা হবে।
এই ফিচার সক্রিয় করতে, আপনাকে Tools and References নির্বাচন করতে হবে। নিচে স্ক্রোল করে Microsoft CDO for Windows 2000 Library খুঁজুন, এটি ইনেবল করুন, এবং OK নির্বাচন করুন।
একটি ইমেইল পাঠাতে এবং স্প্রেডশীটের ফলাফল এম্বেড করতে আপনার যে কোড তৈরি করতে হবে তার তিনটি মূল بخش আছে।
প্রথমটি হলো সাবজেক্ট, To এবং From ঠিকানা, এবং ইমেইল বডি ধারণ করার ভেরিয়েবল সেটআপ।
Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
বডি ডাইনামিক হতে হবে শিটের ফলাফলের উপর নির্ভর করে, তাই আপনাকে এখানে একটি লুপ যোগ করতে হবে যা রেঞ্জের মাধ্যমে যাবে, ডেটা এক্সট্র্যাক্ট করবে, এবং একবারে এক লাইন বডিতে লিখবে।
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
পরবর্তী সেকশনটি হলো SMTP সেটিংস সেটআপ করা যাতে আপনি আপনার SMTP সার্ভারের মাধ্যমে ইমেইল পাঠাতে পারেন। আপনি যদি জিমেইল ব্যবহার করেন, তবে এটি সাধারণত আপনার জিমেইল ঠিকানা, জিমেইল পাসওয়ার্ড, এবং জিমেইল SMTP সার্ভার (smtp.gmail.com)।
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
email@website.com এবং password আপনার নিজের অ্যাকাউন্টের বিবরণ দিয়ে রিপ্লেস করুন।
শেষে, ইমেইল পাঠাতে নিচের কোডটি যোগ করুন।
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.Description <> "" Then MsgBox Err.Description
নোট: যদি এই কোড চালাতে সময় ট্রান্সপোর্ট এরর দেখেন, তবে এর কারণ হতে পারে আপনার গুগল অ্যাকাউন্ট "less secure apps" ব্লক করছে। আপনাকে less secure apps সেটিংস পেজে যেতে হবে এবং এই ফিচারটি ON করতে হবে।
এটি ইনেবল করার পর, আপনার ইমেইল পাঠানো হবে। আপনার অটোমেটিক্যালি জেনারেটেড রেজাল্ট ইমেইল পাওয়ালা ব্যক্তি কী দেখবে সেটি নিচে দেওয়া হলো।
আপনি দেখতে পাচ্ছেন এক্সেল VBA দিয়ে আপনি কত কিছু অটোমেট করতে পারেন। এই আর্টিকেলে শিখা কোড স্নিপেটগুলো নিয়ে পরীক্ষা-নিরীক্ষা করুন এবং আপনার নিজস্ব уникаল VBA অটোমেশন তৈরি করুন।
