এমএস এক্সেলের জন্য একটি উন্নত VBA গাইড

আপনি যদি সবেমাত্র VBA দিয়ে শুরু করেন, তাহলে আপনি নতুনদের জন্য আমাদের VBA গাইড অধ্যয়ন শুরু করতে চাইবেন। কিন্তু আপনি যদি একজন অভিজ্ঞ VBA বিশেষজ্ঞ হন এবং আপনি Excel এ VBA দিয়ে করতে পারেন এমন আরও উন্নত জিনিস খুঁজছেন, তাহলে পড়তে থাকুন৷

এক্সেলে VBA কোডিং ব্যবহার করার ক্ষমতা অটোমেশনের পুরো বিশ্ব খুলে দেয়। আপনি এক্সেল, পুশবাটন, এমনকি ইমেল পাঠাতে স্বয়ংক্রিয়ভাবে গণনা করতে পারেন। VBA এর সাথে আপনার দৈনন্দিন কাজকে স্বয়ংক্রিয় করার আরও বেশি সম্ভাবনা রয়েছে যা আপনি উপলব্ধি করতে পারেন৷

Microsoft Excel এর জন্য উন্নত VBA গাইড

এক্সেলে VBA কোড লেখার মূল লক্ষ্য হল যাতে আপনি একটি স্প্রেডশীট থেকে তথ্য বের করতে পারেন, এতে বিভিন্ন ধরনের গণনা করতে পারেন এবং তারপর ফলাফলগুলি স্প্রেডশীটে আবার লিখতে পারেন

এক্সেল-এ VBA-এর সবচেয়ে সাধারণ ব্যবহার নিম্নলিখিতগুলি৷

• ডেটা আমদানি করুন এবং গণনা সম্পাদন করুন
• বোতাম টিপে একজন ব্যবহারকারীর ফলাফল গণনা করুন
• কাউকে গণনার ফলাফল ইমেল করুন

এই তিনটি উদাহরণ দিয়ে, আপনি আপনার নিজের উন্নত এক্সেল VBA কোডের বিভিন্ন ধরনের লিখতে সক্ষম হবেন।

ডেটা আমদানি করা এবং গণনা সম্পাদন করা

মানুষ এক্সেল ব্যবহার করে সবচেয়ে সাধারণ জিনিসগুলির মধ্যে একটি হল এক্সেলের বাইরে বিদ্যমান ডেটার উপর গণনা করা। আপনি যদি 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 অবজেক্ট নির্বাচন করুন। বস্তু এবং পদ্ধতির ড্রপডাউন বাক্স থেকে, ওয়ার্কশীট নির্বাচন করুন এবং সক্রিয় করুন . আপনি যখনই স্প্রেডশীট খুলবেন এটি কোডটি চালাবে৷

এটি একটি সাব ওয়ার্কশীট_অ্যাক্টিভেট() তৈরি করবে৷ ফাংশন উপরের কোডটি সেই ফাংশনে পেস্ট করুন।

এটি সক্রিয় ওয়ার্কশীটটিকে শীট1-এ সেট করে , শীট সাফ করে, strFile দিয়ে আপনার সংজ্ঞায়িত ফাইল পাথ ব্যবহার করে ফাইলের সাথে সংযোগ করে পরিবর্তনশীল, এবং তারপর সহ ফাইলের প্রতিটি লাইনের মধ্য দিয়ে লুপ সাইকেল করে এবং সেল A1 থেকে শুরু করে শীটে ডেটা রাখে।

আপনি যদি এই কোডটি চালান, তাহলে আপনি দেখতে পাবেন যে CSV ফাইলের ডেটা আপনার ফাঁকা স্প্রেডশীটে, Sheet1-এ আমদানি করা হয়েছে .

আমদানি শুধুমাত্র প্রথম ধাপ। পরবর্তী, আপনি কলামের জন্য একটি নতুন শিরোনাম তৈরি করতে চান যাতে আপনার গণনার ফলাফল থাকবে। এই উদাহরণে, ধরা যাক আপনি প্রতিটি আইটেমের বিক্রয়ের জন্য প্রদত্ত 5% ট্যাক্স গণনা করতে চান৷

আপনার কোডের যে ক্রমটি নেওয়া উচিত তা হল:

1. ট্যাক্স নামে নতুন ফলাফল কলাম তৈরি করুন .
2. বিক্রীত ইউনিট দিয়ে লুপ করুন কলাম এবং বিক্রয় কর গণনা করুন।
3. পত্রকের উপযুক্ত সারিতে গণনার ফলাফল লিখুন।

নিম্নলিখিত কোডটি এই সমস্ত পদক্ষেপগুলি সম্পন্ন করবে৷

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 ফাইল থেকে ডেটার নতুন কপি পাবে। তারপর, এটি গণনা সম্পাদন করবে এবং শীটে ফলাফল লিখবে। আপনাকে আর ম্যানুয়ালি কিছু করতে হবে না!

বোতাম প্রেস থেকে ফলাফল গণনা করুন

শীট খোলার সময় স্বয়ংক্রিয়ভাবে চলার পরিবর্তে গণনা চালানোর সময় আপনি যদি সরাসরি নিয়ন্ত্রণ করতে চান তবে আপনি পরিবর্তে একটি নিয়ন্ত্রণ বোতাম ব্যবহার করতে পারেন।

কন্ট্রোল বোতামগুলি দরকারী যদি আপনি কোন গণনাগুলি ব্যবহার করা হয় তা নিয়ন্ত্রণ করতে চান৷ উদাহরণস্বরূপ, উপরের মতো একই ক্ষেত্রে, আপনি যদি একটি অঞ্চলের জন্য 5% করের হার এবং অন্য অঞ্চলের জন্য 7% করের হার ব্যবহার করতে চান?

আপনি একই CSV আমদানি কোড স্বয়ংক্রিয়ভাবে চালানোর অনুমতি দিতে পারেন, কিন্তু যখন আপনি উপযুক্ত বোতাম টিপুন তখন ট্যাক্স গণনা কোডটি চালানোর জন্য ছেড়ে দিন৷

উপরের মত একই স্প্রেডশীট ব্যবহার করে, ডেভেলপার নির্বাচন করুন ট্যাব, এবং ঢোকান নির্বাচন করুন নিয়ন্ত্রণ থেকে ফিতা মধ্যে গ্রুপ. পুশ বোতাম নির্বাচন করুন ড্রপডাউন মেনু থেকে ActiveX কন্ট্রোল।

শীটের যেকোনো অংশে পুশবাটন আঁকুন যেখানে কোনো ডেটা যাবে।

পুশ বোতামে ডান-ক্লিক করুন, এবং বৈশিষ্ট্যগুলি নির্বাচন করুন৷ . বৈশিষ্ট্য উইন্ডোতে, আপনি ব্যবহারকারীর কাছে যা প্রদর্শন করতে চান তাতে ক্যাপশন পরিবর্তন করুন। এই ক্ষেত্রে এটি 5% ট্যাক্স গণনা হতে পারে .

আপনি এই পাঠ্যটি পুশ বোতামে প্রতিফলিত দেখতে পাবেন। বৈশিষ্ট্যগুলি বন্ধ করুন উইন্ডো, এবং পুশবাটনে ডাবল ক্লিক করুন। এটি কোড এডিটর উইন্ডোটি খুলবে এবং আপনার কার্সারটি ফাংশনের ভিতরে থাকবে যা ব্যবহারকারী পুশবাটন চাপলে চলবে৷

ট্যাক্সের হার গুণক 0.05 রেখে এই ফাংশনে উপরের বিভাগ থেকে ট্যাক্স গণনা কোড পেস্ট করুন। সক্রিয় শীট সংজ্ঞায়িত করতে নিম্নলিখিত 2 লাইন অন্তর্ভুক্ত করতে মনে রাখবেন।

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

এখন, একটি দ্বিতীয় পুশ বোতাম তৈরি করে আবার প্রক্রিয়াটি পুনরাবৃত্তি করুন। ক্যাপশনটি তৈরি করুন 7% ট্যাক্স গণনা করুন .

সেই বোতামে ডাবল ক্লিক করুন এবং একই কোড পেস্ট করুন, কিন্তু ট্যাক্স গুণক 0.07 করুন।

এখন, আপনি কোন বোতাম টিপবেন তার উপর নির্ভর করে, ট্যাক্স কলাম সেই অনুযায়ী গণনা করা হবে।

একবার আপনি হয়ে গেলে, আপনার শীটে উভয় পুশ বোতাম থাকবে। তাদের প্রত্যেকে একটি ভিন্ন ট্যাক্স গণনা শুরু করবে এবং ফলাফলের কলামে ভিন্ন ফলাফল লিখবে।

এটি পাঠ্য করতে, বিকাশকারী নির্বাচন করুন৷ মেনু, এবং ডিজাইন মোড নির্বাচন করুন ডিজাইন মোড নিষ্ক্রিয় করতে রিবনে কন্ট্রোল গ্রুপ তৈরি করুন . এটি পুশ বোতামগুলিকে সক্রিয় করবে।

"কর" ফলাফল কলাম কিভাবে পরিবর্তিত হয় তা দেখতে প্রতিটি পুশ বোতাম নির্বাচন করার চেষ্টা করুন৷

কাউকে ইমেল গণনার ফলাফল

আপনি যদি ইমেলের মাধ্যমে কাউকে স্প্রেডশীটে ফলাফল পাঠাতে চান?

আপনি বসকে ইমেল পত্রক নামে আরেকটি বোতাম তৈরি করতে পারেন উপরের একই পদ্ধতি ব্যবহার করে। এই বোতামের কোডটি SMTP ইমেল সেটিংস কনফিগার করার জন্য Excel CDO অবজেক্ট ব্যবহার করে এবং ব্যবহারকারী-পাঠযোগ্য বিন্যাসে ফলাফলগুলি ইমেল করা জড়িত।

এই বৈশিষ্ট্যটি সক্ষম করতে, আপনাকে সরঞ্জাম এবং তথ্যসূত্র নির্বাচন করতে হবে৷ . Microsoft CDO for Windows 2000 Library-এ স্ক্রোল করুন , এটি সক্ষম করুন এবং ঠিক আছে নির্বাচন করুন৷ .

একটি ইমেল পাঠাতে এবং স্প্রেডশীট ফলাফল এম্বেড করার জন্য আপনাকে যে কোডটি তৈরি করতে হবে তার তিনটি প্রধান বিভাগ রয়েছে৷

প্রথমটি হল বিষয় ধরে রাখার জন্য ভেরিয়েবল সেট আপ করা, ঠিকানা থেকে এবং ইমেল বডি।

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 সার্ভারের মাধ্যমে ইমেল পাঠাতে পারেন। আপনি যদি Gmail ব্যবহার করেন তবে এটি সাধারণত আপনার Gmail ইমেল ঠিকানা, আপনার Gmail পাসওয়ার্ড এবং Gmail 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 এবং পাসওয়ার্ড প্রতিস্থাপন করুন।

অবশেষে, ইমেল পাঠানো শুরু করতে, নিম্নলিখিত কোডটি প্রবেশ করান।

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

দ্রষ্টব্য :এই কোডটি চালানোর চেষ্টা করার সময় আপনি যদি একটি পরিবহন ত্রুটি দেখতে পান, তাহলে সম্ভবত আপনার Google অ্যাকাউন্টটি "কম সুরক্ষিত অ্যাপ" চালানো থেকে ব্লক করছে। আপনাকে কম সুরক্ষিত অ্যাপ সেটিংস পৃষ্ঠাতে যেতে হবে এবং এই বৈশিষ্ট্যটি চালু করতে হবে।

এটি সক্রিয় করার পরে, আপনার ইমেল পাঠানো হবে। যে ব্যক্তি আপনার স্বয়ংক্রিয়ভাবে তৈরি ফলাফল ইমেল গ্রহণ করে তার কাছে এটি এমনই দেখায়৷

আপনি দেখতে পাচ্ছেন যে আপনি আসলে এক্সেল VBA এর সাথে স্বয়ংক্রিয়ভাবে অনেক কিছু করতে পারেন। আপনি এই নিবন্ধে যে কোড স্নিপেটগুলি সম্পর্কে শিখেছেন তার সাথে খেলার চেষ্টা করুন এবং আপনার নিজস্ব অনন্য VBA অটোমেশন তৈরি করুন৷