🎩 🤞🏼 👨🏾‍⚕️ एक्सेल में VBA के साथ काम करने के लिए कुछ सुझाव 🌋 👩🏾‍🌾 🤲🏻

शुभ दोपहर

कुछ समय पहले मुझे "एक्सेल के साथ मदद" करने के लिए कहा गया था, और फिर यह काम चालू हो गया, इसलिए पिछले कुछ महीनों में मैंने बहुत सी उपयोगी चीजें सीखी हैं, और मैं हाल के लेख की खोज में साझा करना चाहता हूं।

यह माना जाता है कि आप Visual Basic की मूल बातें जानते हैं। मैं फॉर्म या मॉड्यूल बनाने के बारे में बात नहीं करूंगा, यहां सिर्फ कोड उदाहरण हैं।

दृश्य मूल

विकल्प

सबसे पहले, VB में, सरणियों की शुरुआत सूचकांक 1 से हो सकती है, जो कई लोगों के लिए अजीब है, इसलिए आप मॉड्यूल की शुरुआत में लिख सकते हैं:

OPTION BASE 0

यह भी निर्धारित करने के लिए सिफारिश की है:

 OPTION EXPLICIT

इस मामले में, दुभाषिया को सभी चर की प्रारंभिक घोषणा की आवश्यकता होगी। चर घोषित करने की आवश्यकता है क्योंकि:

- वीबी उनके शिलालेख को याद रखेगा और अंतिम दर्ज किए गए विकल्प के लिए पूरे कोड में सही नहीं होगा;

- कभी-कभी त्रुटियां चर byRef के हस्तांतरण के साथ होती हैं, यदि उन्हें घोषित नहीं किया जाता है (अर्थात, आपको या तो एक चर घोषित करना चाहिए, या इसे byVal से पहले किसी फ़ंक्शन / प्रक्रिया में असाइन करना चाहिए)।

एक अन्य महत्वपूर्ण ऑपरेटर ON ERROR है। मैं विकल्प लाता हूं:

 ON ERROR RESUME NEXT '     ON ERROR GOTO label: ' ,   ,   label: ON EROR GOTO 0 '   .

भाषा सुविधाएँ

यद्यपि वीबी काफी सरल है, इसके सिंटैक्स के लिए प्रलेखन को पढ़ना उपयोगी है। उदाहरण के लिए, मुझे यह जानकर आश्चर्य हुआ कि आप SELECTs (स्विच का एनालॉग) में जटिल मान्यताओं को लिख सकते हैं:

 SELECT CASE parametr CASE 1: ' do something' CASE 3 to 5: ' do something else' CASE 6, 8, 9: ' do something funny' CASE ELSE: ' do do do' END SELECT

मैक्रो एक्सेलेरेशन

अक्सर मैक्रोज़ को लंबे समय तक चलने की आवश्यकता होती है, जिसे काफी कम किया जा सकता है। शुरुआत में और प्रत्येक संसाधन-गहन कार्य के अंत में, तैयार और समाप्त कॉल करें।

 Public Sub Prepare() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False Application.DisplayStatusBar = False Application.DisplayAlerts = False End Sub Public Sub Ended() Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True Application.DisplayStatusBar = True Application.DisplayAlerts = True End Sub

क्रम में:

1. स्क्रीन पर वस्तुओं को फिर से व्यवस्थित करना अक्षम करें ताकि कुछ भी झपके नहीं।

2. गणना बंद करें। ध्यान दें, यदि कार्य के बीच में मैक्रो बाधित है, तो गणना मैन्युअल मोड में रहेगी!

3. घटनाओं को संभालना नहीं है।

4. पृष्ठ सीमाओं को प्रदर्शित करना भी किसी कारण से मदद करता है।

5. स्टेटस लाइन में विभिन्न डेटा प्रदर्शित होते हैं, जो धीमा हो जाता है, बंद हो जाता है।

6. यदि आवश्यक हो तो। एक्सेल संदेशों को बंद कर देता है। उदाहरण के लिए, हम वर्कबुक करते हैं। ठीक है, एक्सेल पूछना चाहता है कि क्या परिवर्तन सहेजना है। जब यह पैरामीटर बंद हो जाता है, तो सभी उत्तर स्वचालित रूप से दिए जाएंगे (परिवर्तन सहेजे नहीं जाएंगे)।

यह समझना महत्वपूर्ण है कि VBA उपयोगकर्ता के समान सभी क्रियाएं करता है। इसलिए, पृष्ठ पैरामीटर सेट करने के लिए, प्रत्येक बार यह पैरामीटर विंडो को खोलता और बंद करता है। मैंने 10 शीट के लिए पैरामीटर सेट किया है, यह वास्तव में तेज़ नहीं है। इसलिए, हम यह करते हैं:

 If Sheets("01").PageSetup.PrintArea <> "A1:D5" Then Sheets("01").PageSetup.PrintArea = "A1:D5" If Sheets("02").PageSetup.PrintArea <> "A1:E8" Then Sheets("02").PageSetup.PrintArea = "A1:A1:E8"

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

 Dim tCell As Variant For Each tCell In Sheets("01").Range("P16:Q19").SpecialCells(xlCellTypeFormulas) If tCell.Interior.ColorIndex = xlColorIndexNone Then tCell.Locked = True tCell.Interior.Color = RGB(220, 230, 241) End If Next tCell

यह कोड निर्दिष्ट सीमा को स्कैन करता है, इसमें "विशेष सेल" का चयन करता है, इस मामले में सभी के पास सूत्र हैं (अर्थात, समान चिह्न के साथ शुरू होता है)। प्रत्येक सेल के लिए यह दिखता है, अगर इसे चित्रित नहीं किया गया है, तो इसे संरक्षित किया जाना चाहिए (नीचे देखें) और चित्रित किया गया है। ऐसा कोड बहुत तेज है।

किसी भी वैरिएबल के लिए जिसे आप एक पुस्तक, एक शीट, एक रेंज (सेल) सौंपने जा रहे हैं, पहले उसे वेरिएंट घोषित किया जाना चाहिए।

स्वाभाविक रूप से, यदि आपको कोशिकाओं में एक ही प्रकार के मूल्यों की आवश्यकता है, तो आपको उपयोगकर्ता को "स्ट्रेच" करने के लिए, कोशिकाओं की तरह ही स्वत: पूर्ण का उपयोग करने की आवश्यकता है।

 Sheets("01").Range("P15").AutoFill Sheets("01").Range("P15:Q15"), xlFillValues

दूसरी श्रेणी में पहले शामिल होना चाहिए, और दूसरा वैकल्पिक पैरामीटर ऑटोफिल के प्रकार को इंगित करता है।

पुस्तकों और घटनाओं को डाउनलोड करें

जब आप किताब खोलते हैं, तो हर बार एक प्रक्रिया शुरू हो जाती है।

 Private Sub workbook_open() Dim sh as Variant Prepare '   For Each sh In ActiveWorkbook.Worksheets With sh.PageSetup If .Orientation <> xlLandscape Then .Orientation = xlLandscape If .LeftMargin <> Application.CentimetersToPoints(0.5) Then .LeftMargin = Application.CentimetersToPoints(0.5) If .RightMargin <> Application.CentimetersToPoints(0.5) Then .RightMargin = Application.CentimetersToPoints(0.5) If .TopMargin <> Application.CentimetersToPoints(1.5) Then .TopMargin = Application.CentimetersToPoints(1.5) If .BottomMargin <> Application.CentimetersToPoints(0.5) Then .BottomMargin = Application.CentimetersToPoints(0.5) If .HeaderMargin <> Application.CentimetersToPoints(0) Then .HeaderMargin = Application.CentimetersToPoints(0) If .FooterMargin <> Application.CentimetersToPoints(0) Then .FooterMargin = Application.CentimetersToPoints(0) End With Next sh Ended End Sub

इस स्थिति में, प्रिंट सेटिंग्स (मार्जिन, ओरिएंटेशन) डिफ़ॉल्ट पर रीसेट हो जाती हैं। आप अन्य इनिशियलाइज़ेशन भी कर सकते हैं। यह महत्वपूर्ण है कि यदि मैक्रोज़ अक्षम हैं, तो कुछ भी नहीं होगा। यदि मैक्रोज़ के बारे में चेतावनी वाला एक पैनल एक्सेल के शीर्ष पर क्रॉल हो जाता है और उपयोगकर्ता "सक्षम करें" पर क्लिक करता है, तो उस समय वर्कबुक_ोपेन () प्रक्रिया निष्पादित की जाएगी।

उपलब्ध घटनाओं की सूची VB संपादक के शीर्ष पर देखी जा सकती है। उदाहरण के लिए, मैंने चेंज इवेंट पर एक चेक किया, जहां सेल जिसमें परिवर्तन स्थित थे, और यदि यह वांछित सीमा है, तो पुराने और नए मूल्यों के साथ एक लॉग लिखा गया था।

सुरक्षा

सबसे पहले, मैं तुरंत ध्यान देता हूं कि एमएस ऑफिस कंप्यूटर पर मैक्रोज़ को निष्पादित नहीं करता है, जहां पुस्तक एन्क्रिप्ट होने पर उसे एंटीवायरस नहीं मिला। उन कंप्यूटरों पर सामना किया गया जहां एंटीवायरस था, लेकिन जाहिर तौर पर विंडोज एक्सपी को इसके बारे में पता नहीं था।

एक और एंटीवायरस कार्य के साथ अजीब रूप से हस्तक्षेप कर सकता है, त्रुटियों का कारण बनता है जो पूरी तरह से समझाने योग्य नहीं हैं। उन्होंने आईटी विशेषज्ञों को दिखाया, उन्होंने कहा कि ठीक है, उन्होंने कुछ किया है, मुझे नहीं पता।

तो, हमें पुस्तक की सुरक्षा करने की आवश्यकता है ताकि इनपुट केवल आवश्यक कोशिकाओं (सूत्र और शीर्षकों को बदला नहीं जा सके) में अनुमति दी जाए। सबसे पहले, आपको संबंधित कोशिकाओं को "असुरक्षित" बनाने की आवश्यकता है। ऐसा करने के लिए, इनमें से एक करें:

- रेंज का चयन करें, कोशिकाओं का प्रारूप, "ब्लॉक सेल" को अनचेक करें;

- त्वरित पहुँच के लिए "ब्लॉक सेल" बटन को प्रदर्शित करें और इसे क्लिक करें, यह समझने के लिए इसे देखने के लिए बहुत सुविधाजनक है कि सेल संरक्षित है या नहीं;

- और यह तीसरे विकल्प की जांच करने के लिए उपयोगी है - एक मैक्रो लिखने के लिए जो वांछित कोशिकाओं से सुरक्षा को हटा देता है।

अगला, आपको शीट की रक्षा करने की आवश्यकता है। रिव्यू टैब पर एक ऐसा बटन होता है। खिड़की आपको एक पासवर्ड दर्ज करने और अपवाद सेट करने के लिए कहती है (उपयोगकर्ता क्या कर सकता है)। दुर्भाग्य से, अपवादों की सूची पर्याप्त नहीं है। सबसे कष्टप्रद बात यह है कि आप स्तंभों / पंक्तियों के समूहों को मोड़ने / विस्तार करने की अनुमति नहीं दे सकते। इसलिए, हम इस तरह से कार्य करते हैं, हम पुस्तक डाउनलोड करने के लिए लिखते हैं:

 myPassword = "123" For Each sh In ActiveWorkbook.Worksheets sh.Unprotect (myPassword) sh.EnableOutlining = True sh.Protect Password:=myPassword, _ UserInterfaceOnly:=True, AllowSorting:=True, _ AllowFiltering:=True, AllowFormattingRows:=True, _ AllowFormattingColumns:=True, DrawingObjects:=False Next sh

अगली शारीरिक रेखा पर अंडरस्कोर तार्किक रेखा को जारी रखता है। तो यहाँ हम हैं:

1. हटाया गया संरक्षण।

2. समूहबद्धता।

3. सुरक्षा रखें, जबकि:

- केवल उपयोगकर्ता से सुरक्षा, मैक्रोज़ का पूर्ण उपयोग जारी है (!), यह अत्यंत महत्वपूर्ण है;

- पंक्तियों / स्तंभों (ऊँचाई / चौड़ाई) को छँटाई, छानने और प्रारूपित करने की अनुमति;

- इस मामले में DrawingObject नोटों से कोशिकाओं तक सुरक्षा हटाता है, शायद कुछ और।

यहाँ हम आश्चर्य की एक जोड़ी भर में आते हैं। सबसे पहले, सभी मैक्रोज़ उस तरह से भी काम नहीं करेंगे। ज्ञात बग, आपने कुछ भी नहीं किया है। आप एक स्ट्रिंग नहीं डाल सकते, उदाहरण के लिए। मुझे वहीं शूटिंग करनी है और संरक्षण देना है। यदि इस समय "हमलावर" ctrl + ब्रेक दबाता है, तो सुरक्षा बंद हो जाएगी।

दूसरी बात, मान लीजिए कि आप किसी भी तरह से पंक्तियों (एसेसडेल्ट्रिंग्स) को हटा नहीं सकते हैं, जिसमें संरक्षित सेल हैं, कम से कम एक। अधिक जानकारी यहाँ ।

समाधान (बैसाखी) को हटाने के लिए एक बटन या कीबोर्ड शॉर्टकट जोड़ना है। उसी समय, आप जांच सकते हैं कि उपयोगकर्ता वह नहीं हटाता है जो आवश्यक नहीं है। Workbook_open में, जोड़ें:

 Application.OnKey "+{DELETE}", ".DelSelectedRow"

अब शिफ्ट + डिलीट को दबाने पर प्रक्रिया को बुलाया जाएगा।

 Sub DelSelectedRow() If Selection.Rows.Count = 1 Then If Selection.Parent.Name = "01" And Selection.Cells.Count >= 1000 Then If Selection.row > 13 And Selection.row < 50 Then Selection.Delete End If End If End If End Sub

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

निष्कर्ष

वीबीए एक बहुत छोटी चीज है जो आपको एमएस ऑफिस में पहाड़ों को ढहाने की अनुमति देती है। कई व्यवसाय वर्षों से एक्सेल मॉडल का उपयोग कर रहे हैं, और यदि वे अच्छी तरह से किए जाते हैं, तो सब कुछ काम करता है।

वह खुद VBA का अध्ययन करने के लिए उपयुक्त है, सबसे पहले वहां अच्छी मदद है। उदाहरण के लिए, प्रोटेक्ट विधि में हल किए जा सकने वाले सभी विकल्पों का पता लगाने के लिए, F1 दबाएं, प्रोटेक्ट करें, एंटर करें। और वोइला।

दूसरे, आप मैक्रो लिखकर और उसके कोड को देखकर मैन्युअल रूप से आवश्यक कार्रवाई कर सकते हैं। कोड भयानक होगा (उदाहरण के लिए, पृष्ठ के मापदंडों को बदलते समय, मैक्रो सभी मापदंडों और क्षेत्रों के मूल्यों को रिकॉर्ड करेगा, न कि केवल एक जिसे आपने बदला है), लेकिन उत्तर होंगे। हालांकि, उदाहरण के लिए, .ऑटोफिट, जो तब दर्ज किया जाता है जब सेल की ऊंचाई सामग्री के अनुसार बदल जाती है (बाईं ओर सीमा पर डबल-क्लिक करें), वास्तव में काम नहीं करता है।

मैं सुझाव देता हूं कि पारखी लोग अपना अनुभव साझा करें, टिप्पणियों में सलाह दें। धन्यवाद, आप के लिए सफल विकास।

एक्सेल में VBA के साथ काम करने के लिए कुछ सुझाव