VBA Excel: नमूना कार्यक्रम। एक्सेल मैक्रोज़

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

VBA क्या है

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

विशेषज्ञ इसके फायदे बताते हैंविकास की तुलनात्मक आसानी। जैसा कि अभ्यास से पता चलता है, यहां तक ​​कि ऐसे उपयोगकर्ता जिनके पास पेशेवर प्रोग्रामिंग कौशल नहीं है, वे VBA की मूल बातें सीख सकते हैं। VBA की ख़ासियत में कार्यालय अनुप्रयोगों के वातावरण में स्क्रिप्ट का निष्पादन शामिल है।

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

वस्तुएँ, संग्रह, गुण और विधियाँ

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

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

"संग्रह" की अवधारणा के लिए, यह एक ही वर्ग की वस्तुओं का एक समूह है, जो रिकॉर्ड में चार्टऑर्जेक्ट्स के रूप में है। इसके व्यक्तिगत तत्व भी वस्तुएं हैं।

अगली अवधारणा गुण है। वे किसी भी वस्तु की एक आवश्यक विशेषता हैं। उदाहरण के लिए, रेंज के लिए, यह वैल्यू या फॉर्मूला है।

विधियाँ यह दिखाती हैं कि क्या आवश्यक हैकरने के लिए। वीबीए में कोड लिखते समय, उन्हें एक बिंदु से वस्तु से अलग किया जाना चाहिए। उदाहरण के लिए, जैसा कि बाद में दिखाया जाएगा, Excel में प्रोग्रामिंग करते समय सेल (1,1) .Select कमांड का बहुत बार उपयोग किया जाता है। इसका मतलब है कि आपको निर्देशांक (1,1) के साथ एक सेल का चयन करना होगा अर्थात। A1।

चयन ।ClearContents अक्सर इसके साथ प्रयोग किया जाता है। इसके निष्पादन का अर्थ है चयनित सेल की सामग्री को साफ़ करना।

कैसे शुरू करें

पहला कदम एक फ़ाइल बनाना है और इसे एक नाम के साथ सहेजना है और "एक्सेल मैक्रो-सक्षम वर्कबुक" का चयन करना है।

फिर आपको वीबी एप्लिकेशन पर जाने की आवश्यकता है, जिसके लिए यह कुंजी "ऑल्ट" और "एफ 11" के संयोजन का उपयोग करने के लिए पर्याप्त है। आगे की:

• विंडो के शीर्ष पर स्थित मेनू बार में, एक्सेल आइकन के बगल में स्थित आइकन पर क्लिक करें;
• टीम का चयन करें Mudule;
• फ्लॉपी डिस्क आइकन पर क्लिक करके सहेजें;
• लिखो, चलो एक कोड स्केच कहते हैं।

यह इस तरह दिख रहा है:

उप कार्यक्रम ()

“हमारी पोस्ट

अंत उप

कृपया ध्यान दें कि लाइन "हमारा कोड" एक अलग रंग (हरा) में हाइलाइट किया जाएगा। इसका कारण लाइन की शुरुआत में एपोस्ट्रोफ में है, जिसका अर्थ है कि एक टिप्पणी इस प्रकार है।

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

एक्सेल मैक्रोज़

यह नाम कार्यक्रमों को छुपाता हैअनुप्रयोग भाषा के लिए Visual Basic में लिखा गया है। इस प्रकार, एक्सेल में प्रोग्रामिंग सही कोड के साथ मैक्रोज़ बनाने के बारे में है। इस सुविधा के लिए धन्यवाद, Microsoft स्प्रेडशीट प्रोसेसर एक विशेष उपयोगकर्ता की आवश्यकताओं के अनुकूल स्वयं-विकसित करता है। अब जब आप जानते हैं कि मैक्रो लिखने के लिए मॉड्यूल कैसे बनाया जाता है, तो आप विशिष्ट VBA एक्सेल उदाहरण देखना शुरू कर सकते हैं। सबसे अल्पविकसित कोड के साथ शुरू करना सबसे अच्छा है।

उदाहरण 1

टास्क: एक प्रोग्राम लिखें जो एक सेल की सामग्री के मूल्य को कॉपी करेगा और फिर दूसरे को लिखेगा।

इसके लिए:

• "दृश्य" टैब खोलें;
• मैक्रोज़ आइकन पर जाएं;
• "रिकॉर्ड मैक्रो" पर क्लिक करें;
• खुले हुए फॉर्म को भरें।

सादगी के लिए, "मैक्रो नाम" फ़ील्ड में "मैक्रो 1" को छोड़ दें, और डालें, उदाहरण के लिए, "कीबोर्ड शॉर्टकट" फ़ील्ड में hh (इसका मतलब है कि आप ब्लिट्ज कमांड "Ctrl + h") के साथ कार्यक्रम शुरू कर सकते हैं। एंटर दबाए।

अब जबकि मैक्रो रिकॉर्डिंग शुरू हो चुकी है,किसी भी सेल की सामग्री को दूसरे में कॉपी करें। मूल आइकन पर लौटता है। "रिकॉर्ड मैक्रो" पर क्लिक करें। इस क्रिया का अर्थ है कार्यक्रम का अंत।

आगे की:

• फिर से लाइन "मैक्रोज़" पर जाएं;
• सूची में "मैक्रो 1" का चयन करें;
• "रन" पर क्लिक करें (कीबोर्ड शॉर्टकट "Ctrl + hh" शुरू करके वही कार्रवाई शुरू की गई है)।

परिणाम वह क्रिया है जो मैक्रो की रिकॉर्डिंग के दौरान की गई थी।

यह देखने के लिए समझ में आता है कि कोड कैसा दिखता है।ऐसा करने के लिए, लाइन "मैक्रोज़" पर वापस जाएं और "बदलें" या "दर्ज करें" पर क्लिक करें। नतीजतन, वे खुद को एक VBA वातावरण में पाते हैं। दरअसल, मैक्रो कोड खुद सब मैक्रो 1 () और एंड सब के बीच स्थित है।

यदि नकल की गई थी, उदाहरण के लिए, सेसेल A1 से सेल C1 तक, फिर कोड की एक पंक्ति रेंज ("C1") की तरह दिखाई देगी। अनुवादित, यह "रेंज (" C1 ") जैसा दिखता है। चुनें", दूसरे शब्दों में, यह V1 एक्सेल में बदलकर सेल 1 में बदल जाता है।

ActiveSheet.Paste आदेश कोड के सक्रिय भाग को समाप्त करता है। इसका अर्थ है चयनित सेल की सामग्री लिखना (इस मामले में A1) चयनित सेल C1 के लिए।

उदाहरण 2

VBA लूप्स आपको एक्सेल में विभिन्न मैक्रोज़ बनाने में मदद करते हैं।

VBA लूप्स आपको विभिन्न मैक्रोज़ बनाने में मदद करते हैं। मान लीजिए कि कोई फ़ंक्शन y = x + x है² + 3x³ - cos (x)। इसका ग्राफ पाने के लिए एक मैक्रो बनाना आवश्यक है। यह केवल VBA लूप का उपयोग करके किया जा सकता है।

फ़ंक्शन तर्क के प्रारंभिक और अंतिम मूल्य के लिए, X1 = 0 और x2 = 10 लें। इसके अलावा, आपको एक निरंतर दर्ज करना होगा - तर्क को बदलने के चरण के लिए मूल्य और काउंटर के लिए प्रारंभिक मूल्य।

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

उप प्रोग्राम ()

एक्स 1 = 1

x2 = 10

शग = 0.1

मैं = १

जब तक X1 <x2 (लूप तब तक चलेगा जब तक एक्सप्रेशन X1 <x2 सही है)

y = X1 + X1 ^ 2 + 3 * X1 ^ 3 - कॉस (X1)

कक्ष (i, 1) .Value = X1 (X1 मान को निर्देशांक के साथ सेल में लिखा गया है (i, 1))

कक्ष (i, 2)

i = i + 1 (काउंटर सक्रिय है);

X1 = X1 + shag (स्टेप साइज से तर्क बदल जाता है);

लूप

अंत उप।

एक्सेल में इस मैक्रो को चलाने के परिणामस्वरूप, हमें दो कॉलम मिलते हैं, जिनमें से पहले में x के लिए मान शामिल हैं, और दूसरा - y के लिए।

फिर एक्सेल के लिए एक मानक तरीके से उनका उपयोग करके एक ग्राफ तैयार किया जाता है।

उदाहरण 3

VBA Excel 2010 में छोरों को लागू करने के लिए, अन्य संस्करणों की तरह, पहले से ही दिए गए निर्माण के दौरान, फॉर का उपयोग किया जाता है।

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

पहले आपको ऊपर बताए अनुसार एक मैक्रो बनाने की आवश्यकता है। अगला, हम कोड को स्वयं लिखते हैं। हम मानते हैं कि हम 10 कोशिकाओं के लिए मूल्यों में रुचि रखते हैं। कोड इस तरह दिखता है।

I = 1 से 10 के लिए अगला

कमांड को "मानव" भाषा में "1 से 10 तक दोहराएं।

यदि कार्य वर्गों के साथ एक स्तंभ प्राप्त करना है, उदाहरण के लिए, 1 से 11 तक की सभी विषम संख्याएँ, तो हम लिखते हैं:

I = 1 से 10 के लिए चरण 1 अगला।

यहाँ कदम एक कदम है। इस मामले में, यह दो के बराबर है। डिफ़ॉल्ट रूप से, लूप में इस शब्द की अनुपस्थिति का मतलब है कि चरण एक है।

प्राप्त परिणामों के साथ कोशिकाओं में बचाया जाना चाहिएसंख्या (i, 1)। फिर, चरण आकार द्वारा i बढ़ने के साथ चक्र के प्रत्येक प्रारंभ में, पंक्ति संख्या भी स्वचालित रूप से बढ़ जाएगी। इस प्रकार, कोड को अनुकूलित किया जाएगा।

सामान्य तौर पर, कोड ऐसा दिखेगा:

उप कार्यक्रम ()

I = 1 से 10 चरण 1 के लिए (आप बस I = 1 से 10 के लिए लिख सकते हैं)

कक्ष (i, 1) .Value = i ^ 2 (अर्थात वर्ग का मान सेल में लिखा गया है (i, 1))

अगला (कुछ अर्थों में एक काउंटर की भूमिका निभाता है और लूप की एक और शुरुआत का मतलब है)

अंत उप।

यदि सब कुछ सही तरीके से किया जाता है, जिसमें मैक्रो को रिकॉर्ड करना और चलाना शामिल है (ऊपर दिए गए निर्देशों को देखें), तो हर बार जब इसे बुलाया जाता है, तो निर्दिष्ट आकार का एक कॉलम प्राप्त होगा (इस मामले में, जिसमें 10 सेल शामिल हैं)।

उदाहरण 4

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

आइए एक विशिष्ट मामले पर विचार करें। मान लीजिए कि आपको एक्सेल के लिए एक मैक्रो बनाने की आवश्यकता है ताकि निर्देशांक (1,1) के साथ सेल लिखा जाए:

1 यदि तर्क सकारात्मक है;

0 यदि तर्क शून्य है;

-1 अगर तर्क नकारात्मक है।

एक्सेल के लिए इस तरह के मैक्रो का निर्माण मानक तरीके से शुरू होता है, Alt और F11 गर्म कुंजियों का उपयोग करके। फिर निम्नलिखित कोड लिखा है:

उप कार्यक्रम ()

x = कक्ष (1, 1) .वायु (यह आदेश x निर्देशांक पर सेल सामग्री का मान प्रदान करता है (1, 1))

यदि x> 0 तब सेल (1, 1) ।वैल्यू = 1

यदि x = 0 तो सेल (1, 1) ।वैल्यू = 0

यदि x <0 तो कोशिकाएं (1, 1) .Value = -1

अंत उप।

यह मैक्रो को चलाने और एक्सेल में तर्क के लिए वांछित मूल्य प्राप्त करने के लिए बनी हुई है।

VBA फ़ंक्शन

जैसा कि आपने देखा होगा, प्रोग्रामिंग मेंMicrosoft का सबसे प्रसिद्ध स्प्रेडशीट प्रोसेसर वह सब कठिन नहीं है। खासकर यदि आप सीखते हैं कि VBA फ़ंक्शन का उपयोग कैसे करें। कुल मिलाकर, एक्सेल और वर्ड में एप्लिकेशन लिखने के लिए विशेष रूप से बनाई गई इस प्रोग्रामिंग भाषा में लगभग 160 फ़ंक्शन हैं। उन्हें कई बड़े समूहों में विभाजित किया जा सकता है। यह:

• गणितीय कार्य। उन्हें तर्क पर लागू करते हुए, उन्हें कॉशन, प्राकृतिक लघुगणक, पूर्णांक भाग, आदि का मान मिलता है।
• वित्तीय कार्य। एक्सेल में उनकी उपस्थिति और प्रोग्रामिंग का उपयोग करने के लिए धन्यवाद, आप लेखांकन और वित्तीय गणना के लिए प्रभावी उपकरण प्राप्त कर सकते हैं।
• अर्रे प्रसंस्करण कार्य। इनमें ऐरे, इसराए शामिल हैं; LBound; UBound।
• VBA एक्सेल स्ट्रिंग के लिए कार्य करता है।यह एक काफी बड़ा समूह है। इसमें शामिल है, उदाहरण के लिए, अंतरिक्ष एक पूर्णांक तर्क के बराबर कई रिक्त स्थान के साथ एक स्ट्रिंग बनाने के लिए कार्य करता है, या एएनएसआई कोड में वर्णों का अनुवाद करने के लिए एस्क। उनमें से सभी का व्यापक रूप से उपयोग किया जाता है और आपको एक्सेल में पंक्तियों के साथ काम करने की अनुमति मिलती है, जिससे ऐसे अनुप्रयोग बनते हैं जो इन तालिकाओं के साथ काम को सुविधाजनक बनाते हैं।
• डेटा प्रकार रूपांतरण कार्य। उदाहरण के लिए, सीवीआर एक्सप्रेशन तर्क का मान वैरिएंट डेटा प्रकार में परिवर्तित करके लौटाता है।
• तिथियों के साथ काम करने के लिए कार्य।वे एक्सेल की मानक क्षमताओं का काफी विस्तार करते हैं। उदाहरण के लिए, वीकडेनेम फ़ंक्शन सप्ताह के दिन का नाम (पूर्ण या आंशिक) उसकी संख्या से लौटाता है। टाइमर और भी अधिक उपयोगी है। यह आपको कुछ सेकंडों की संख्या प्रदान करता है जो आधी रात से दिन में एक विशिष्ट बिंदु तक पहुंच गए हैं।
• एक संख्यात्मक तर्क को विभिन्न संख्या प्रणालियों में परिवर्तित करने के लिए कार्य। उदाहरण के लिए, ऑक्टा ऑक्टल प्रतिनिधित्व में एक संख्या को आउटपुट करता है।
• स्वरूपण कार्य। इनमें से सबसे महत्वपूर्ण प्रारूप है। यह प्रारूप वर्णन में दिए गए निर्देशों के अनुसार स्वरूपित एक वेरिएंट देता है।
• और आगे

इन कार्यों के गुणों और उनके आवेदन का अध्ययन करने से एक्सेल के दायरे का काफी विस्तार होगा।

उदाहरण 5

आइए अधिक जटिल समस्याओं को हल करने के लिए आगे बढ़ने का प्रयास करें। उदाहरण के लिए:

उद्यम की लागत के वास्तविक स्तर की रिपोर्ट के एक पेपर दस्तावेज़ को देखते हुए। आवश्यक:

• एक्सेल स्प्रेडशीट प्रोसेसर का उपयोग करके अपने टेम्पलेट भाग को विकसित करने के लिए;
• एक VBA प्रोग्राम बनाएं जो प्रारंभिक डेटा को भरने के लिए अनुरोध करेगा, आवश्यक गणना करेगा और उन्हें टेम्पलेट की संबंधित कोशिकाओं में भर देगा।

आइए एक उपाय पर विचार करें।

खाका निर्माण

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

चर

टेम्पलेट के स्वचालित भरने के लिए एक कार्यक्रम लिखने के लिए, आपको पदनामों का चयन करना होगा। उनका उपयोग चर के लिए किया जाएगा:

• NN- वर्तमान तालिका पंक्ति की संख्या;
• टीपी और टीएफ - योजनाबद्ध और वास्तविक कारोबार;
• एसएफ और एसपी - वास्तविक और नियोजित लागत;
• आईपी ​​और आईएफ - लागतों की योजनाबद्ध और वास्तविक स्तर।

चलो एक ही अक्षर से निरूपित करते हैं, लेकिन "उपसर्ग" इटोग के साथ, इस कॉलम के लिए कुल का संचय। उदाहरण के लिए, इटोगटीपी - "अनुमानित टर्नओवर" नामक एक टेबल कॉलम को संदर्भित करता है।

VBA प्रोग्रामिंग का उपयोग करके समस्या का समाधान करना

प्रस्तुत पदनामों का उपयोग करते हुए, हम विचलन के लिए सूत्र प्राप्त करते हैं। यदि आप% में गणना करना चाहते हैं, तो हमारे पास (F - P) / P * 100 है, और कुल में - (F - P)।

इन गणनाओं के परिणामों को एक्सेल तालिका में उपयुक्त कोशिकाओं में सीधे प्रवेश किया जा सकता है।

तथ्य और पूर्वानुमान के परिणामों के लिए, वे सूत्र IogP = ItogP + P और ItogF = ItogF + F का उपयोग करके प्राप्त किए जाते हैं।

विचलन के लिए, उपयोग करें = (ItogF - ItogP) / ItogP * 100, यदि गणना एक प्रतिशत के रूप में की जाती है, और कुल मूल्य के मामले में - (ItogF - ItogP)।

परिणाम तुरंत फिर से उपयुक्त कोशिकाओं को लिखे गए हैं, इसलिए उन्हें चर को निर्दिष्ट करने की कोई आवश्यकता नहीं है।

बनाए गए प्रोग्राम को शुरू करने से पहले, आपको "Report1.xls" नाम के तहत, उदाहरण के लिए, कार्यपुस्तिका को सहेजना होगा।

"रिपोर्ट तालिका बनाएँ" कुंजी की आवश्यकता हैशीर्ष लेख जानकारी दर्ज करने के बाद सिर्फ 1 बार दबाएँ। जागरूक होने के लिए अन्य नियम हैं। विशेष रूप से, तालिका में प्रत्येक गतिविधि के लिए मान दर्ज करने के बाद "रो जोड़ें" बटन को हर बार दबाया जाना चाहिए। सभी डेटा दर्ज करने के बाद, आपको "फिनिश" बटन पर क्लिक करना होगा और फिर "एक्सेल" विंडो पर स्विच करना होगा।

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