एमएस एक्सेल के लिए उन्नत VBA गाइड

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

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

माइक्रोसॉफ्ट एक्सेल के लिए उन्नत 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 ऑब्जेक्ट चुनें। ऑब्जेक्ट और मेथड ड्रॉपडाउन बॉक्स से, वर्कशीट और एक्टिवेट चुनें। यह कोड को हर बार चलाएगा जब आप स्प्रेडशीट खोलेंगे।

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

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

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

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

आपके कोड को निम्न क्रम में कार्य करना चाहिए:

कर नामक नया परिणाम कॉलम बनाएं।
बेची गई इकाइयां कॉलम के माध्यम से लूप करें और बिक्री कर की गणना करें।
शीट में उपयुक्त पंक्ति में गणना परिणाम लिखें।

निम्न कोड इन सभी चरणों को पूरा करेगा।

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")

'अंतिम पंक्ति और कॉलम ढूंढें
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) = "कर"

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 आयात कोड को स्वचालित रूप से चलने दे सकते हैं, लेकिन कर गणना कोड को तब चलने के लिए छोड़ सकते हैं जब आप उपयुक्त बटन दबाते हैं।

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

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

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

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

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

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

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

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

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

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

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

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

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

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

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

इस सुविधा को सक्षम करने के लिए, आपको टूल्स और रेफरेंस चुनने की आवश्यकता है। माइक्रोसॉफ्ट CDO फॉर विंडोज 2000 लाइब्रेरी तक नीचे स्क्रॉल करें, इसे सक्षम करें, और 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 = "इस तिमाही में भुगतान किए गए कर"
strFrom = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
strCc = ""
strBcc = ""
strBody = "निम्नलिखित इस तिमाही में बिक्री पर भुगतान किए गए करों का विवरण है।"

बेशक, बॉडी को शीट में मौजूद परिणामों के आधार पर डायनामिक होना चाहिए, इसलिए यहां आपको एक लूप जोड़ना होगा जो रेंज के माध्यम से जाता है, डेटा निकालता है, और एक समय में एक लाइन बॉडी में लिखता है।

Set StartCell = Range("A1") 'अंतिम पंक्ति और कॉलम ढूंढें 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 & "हमने बेचा " & Cells(rowCounter, 3).Value & " " & Cells(rowCounter, 1).Value & " के लिए " & Cells(rowCounter, 4).Value & " और करों का भुगतान किया " & 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 और 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

नोट: यदि आप इस कोड को चलाने का प्रयास करते समय ट्रांसपोर्ट त्रुटि देखते हैं, तो इसकी संभावना है क्योंकि आपका Google खाता "कम सुरक्षित ऐप्स" को चलाने से रोक रहा है। आपको कम सुरक्षित ऐप्स सेटिंग्स पेज पर जाने और इस सुविधा को चालू करने की आवश्यकता होगी।

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

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