सभी का अभिवादन।

इस पोस्ट में मैं आपको बताऊंगा कि माइक्रोसॉफ्ट एक्सेल 2007/2010 में VBA क्या है और इसके साथ कैसे काम करना है (पुराने संस्करणों के लिए, केवल इंटरफ़ेस बदलता है - कोड एक अलग दिनचर्या को स्वचालित करने के लिए सबसे अधिक संभावना होगा)।

VBA (Visual Basic for Applications) विजुअल बेसिक का एक सरलीकृत संस्करण है जो कई Microsoft Office उत्पादों में बनाया गया है। यह आपको किसी विशिष्ट दस्तावेज़ की फ़ाइल में सीधे प्रोग्राम लिखने की अनुमति देता है। आपको विभिन्न आईडीई स्थापित करने की आवश्यकता नहीं है - डिबगर सहित सब कुछ पहले से ही एक्सेल में है।

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

मुझे तुरंत कहना होगा - अन्य भाषाओं में लिखना (C ++ / Delphi / PHP) भी संभव है, लेकिन आपको कार्यालय फ़ाइलों को पढ़ना, संशोधित करना और लिखना सीखना होगा - आप दस्तावेजों में एम्बेड नहीं कर सकते। और Microsoft इंटरफेस COM के माध्यम से काम करता है। आपको पूरे आतंक को समझने के लिए, यहाँ COM का उपयोग करके हैलो वर्ल्ड बनाया गया है ।

इसलिए, अफसोस, हम Visual Basic सीखेंगे।

थोड़ी तैयारी और समस्या बयान

तो चलिए चलते हैं। एक्सेल खोलें।

आरंभ करने के लिए, आइए रिबन में डेवलपर पैनल जोड़ें। इसमें फॉर्म बनाने के लिए बटन, टेक्स्ट फ़ील्ड और अन्य तत्व शामिल हैं।

एक टैब दिखाई दिया है।

अब आइए विचार करें कि हम किस उदाहरण पर VBA का अध्ययन करेंगे। हाल ही में, मुझे एक मूल्य सूची को अच्छी तरह से व्यवस्थित करने की आवश्यकता थी जो एक तालिका की तरह दिखती थी। हम Google पर जाते हैं, "मूल्य सूची" टाइप करते हैं और जो भी इस तरह से सजाया जाता है उसे डाउनलोड करते हैं (विज्ञापन के लिए इस पर विचार न करें, कृपया):

यही है, यह आवश्यक है कि कम से कम दो समूह हों जिनके द्वारा सामान को जोड़ा जा सकता है (हमारे मामले में, ये टाइप और निर्माता होंगे - इस क्रम में)। मेरे प्रस्तावित एल्गोरिदम के लिए सही ढंग से काम करने के लिए, सामानों को क्रमबद्ध करें ताकि एक समूह से सामान एक पंक्ति में हो (पहले प्रकार , फिर निर्माता द्वारा)।

परिणाम हम प्राप्त करना चाहते हैं कुछ इस तरह दिखता है:

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

कोड

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

और यहाँ हम VB विकास के वातावरण में हैं। इसे "मेन्यू टेक्स्ट" / "कोड देखें" कमांड के साथ संदर्भ मेनू से भी बुलाया जा सकता है।

यहाँ एक विंडो है जिसमें एक स्टब प्रक्रिया है। आप इसे तैनात कर सकते हैं। कोड कुछ इस तरह दिखना चाहिए:

Sub FormatPrice() End Sub

हैलो वर्ल्ड लिखते हैं:

Sub FormatPrice() MsgBox "Hello World!" End Sub

और या तो बटन पर क्लिक करके शुरू करें (पहले से चयन हटाकर), या संपादक से सीधे F5 दबाकर।

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

सिंटेक्स उदाहरण

' .    
      

        
        
        
      

     '   VBA  
      

        
        
        
      

     Sub foo(a As String , b As String ) 
      

        
        
        
      

     ' Exit Sub '   "  " 
      

        
        
        
      

     MsgBox a + ";" + b 
      

        
        
        
      

     End Sub 
      

        
        
        
      

     
      

        
        
        
      

     ' .  Integer 
      

        
        
        
      

     Function LengthSqr(x As Integer , y As Integer ) As Integer 
      

        
        
        
      

     ' Exit Function 
      

        
        
        
      

     LengthSqr = x * x + y * y 
      

        
        
        
      

     End Function 
      

        
        
        
      

     
      

        
        
        
      

     Sub FormatPrice() 
      

        
        
        
      

     Dim s1 As String , s2 As String 
      

        
        
        
      

     s1 = "str1" 
      

        
        
        
      

     s2 = "str2" 
      

        
        
        
      

     If s1 <> s2 Then 
      

        
        
        
      

     foo "123" , "456" '      
      

        
        
        
      

     End If 
      

        
        
        
      

     
      

        
        
        
      

     Dim res As sTRING '   VB  . ,    
      

        
        
        
      

     Dim i As Integer 
      

        
        
        
      

     '       
      

        
        
        
      

     For i = 1 To 10 
      

        
        
        
      

     res = res + CStr(i) '     String 
      

        
        
        
      

     If i = 5 Then Exit For 
      

        
        
        
      

     Next i 
      

        
        
        
      

     
      

        
        
        
      

     Dim x As Double 
      

        
        
        
      

     x = Val( "1.234" ) '   
      

        
        
        
      

     x = x + 10 
      

        
        
        
      

     MsgBox x 
      

        
        
        
      

     
      

        
        
        
      

     On Error Resume Next '   -    
      

        
        
        
      

     x = 5 / 0 
      

        
        
        
      

     MsgBox x 
      

        
        
        
      

     
      

        
        
        
      

     On Error GoTo Err '      Err 
      

        
        
        
      

     x = 5 / 0 
      

        
        
        
      

     MsgBox "OK!" 
      

        
        
        
      

     GoTo ne 
      

        
        
        
      

     
      

        
        
        
      

     Err: 
      

        
        
        
      

     MsgBox "Err!" 
      

        
        
        
      

     
      

        
        
        
      

     ne: 
      

        
        
        
      

     On Error GoTo 0 '    
      

        
        
        
      

     
      

        
        
        
      

     '  ,   
      

        
        
        
      

     Do While True 
      

        
        
        
      

     Exit Do 
      

        
        
        
      

     
      

        
        
        
      

     Loop 'While True 
      

        
        
        
      

     Do 'Until False 
      

        
        
        
      

     Exit Do 
      

        
        
        
      

     Loop Until False 
      

        
        
        
      

     '     ,     ,  . 
      

        
        
        
      

     ' Val    Integer 
      

        
        
        
      

     Select Case LengthSqr(Len( "abc" ), Val( "4" )) 
      

        
        
        
      

     Case 24 
      

        
        
        
      

     MsgBox "0" 
      

        
        
        
      

     Case 25 
      

        
        
        
      

     MsgBox "1" 
      

        
        
        
      

     Case 26 
      

        
        
        
      

     MsgBox "2" 
      

        
        
        
      

     End Select 
      

        
        
        
      

     
      

        
        
        
      

     '  . 
      

        
        
        
      

     '      ReDim (Preserve) - . google 
      

        
        
        
      

     Dim arr(1 to 10, 5 to 6) As Integer 
      

        
        
        
      

     arr(1, 6) = 8 
      

        
        
        
      

     
      

        
        
        
      

     Dim coll As New Collection 
      

        
        
        
      

     Dim coll2 As Collection 
      

        
        
        
      

     coll.Add "item" , "key" 
      

        
        
        
      

     Set coll2 = coll '       Set 
      

        
        
        
      

     MsgBox coll2( "key" ) 
      

        
        
        
      

     Set coll2 = New Collection 
      

        
        
        
      

     MsgBox coll2.Count 
      

        
        
        
      

     End Sub

रैक -1। आईडीई (अंग्रेजी एक्सेल में) से कोड कॉपी करते समय, सभी पाठ 1252 लैटिन -1 में परिवर्तित हो जाते हैं। इसलिए, यदि आप रूसी टिप्पणियों को बचाना चाहते हैं, तो आपको मगरमच्छों को लैटिन -1 के रूप में सहेजने की आवश्यकता है, और फिर 1251 में खोलें।

रेक २। क्योंकि वीबी आपको अघोषित चर का उपयोग करने की अनुमति देता है, मैं हमेशा कोड की शुरुआत में (सभी प्रक्रियाओं से पहले) विकल्प स्पष्ट करता हूं। यह निर्देश दुभाषिया को अपने आप से चर शुरू करने से रोकता है।

रेक ३। वैश्विक चर केवल पहले फ़ंक्शन / प्रक्रिया से पहले घोषित किए जा सकते हैं। स्थानीय - प्रक्रिया / कार्य में कहीं भी।

कुछ और अतिरिक्त सुविधाएँ जो काम आ सकती हैं: InPos , Mid , Trim , LBound , UBound । इसके अलावा, कार्यों के संचालन / उनके मापदंडों के बारे में सभी सवालों के जवाब MSDN पर प्राप्त किए जा सकते हैं।

मुझे उम्मीद है कि यह आपके लिए कोड से डरने और कंप्यूटर विज्ञान पर किसी तरह का होमवर्क लिखने के लिए पर्याप्त है। पोस्ट के दौरान मैं आपको नए डिजाइनों से परिचित कराऊंगा।

हम एक्सेल के तहत बहुत कोड करते हैं

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

Sub FormatPrice() Sheets( "result" ).Cells.Clear Sheets( "data" ).Activate End Sub

सेल रेंज के साथ काम करें

एक्सेल VBA में सभी काम सेल रेंज के साथ किया जाता है। वे रेंज फ़ंक्शन द्वारा बनाए जाते हैं और टाइप रेंज की एक वस्तु लौटाते हैं। उसके पास डेटा और / या डिज़ाइन के साथ काम करने के लिए आवश्यक सब कुछ है। वैसे, शीट की सेल संपत्ति रेंज भी है ।

रेंज के उदाहरण

Sheets( "result" ).Activate 
      

        
        
        
      

     Dim r As Range 
      

        
        
        
      

     Set r = Range( "A1" ) 
      

        
        
        
      

     r.Value = "123" 
      

        
        
        
      

     Set r = Range( "A3,A5" ) 
      

        
        
        
      

     r.Font.Color = vbRed 
      

        
        
        
      

     r.Value = "456" 
      

        
        
        
      

     Set r = Range( "A6:A7" ) 
      

        
        
        
      

     r.Value = "=A1+A3"

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

अगली पंक्ति से गिने गए समूह।
हम प्राथमिकता के क्रम में सभी समूहों से गुजरते हैं (पहले, बड़े वाले)
1. यदि वर्तमान समूह मेल नहीं खाता है, तो AddGroup (i, name) प्रक्रिया को कॉल करें, जहां मैं समूह संख्या (वर्तमान संख्या से अधिकतम तक) हूं , नाम उसका नाम है। न केवल हमारे हेडर, बल्कि सभी छोटे वाले बनाने के लिए कई कॉल की आवश्यकता होती है।
सभी आवश्यक हेडर खींचने के बाद, एक और लाइन बनाएं और इसे डेटा से भरें।

कार्य को सरल बनाने के लिए, मैं निम्नलिखित कमी कार्यों को परिभाषित करने की सलाह देता हूं:

Function GetCol(Col As Integer ) As String GetCol = Chr(Asc( "A" ) + Col) End Function Function GetCellS(Sheet As String , Col As Integer , Row As Integer ) As Range Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row)) End Function Function GetCell(Col As Integer , Row As Integer ) As Range Set GetCell = Range(GetCol(Col) + CStr(Row)) End Function

इसके बाद, हम वैश्विक चर "वर्तमान लाइन" को परिभाषित करते हैं: पूर्णांक के रूप में डिम कर्व । प्रक्रिया की शुरुआत में, इसे एकता के बराबर बनाया जाना चाहिए। हमें चर " डेटा में वर्तमान पंक्ति" की भी आवश्यकता है, वर्तमान पिछली पंक्ति के समूह नामों के साथ एक सरणी। फिर आप एक चक्र लिख सकते हैं "जबकि पंक्ति में पहला सेल खाली नहीं है।"

वैश्विक चर

Option Explicit '       
      

        
        
        
      

     Dim CurRow As Integer 
      

        
        
        
      

     Const GroupsCount As Integer = 2 
      

        
        
        
      

     Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice() 
      

        
        
        
      

     Dim I As Integer '   data 
      

        
        
        
      

     CurRow = 1 
      

        
        
        
      

     Dim Groups(1 To GroupsCount) As String 
      

        
        
        
      

     Dim PrGroups(1 To GroupsCount) As String 
      

        
        
        
      

     
      

        
        
        
      

     Sheets( "data" ).Activate 
      

        
        
        
      

     I = 2 
      

        
        
        
      

     Do While True 
      

        
        
        
      

     If GetCell(0, I).Value = "" Then Exit Do 
      

        
        
        
      

     ' ... 
      

        
        
        
      

     I = I + 1 
      

        
        
        
      

     Loop 
      

        
        
        
      

     End Sub

अब आपको समूह सरणी को पॉप्युलेट करने की आवश्यकता है:

इलिप्सिस के स्थान पर

Dim I2 As Integer 
      

        
        
        
      

     For I2 = 1 To GroupsCount 
      

        
        
        
      

     Groups(I2) = GetCell(I2, I) 
      

        
        
        
      

     Next I2 
      

        
        
        
      

     ' ... 
      

        
        
        
      

     For I2 = 1 To GroupsCount ' VB     
      

        
        
        
      

     PrGroups(I2) = Groups(I2) 
      

        
        
        
      

     Next I2 
      

        
        
        
      

     I =  I + 1

और हेडर बनाएँ:

पिछले टुकड़े में दीर्घवृत्त के स्थान पर

For I2 = 1 To GroupsCount 
      

        
        
        
      

     If Groups(I2) <> PrGroups(I2) Then 
      

        
        
        
      

     Dim I3 As Integer 
      

        
        
        
      

     For I3 = I2 To GroupsCount 
      

        
        
        
      

     AddHeader I3, Groups(I3) 
      

        
        
        
      

     Next I3 
      

        
        
        
      

     Exit For 
      

        
        
        
      

     End If 
      

        
        
        
      

     Next I2

AddHeader प्रक्रिया के बारे में मत भूलना:

प्रारूप मूल्य से पहले

Sub AddHeader(Ty As Integer , Name As String ) 
      

        
        
        
      

     GetCellS( "result" , 1, CurRow).Value = Name 
      

        
        
        
      

     CurRow = CurRow + 1 
      

        
        
        
      

     End Sub

अब हमें परिणाम के लिए सभी जानकारी को स्थानांतरित करने की आवश्यकता है

For I2 = 0 To DataCount - 1 GetCellS( "result" , I2, CurRow).Value = GetCell(I2, I) Next I2

कॉलम को चौड़ाई में फिट करें और परिणाम प्रदर्शित करने के लिए परिणाम पत्रक का चयन करें

FormatPrice के अंत में लूप के बाद

Sheets( "Result" ).Activate 
      

        
        
        
      

     Columns.AutoFit

वह सब है। आप पहले संस्करण का आनंद ले सकते हैं।

बदसूरत, लेकिन ऐसा लगता है। चलो स्वरूपण के साथ सौदा करते हैं। सबसे पहले, AddHeader प्रक्रिया बदलें:

Sub AddHeader(Ty As Integer , Name As String ) Sheets( "result" ).Range( "A" + CStr(CurRow) + ":C" + CStr(CurRow)).Merge ' ' With With GetCellS( "result" , 0, CurRow) .Value = Name .Font.Italic = True .Font.Name = "Cambria" Select Case Ty Case 1 ' .Font.Bold = True .Font.Size = 16 Case 2 ' .Font.Size = 12 End Select .HorizontalAlignment = xlCenter End With CurRow = CurRow + 1 End Sub

पहले से बेहतर:

यह केवल सीमाएँ खींचने के लिए बनी हुई है। यहां हमें पहले से ही सभी मर्ज किए गए कक्षों के साथ काम करने की आवश्यकता है, अन्यथा केवल एक सीमा होगी:

इसलिए, हम सीमा शैली के अलावा कोड को थोड़ा बदलते हैं:

Sub AddHeader(Ty As Integer , Name As String ) With Sheets( "result" ).Range( "A" + CStr(CurRow) + ":C" + CStr(CurRow)) .Merge .Value = Name .Font.Italic = True .Font.Name = "Cambria" .HorizontalAlignment = xlCenter Select Case Ty Case 1 ' .Font.Bold = True .Font.Size = 16 .Borders(xlTop).Weight = xlThick Case 2 ' .Font.Size = 12 .Borders(xlTop).Weight = xlMedium End Select .Borders(xlBottom).Weight = xlMedium ' : xlThick, xlMedium, xlThin, xlHairline End With CurRow = CurRow + 1 End Sub

यह केवल एक नया समूह शुरू करने से पहले पास प्राप्त करने के लिए रहता है। यह आसान है:

FormatPrice की शुरुआत में

Dim I As Integer '    data 
      

        
        
        
      

     CurRow = 0 '        
      

        
        
        
      

     Dim Groups(1 To GroupsCount) As String

हेडिंग लूप में

If Groups(I2) <> PrGroups(I2) Then 
      

        
        
        
      

     CurRow = CurRow + 1 
      

        
        
        
      

     Dim I3 As Integer

बिल्कुल वही जो वे चाहते थे।

आशा है कि इस लेख ने आपको VBA के लिए एक्सेल प्रोग्रामिंग से थोड़ा परिचित होने में मदद की है। होमवर्क - परिणाम में शीर्षक " आईडी, शीर्षक, मूल्य " जोड़ें। संकेत: ~~CurRow = 0~~ CurRow = 1 ।

फ़ाइल यहाँ (min.us) या यहाँ (ड्रॉपबॉक्स) डाउनलोड की जा सकती है। मैक्रो निष्पादन सक्षम करने के लिए याद रखें। ~~यदि कोई मानव फ़ाइल होस्टिंग का वादा करता है, तो मैं उसे अपलोड करूँगा।~~

आपका ध्यान देने के लिए धन्यवाद।

मुझे टिप्पणियों में रचनात्मक आलोचना करने में खुशी होगी।

UPD: मैंने ड्रॉपबॉक्स और min.us पर उदाहरण पुनः लोड किया।

UPD2: वास्तव में, एक पैरामीटर के साथ एक प्रक्रिया को बुलाते समय, कोष्ठक लगाए जा सकते हैं। या कॉल फू निर्माण ("बार", 1, 2, 3) का उपयोग करें - यहां कोष्ठक हमेशा आवश्यक होते हैं।

VBA के साथ Microsoft Excel में नियमित स्वचालन