एमएस एक्सेल के लिए एक उन्नत वीबीए गाइड

यदि आप अभी वीबीए के साथ शुरुआत कर रहे हैं, तो आप (VBA)शुरुआती लोगों के लिए हमारे वीबीए गाइड(VBA guide for beginners) का अध्ययन शुरू करना चाहेंगे । लेकिन अगर आप एक अनुभवी VBA विशेषज्ञ हैं और आप अधिक उन्नत चीजों की तलाश कर रहे हैं जो आप Excel में VBA के साथ कर सकते हैं , तो पढ़ते रहें।

एक्सेल में (Excel)वीबीए(VBA) कोडिंग का उपयोग करने की क्षमता स्वचालन की पूरी दुनिया खोलती है। आप एक्सेल(Excel) , पुशबटन में गणनाओं को स्वचालित कर सकते हैं और ईमेल भी भेज सकते हैं। VBA के साथ आपके दैनिक कार्य को स्वचालित करने की जितनी संभावनाएं आप महसूस कर सकते हैं, उससे कहीं अधिक संभावनाएं हैं ।

माइक्रोसॉफ्ट एक्सेल के लिए उन्नत वीबीए गाइड(Advanced VBA Guide For Microsoft Excel)

एक्सेल में (Excel)वीबीए(VBA) कोड लिखने का मुख्य लक्ष्य यह है कि आप एक स्प्रेडशीट से जानकारी निकाल सकते हैं, उस पर कई तरह की गणना कर सकते हैं, और फिर परिणाम वापस स्प्रेडशीट में लिख सकते हैं

एक्सेल(Excel) में वीबीए(VBA) के सबसे सामान्य उपयोग निम्नलिखित हैं ।

  • डेटा आयात(Import) करें और गणना करें
  • (Calculate)एक बटन दबाने वाले उपयोगकर्ता से परिणामों की गणना करें
  • किसी को ईमेल(Email) गणना परिणाम

इन तीन उदाहरणों के साथ, आप अपने स्वयं के उन्नत एक्सेल वीबीए(Excel VBA) कोड की एक किस्म लिखने में सक्षम होना चाहिए ।

डेटा आयात करना और गणना करना(Importing Data and Performing Calculations)

लोगों द्वारा एक्सेल(Excel) का उपयोग करने वाली सबसे आम चीजों में से एक एक्सेल(Excel) के बाहर मौजूद डेटा पर गणना करना है । यदि आप VBA(VBA) का उपयोग नहीं करते हैं , तो इसका मतलब है कि आपको डेटा को मैन्युअल रूप से आयात करना होगा, गणनाओं को चलाना होगा और उन मानों को किसी अन्य शीट या रिपोर्ट में आउटपुट करना होगा।

VBA के साथ , आप पूरी प्रक्रिया को स्वचालित कर सकते हैं। उदाहरण के लिए, यदि आपके पास प्रत्येक सोमवार को आपके कंप्यूटर पर एक निर्देशिका में एक नई (Monday)CSV फ़ाइल डाउनलोड होती है , तो आप मंगलवार(Tuesday) की सुबह पहली बार अपनी स्प्रेडशीट खोलने पर अपना VBA कोड चलाने के लिए कॉन्फ़िगर कर सकते हैं ।

निम्न आयात कोड आपकी एक्सेल(Excel) स्प्रेडशीट में 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

एक्सेल वीबीए (Excel VBA)एडिटिंग(Sheet1) टूल खोलें और शीट 1 ऑब्जेक्ट चुनें। ऑब्जेक्ट और मेथड ड्रॉपडाउन बॉक्स से, वर्कशीट(Worksheet) और एक्टिवेट(Activate) चुनें । हर बार जब आप स्प्रेडशीट खोलेंगे तो यह कोड चलाएगा।

यह एक सब वर्कशीट_एक्टिवेट ()(Sub Worksheet_Activate() ) फंक्शन बनाएगा । उपरोक्त कोड को उस फ़ंक्शन में पेस्ट करें।

यह सक्रिय वर्कशीट को शीट 1 पर सेट करता है, शीट को साफ़ करता है, फ़ाइल पथ का उपयोग करके फ़ाइल से जोड़ता है जिसे आपने strFile चर(Sheet1) के साथ परिभाषित किया है, और फिर फ़ाइल में प्रत्येक पंक्ति के माध्यम से लूप चक्र के साथ(With) और डेटा को सेल ए 1 से शुरू होने वाली शीट में रखता है। .

यदि आप इस कोड को चलाते हैं, तो आप देखेंगे कि CSV फ़ाइल डेटा (CSV)शीट(Sheet1) 1 में आपकी रिक्त स्प्रेडशीट में आयात किया गया है ।

आयात करना केवल पहला कदम है। इसके बाद, आप उस कॉलम के लिए एक नया हेडर बनाना चाहते हैं जिसमें आपके गणना परिणाम होंगे। इस उदाहरण में, मान लें कि आप प्रत्येक वस्तु की बिक्री पर भुगतान किए गए 5% करों की गणना करना चाहते हैं।

आपके कोड को जो कार्रवाई करनी चाहिए उसका क्रम है:

  1. टैक्स(taxes) नामक नया परिणाम कॉलम बनाएं ।
  2. बेची गई इकाइयों(units sold) के माध्यम से लूप करें और बिक्री कर की गणना करें।
  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 कोड को पिछले कोड के नीचे चिपकाएँ, और स्क्रिप्ट चलाएँ। आपको परिणाम कॉलम ई में दिखाई देंगे।

अब, हर बार जब आप अपनी एक्सेल वर्कशीट खोलते हैं, तो यह स्वचालित रूप से बाहर निकल जाएगी और (Excel)सीएसवी(CSV) फ़ाइल से डेटा की सबसे ताज़ा कॉपी प्राप्त करेगी । फिर, यह गणना करेगा और परिणाम शीट पर लिखेगा। अब आपको मैन्युअल रूप से कुछ भी करने की ज़रूरत नहीं है!

बटन प्रेस से परिणामों की गणना करें(Calculate Results From Button Press)

यदि आप शीट के खुलने पर स्वचालित रूप से चलने के बजाय गणना चलने पर अधिक प्रत्यक्ष नियंत्रण चाहते हैं, तो आप इसके बजाय एक नियंत्रण बटन का उपयोग कर सकते हैं।

(Control)यदि आप नियंत्रित करना चाहते हैं कि कौन सी गणनाओं का उपयोग किया जाता है तो नियंत्रण बटन उपयोगी होते हैं। उदाहरण के लिए, ऊपर के समान मामले में, क्या होगा यदि आप एक क्षेत्र के लिए 5% कर की दर और दूसरे के लिए 7% कर की दर का उपयोग करना चाहते हैं?

आप उसी CSV आयात कोड को स्वचालित रूप से चलाने की अनुमति दे सकते हैं, लेकिन जब आप उपयुक्त बटन दबाते हैं तो कर गणना कोड को चलने के लिए छोड़ दें।

ऊपर दी गई समान स्प्रेडशीट का उपयोग करते हुए, डेवलपर(Developer) टैब चुनें, और रिबन में नियंत्रण(Controls) समूह से सम्मिलित करें चुनें। (Insert)ड्रॉपडाउन मेनू से पुश बटन (push button)ActiveX Control चुनें।(ActiveX Control)

पुशबटन को शीट के किसी भी भाग पर ड्रा करें जहाँ से कोई डेटा जाएगा।

पुश बटन पर राइट-क्लिक करें, और गुण(Properties) चुनें । गुण(Properties) विंडो में , उस कैप्शन को बदलें जिसे आप उपयोगकर्ता को दिखाना चाहते हैं। इस मामले में यह Calculate 5% Tax हो सकती है ।

आपको यह टेक्स्ट पुश बटन पर ही दिखाई देगा। गुण(properties) विंडो बंद करें , और स्वयं पुशबटन पर डबल-क्लिक करें। यह कोड संपादक विंडो खोलेगा, और आपका कर्सर उस फ़ंक्शन के अंदर होगा जो उपयोगकर्ता द्वारा पुशबटन दबाने पर चलेगा।

कर की दर गुणक को 0.05 पर रखते हुए, उपरोक्त अनुभाग से कर गणना कोड को इस फ़ंक्शन में चिपकाएं। सक्रिय शीट को परिभाषित करने के लिए निम्नलिखित 2 पंक्तियों को शामिल करना याद रखें।

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

अब, दूसरा पुश बटन बनाकर प्रक्रिया को दोबारा दोहराएं। कैप्शन बनाएं Calculate 7% Tax करें ।

उस बटन पर डबल-क्लिक(Double-click) करें और वही कोड पेस्ट करें, लेकिन टैक्स मल्टीप्लायर 0.07 बनाएं।

अब, आप जिस बटन को दबाते हैं, उसके आधार पर कर कॉलम की गणना उसी के अनुसार की जाएगी।

एक बार जब आप कर लेते हैं, तो आपके पास अपनी शीट पर दोनों पुश बटन होंगे। उनमें से प्रत्येक एक अलग कर गणना शुरू करेगा और परिणाम कॉलम में अलग-अलग परिणाम लिखेगा। 

इसे टेक्स्ट करने के लिए, डेवलपर(Developer) मेनू का चयन करें, और डिज़ाइन मोड को अक्षम करने के लिए रिबन में नियंत्रण समूह डिज़ाइन मोड (Controls)का (Design Mode)चयन(Design Mode) करें । यह पुश बटन को सक्रिय करेगा। 

प्रत्येक पुश बटन का चयन करके देखें कि "कर" परिणाम कॉलम कैसे बदलता है।

किसी को ईमेल गणना परिणाम(Email Calculation Results to Someone)

क्या होगा यदि आप ईमेल के माध्यम से किसी को स्प्रेडशीट पर परिणाम भेजना चाहते हैं?

आप ऊपर दी गई समान प्रक्रिया का उपयोग करके बॉस को ईमेल शीट(Email Sheet to Boss) नामक एक और बटन बना सकते हैं । इस बटन के कोड में एसएमटीपी(SMTP) ईमेल सेटिंग्स को कॉन्फ़िगर करने के लिए एक्सेल सीडीओ(Excel CDO) ऑब्जेक्ट का उपयोग करना और उपयोगकर्ता-पठनीय प्रारूप में परिणाम ईमेल करना शामिल होगा।

इस सुविधा को सक्षम करने के लिए, आपको टूल्स और संदर्भों(Tools and References) का चयन करना होगा । विंडोज 2000 लाइब्रेरी के लिए माइक्रोसॉफ्ट सीडीओ(Microsoft CDO for Windows 2000 Library) तक स्क्रॉल करें, इसे सक्षम करें, और ठीक(OK) चुनें ।

ईमेल भेजने और स्प्रेडशीट परिणामों को एम्बेड करने के लिए आपको कोड के तीन मुख्य खंड बनाने होंगे।

पहला विषय को रखने के लिए चर सेट कर रहा है, पते से(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 = "[email protected]"
strTo = "[email protected]"
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("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

[email protected] और पासवर्ड को अपने खाते के विवरण से बदलें ।

अंत में, ईमेल भेजने को आरंभ करने के लिए, निम्नलिखित कोड डालें।

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

नोट(Note) : यदि आप इस कोड को चलाने का प्रयास करते समय कोई परिवहन त्रुटि देखते हैं, तो यह संभव है क्योंकि आपका Google खाता "कम सुरक्षित ऐप्स" को चलने से रोक रहा है। आपको कम सुरक्षित ऐप्स सेटिंग पृष्ठ(less secure apps settings page) पर जाना होगा और इस सुविधा को चालू करना होगा।

उसके सक्षम होने के बाद, आपका ईमेल भेजा जाएगा। यह उस व्यक्ति को दिखता है जो आपका स्वचालित रूप से उत्पन्न परिणाम ईमेल प्राप्त करता है।

जैसा कि आप देख सकते हैं कि एक्सेल वीबीए(Excel VBA) के साथ आप वास्तव में बहुत कुछ स्वचालित कर सकते हैं । इस लेख में आपके द्वारा सीखे गए कोड स्निपेट के साथ खेलने का प्रयास करें और अपने स्वयं के अनूठे VBA ऑटोमेशन बनाएं।



Vicky Krishnan

About the author

मैं 10 से अधिक वर्षों के अनुभव के साथ एक कंप्यूटर पेशेवर हूं। अपने खाली समय में, मुझे ऑफिस डेस्क पर मदद करना और बच्चों को इंटरनेट का उपयोग करना सिखाना पसंद है। मेरे कौशल में बहुत सी चीजें शामिल हैं, लेकिन सबसे महत्वपूर्ण बात यह है कि मुझे पता है कि लोगों को समस्याओं को हल करने में कैसे मदद करनी है। अगर आपको किसी ऐसे व्यक्ति की ज़रूरत है जो आपकी किसी अत्यावश्यक चीज़ में मदद कर सके या बस कुछ बुनियादी सुझाव चाहते हों, तो कृपया मुझसे संपर्क करें!


Related posts