excel-vba - Excel VBA में चयन का उपयोग करने से कैसे बचें





(11)


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

इस प्रकार आप निम्नलिखित मामलों में Select और Activate से बच सकते हैं:

एक नई वर्कशीट जोड़ना और उस पर एक सेल कॉपी करना:

से (मैक्रो रिकॉर्डर के साथ उत्पन्न कोड):

Sub Makro2()
    Range("B2").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

सेवा मेरे:

Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

जब आप कार्यपत्रकों के बीच सीमा की प्रतिलिपि बनाना चाहते हैं:

से:

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste

सेवा मेरे:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

श्रेणी नामक फैंसी का उपयोग करना

आप उन्हें [] साथ एक्सेस कर सकते हैं। दूसरी तरफ की तुलना में वास्तव में सुंदर कौन सा है। अपने आप को जांचो:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

ऊपर से उदाहरण इस तरह दिखेगा:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

मूल्यों की प्रतिलिपि नहीं बना रहे हैं, लेकिन उन्हें ले जा रहे हैं

आमतौर पर, यदि आप select इच्छुक हैं, तो शायद आप कुछ कॉपी कर रहे हैं। यदि आप केवल मूल्यों में रूचि रखते हैं, तो चयन से बचने के लिए यह एक अच्छा विकल्प है:

Range("B1:B6").Value = Range("A1:A6").Value

वर्कशीट को हमेशा संदर्भित करने का प्रयास करें

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

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

क्या मैं वास्तव में कभी भी उपयोग नहीं कर सकता .Select या कुछ भी के लिए .Activate ?

एकमात्र ऐसा समय जब आप उपयोग करने के लिए उचित हो सकते हैं। .Activate और चयन करें जब आप सुनिश्चित करना चाहते हैं कि विज़ुअल कारणों के लिए एक विशिष्ट वर्कशीट चयनित है। उदाहरण के लिए, आपका एक्सेल हमेशा पहले चयनित कवर वर्कशीट के साथ खुलता है, जो फ़ाइल बंद होने पर सक्रियतापत्र को अनदेखा करता था। इस प्रकार, ऐसा कुछ बिल्कुल ठीक है:

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub

मैंने उपयोग करने के समझने के घृणा के बारे में बहुत कुछ सुना है .Select एक्सेल .Select में चयन करें, लेकिन इसका उपयोग करने से बचने के बारे में अनिश्चित हूं। मुझे लगता है कि अगर मैं Select कार्यों के बजाय चर का उपयोग करने में सक्षम था तो मेरा कोड अधिक उपयोग करने योग्य होगा। हालांकि, मुझे यकीन नहीं है कि Select का उपयोग न करने पर चीजों को कैसे संदर्भित किया जाए (जैसे ActiveCell आदि)।

मुझे इस लेख को श्रेणियों पर और इस उदाहरण को चुनने के लाभों के लाभ पर नहीं मिला है लेकिन कैसे कुछ भी नहीं मिल सकता है?




हमेशा कार्यपुस्तिका, वर्कशीट और सेल / रेंज बताएं।

उदाहरण के लिए:

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)

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

और कभी भी कार्यपुस्तिका के सूचकांक का उपयोग न करें।

Workbooks(1).Worksheets("fred").cells(1,1)

जब आप उपयोगकर्ता अपना कोड चलाते हैं तो आपको नहीं पता कि अन्य कार्यपुस्तिकाएं कब खुली रहेंगी।




कृपया ध्यान दें कि निम्नलिखित में मैं चयन दृष्टिकोण (जिसे ओपी से बचना चाहता है) की तुलना कर रहा हूं, रेंज दृष्टिकोण के साथ (और यह प्रश्न का उत्तर है)। तो जब आप पहली चयन देखते हैं तो पढ़ना बंद न करें।

यह वास्तव में इस बात पर निर्भर करता है कि आप क्या करने की कोशिश कर रहे हैं। वैसे भी एक साधारण उदाहरण उपयोगी हो सकता है। मान लीजिए कि आप सक्रिय सेल का मान "foo" पर सेट करना चाहते हैं। ActiveCell का उपयोग करके आप इस तरह कुछ लिखेंगे:

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

यदि आप इसे उस सेल के लिए उपयोग करना चाहते हैं जो सक्रिय नहीं है, उदाहरण के लिए "बी 2" के लिए, आपको इसे पहले चुनना चाहिए, जैसे:

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

रेंज का उपयोग करके आप एक और जेनेरिक मैक्रो लिख सकते हैं जिसका उपयोग किसी भी सेल के मूल्य को सेट करने के लिए किया जा सकता है जिसे आप चाहते हैं:

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

फिर आप मैक्रो 2 को फिर से लिख सकते हैं:

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

और मैक्रो 1 के रूप में:

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

उम्मीद है कि यह चीजों को थोड़ा सा साफ़ करने में मदद करता है।




जोर देने का एक छोटा सा बिंदु मैं ऊपर दिए गए सभी उत्कृष्ट उत्तरों में जोड़ूंगा:

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

नामित श्रेणियों के उदार उपयोग करने के कुछ अतिरिक्त कारण यहां दिए गए हैं, हालांकि मुझे यकीन है कि मैं और अधिक सोच सकता हूं।

नामित श्रेणियां आपके कोड को पढ़ने और समझने में आसान बनाती हैं।

उदाहरण:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
'e.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
'e.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

यह बहुत स्पष्ट है कि नामित श्रेणी Months और MonthlySales में क्या है, और प्रक्रिया क्या कर रही है।

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

नामित श्रेणियां यह सुनिश्चित करती हैं कि स्प्रेडशीट परिवर्तनों की कॉन्फ़िगरेशन (यदि नहीं!) तो आपके मैक्रोज़ ब्रेक नहीं करते हैं।

गौर करें, अगर उपरोक्त उदाहरण इस तरह लिखा गया था:

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1 
    Debug.Print rng2(rng3.Row)
Next rng3

यह कोड पहले ठीक काम करेगा - यह तब तक है जब तक आप या भविष्य के उपयोगकर्ता "जीई विज़" का फैसला नहीं करते हैं, मुझे लगता है कि मैं कॉलम A में वर्ष के साथ एक नया कॉलम जोड़ने जा रहा हूं, या महीनों के बीच व्यय कॉलम डालता हूं और बिक्री कॉलम, या प्रत्येक कॉलम में एक हेडर जोड़ें। अब, आपका कोड टूटा हुआ है। और क्योंकि आपने भयानक परिवर्तनीय नामों का उपयोग किया है, यह आपको यह समझने के लिए बहुत अधिक समय लगेगा कि इसे कैसे ठीक किया जाना चाहिए।

यदि आपने नामों के साथ शुरू करने के लिए नामों का उपयोग किया था, तो Months और Sales कॉलम को आपके जैसी सभी जगहों पर ले जाया जा सकता है, और आपका कोड ठीक काम करना जारी रखेगा।




शीघ्र जवाब:

उपयोग करने से बचने के लिए .Select विधि आप अपनी इच्छित संपत्ति के बराबर एक चर सेट कर सकते हैं।

► उदाहरण के लिए, यदि आप Cell A1 में मान चाहते हैं तो आप उस सेल की मान संपत्ति के बराबर एक चर सेट कर सकते हैं।

  • उदाहरण valOne = Range("A1").Value

► उदाहरण के लिए, यदि आप 'शीट 3' के कोडनाम चाहते हैं तो आप उस वर्कशीट की कोडनाम संपत्ति के बराबर एक चर सेट कर सकते हैं।

  • उदाहरण valTwo = Sheets("Sheet3").Codename

मुझे आशा है कि वह मदद करेंगे। अगर आपका कोई प्रश्न हैं, तो मुझे से पूछें।




यह एक उदाहरण है जो सेल "ए 1" की सामग्री को साफ़ करेगा (या अधिक यदि चयन प्रकार xllastcell, आदि है)। सभी कोशिकाओं का चयन किए बिना किया जाता है।

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents 

मुझे उम्मीद है इससे किसी को सहायता मिलेगी।




"... और मुझे लगता है कि अगर मैं चयन कार्यों के बजाय चर का उपयोग करने में सक्षम था तो मेरा कोड अधिक उपयोग करने योग्य होगा।"

जबकि मैं अलग-अलग स्थितियों से कहीं ज्यादा नहीं सोच सकता हूं। Selection कि सेल सेल संदर्भ के मुकाबले बेहतर विकल्प होगा, मैं Selection की रक्षा में वृद्धि करूंगा और Selection कि इसे उसी कारण से बाहर नहीं किया जाना चाहिए .Select से बचा जाना चाहिए।

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

चयन-आधारित उप ढांचे के उदाहरण:

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

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

संक्षेप में, Selection के साथ इसके करीबी सहयोग के कारण Selection न छोड़ें। Selection और सक्रिय करें। वर्कशीट संपत्ति के रूप में इसके कई अन्य उद्देश्य हैं।

(हाँ, मुझे पता है कि यह सवाल था .Select Selection , Selection न करें, लेकिन मैं किसी भी गलतफहमी को हटाना चाहता हूं कि नौसिखिया वीबीए कोडर अनुमान लगा सकते हैं।)




.select उपयोग लोगों से आता है, जो मुझे पसंद करते हैं, रिकॉर्डिंग मैक्रोज़ के माध्यम से आवश्यकता के जरिए .select सीखना शुरू कर देते हैं और उसके बाद कोड को संशोधित किए बिना संशोधित करते हैं। selection और बाद में selection केवल एक अनावश्यक मध्य-पुरुष है।

.select से बचा जा सकता है, जैसा कि पहले से ही पहले से मौजूद मौजूदा वस्तुओं के साथ सीधे काम करके पोस्ट किया जा सकता है, जो विभिन्न अप्रत्यक्ष संदर्भों को एक जटिल तरीके से I और j की गणना करने और फिर सेल (i, j) आदि को संपादित करने की अनुमति देता है।

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




चयन से बचने के तरीके के कुछ उदाहरण

Dim 'डी चर का प्रयोग करें

Dim rng as Range

परिवर्तनीय को आवश्यक सीमा पर Set । सिंगल-सेल रेंज को संदर्भित करने के कई तरीके हैं

Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = [A1]
Set rng = Range("NamedRange")

या एक बहु-सेल रेंज

Set rng = Range("A1:B10")
Set rng = Range(Cells(1,1), Cells(2,10))
Set rng = [A1:B10]
Set rng = Range("AnotherNamedRange")

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

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)

फिर, यह सक्रिय कार्यपुस्तिका को संदर्भित करता है, इसलिए आप यहां भी स्पष्ट होना चाहेंगे।

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

पास आपके Sub और Function के रेंज चर के रूप में है

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = [A1:B10]
    ClearRange rng
End Sub

आपको चर के लिए तरीके (जैसे Find और Copy ) भी लागू करना चाहिए

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = [A1:A10]
Set rng2 = [B1:B10]
rng1.Copy rng2

यदि आप कोशिकाओं की एक श्रृंखला पर लूप कर रहे हैं तो यह श्रेणी मूल्यों को पहले एक संस्करण सरणी में कॉपी करने के लिए बेहतर (तेज़) होता है और उस पर लूप होता है

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = [A1:A10000]
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i,1) * 10 'or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

यह संभव है कि यह एक छोटा सा टस्टर है।




दो मुख्य कारण क्यों हैं .Select Selection / Activecell / Selection / Activecell / Activesheet / Activeworkbook आदि ... से बचा जाना चाहिए

  1. यह आपके कोड को धीमा कर देता है।
  2. यह आमतौर पर रनटाइम त्रुटियों का मुख्य कारण होता है।

हम इससे कैसे बचें?

1) सीधे प्रासंगिक वस्तुओं के साथ काम करते हैं

इस कोड पर विचार करें

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

इस कोड को भी लिखा जा सकता है

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) यदि आवश्यक हो तो अपने चर घोषित करें। उपरोक्त एक ही कोड के रूप में लिखा जा सकता है

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With



queryString = "SELECT name FROM user WHERE id=" & Worksheets("Sheet1").Range("D4").Value




excel vba excel-vba