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

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

एक्सेल में वीबीए कोडिंग का उपयोग करने की क्षमता स्वचालन की पूरी दुनिया खोलती है। आप एक्सेल, पुशबटन में गणनाओं को स्वचालित कर सकते हैं और ईमेल भी भेज सकते हैं। 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

एक्सेल वीबीए एडिटिंग टूल खोलें और शीट 1 ऑब्जेक्ट चुनें। ऑब्जेक्ट और विधि ड्रॉपडाउन बॉक्स से, वर्कशीट choose चुनें और सक्रिय करें . हर बार जब आप स्प्रेडशीट खोलेंगे तो यह कोड चलाएगा।

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

यह सक्रिय कार्यपत्रक को पत्रक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 कोड को पिछले कोड के नीचे चिपकाएँ, और स्क्रिप्ट चलाएँ। आप कॉलम ई में परिणाम देखेंगे।

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

बटन प्रेस से परिणामों की गणना करें

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

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

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

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

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

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

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

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

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

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

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

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

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

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

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

किसी को गणना परिणाम ईमेल करें

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

आप बॉस को ईमेल पत्रक नामक एक और बटन बना सकते हैं उपरोक्त समान प्रक्रिया का उपयोग करना। इस बटन के कोड में एसएमटीपी ईमेल सेटिंग्स को कॉन्फ़िगर करने के लिए एक्सेल सीडीओ ऑब्जेक्ट का उपयोग करना और उपयोगकर्ता-पठनीय प्रारूप में परिणामों को ईमेल करना शामिल होगा।

इस सुविधा को सक्षम करने के लिए, आपको उपकरण और संदर्भ . का चयन करना होगा . Windows 2000 लाइब्रेरी के लिए Microsoft CDO तक नीचे स्क्रॉल करें , इसे सक्षम करें, और ठीक . चुनें ।

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

पहला विषय, प्रति और प्रेषक पते, और ईमेल बॉडी को होल्ड करने के लिए वैरिएबल सेट कर रहा है।

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("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 protected]"
.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

ईमेल@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 ऑटोमेशन बनाएं।