excel-vba
VBA बेस्ट प्रैक्टिस
खोज…
टिप्पणियों
हम सभी उन्हें जानते हैं, लेकिन ये अभ्यास किसी के लिए VBA में कार्यक्रम शुरू करने के लिए कम स्पष्ट हैं।
हमेशा "विकल्प स्पष्ट" का उपयोग करें
VBA संपादक विंडो में, टूल मेनू से "विकल्प" चुनें:
फिर "संपादक" टैब में, सुनिश्चित करें कि "परिवर्तनीय घोषणा की आवश्यकता है" की जाँच की गई है:
इस विकल्प का चयन स्वचालित रूप से प्रत्येक VBA मॉड्यूल के शीर्ष पर Option Explicit
करेगा।
छोटे नोट: यह मॉड्यूल, क्लास मॉड्यूल आदि के लिए सही है, जो अब तक नहीं खोला गया है। इसलिए यदि आप पहले से ही एक नज़र रखते थे जैसे कि "आवश्यक परिवर्तन की आवश्यकता" विकल्प को सक्रिय करने से पहले
Sheet1
का कोड,Option Explicit
नहीं जोड़ा जाएगा!
Option Explicit
लिए यह आवश्यक है कि प्रत्येक चर को Dim
स्टेटमेंट के साथ उपयोग करने से पहले परिभाषित किया जाए। Option Explicit
बिना सक्षम, किसी भी अपरिचित शब्द को VBA संकलक द्वारा Variant
प्रकार का एक नया चर माना जाएगा, जिससे टाइपोग्राफिक त्रुटियों से संबंधित अत्यंत कठिन-से-स्पॉट कीड़े हो सकते हैं। Option Explicit
साथ, किसी भी अपरिचित शब्द को फेंकने की रेखा को इंगित करने के लिए एक संकलन त्रुटि हो जाएगी।
उदाहरण :
यदि आप निम्नलिखित कोड चलाते हैं:
Sub Test()
my_variable = 12
MsgBox "My Variable is : " & myvariable
End Sub
आपको निम्न संदेश मिलेगा:
आप लिख कर कोई त्रुटि की है myvariable
के बजाय my_variable
, तो संदेश बॉक्स प्रदर्शित करता है एक खाली चर। यदि आप Option Explicit
उपयोग करते हैं, तो यह त्रुटि संभव नहीं है क्योंकि आपको समस्या का संकेत करने वाला एक संकलन त्रुटि संदेश मिलेगा।
अब यदि आप सही घोषणा जोड़ते हैं:
Sub Test()
Dim my_variable As Integer
my_variable = 12
MsgBox "My Variable is : " & myvariable
End Sub
आपको एक त्रुटि संदेश प्राप्त होगा जो ठीक से त्रुटि का संकेत myvariable
:
विकल्प स्पष्ट और सारणी पर ध्यान दें ( एक गतिशील सरणी की घोषणा ):
आप किसी प्रक्रिया के भीतर किसी सरणी को घोषित करने के लिए ReDim स्टेटमेंट का उपयोग कर सकते हैं।
जब आप ReDim स्टेटमेंट का उपयोग करते हैं, तो सरणी के नाम को गलत न करें
भले ही विकल्प स्पष्ट विवरण मॉड्यूल में शामिल हो, एक नई सरणी बनाई जाएगी
Dim arr() as Long
ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"
एरेस के साथ काम करें, न कि रेंज के साथ
कार्यालय ब्लॉग - एक्सेल VBA प्रदर्शन कोडिंग सर्वश्रेष्ठ आचरण
अक्सर, जितना संभव हो उतना Range
के उपयोग से बचकर सर्वश्रेष्ठ प्रदर्शन प्राप्त किया जाता है। इस उदाहरण में हम एक सरणी में संपूर्ण Range
ऑब्जेक्ट में पढ़ते हैं, सरणी में प्रत्येक संख्या को वर्ग करते हैं, और फिर Range
में सरणी वापस करते हैं। यह केवल दो बार Range
एक्सेस करता है, जबकि एक लूप इसे पढ़ने / लिखने के लिए 20 बार एक्सेस करेगा।
Option Explicit
Sub WorkWithArrayExample()
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array
For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
End Sub
अधिक उदाहरण और समयबद्ध उदाहरण के साथ जानकारी चार्ल्स विलियम्स के लेखन कुशल VBA UDFs (भाग 1) और श्रृंखला के अन्य लेखों में पाई जा सकती है।
उपलब्ध होने पर वीबी स्थिरांक का उपयोग करें
If MsgBox("Click OK") = vbOK Then
के स्थान पर इस्तेमाल किया जा सकता है
If MsgBox("Click OK") = 1 Then
पठनीयता में सुधार करने के लिए।
उपलब्ध वीबी स्थिरांक खोजने के लिए ऑब्जेक्ट ब्राउज़र का उपयोग करें। वीबी संपादक से → ऑब्जेक्ट ब्राउज़र या F2 देखें ।
खोज करने के लिए कक्षा दर्ज करें
सदस्यों को उपलब्ध देखें
वर्णनात्मक चर नामकरण का उपयोग करें
आपके कोड में वर्णनात्मक नाम और संरचना टिप्पणियों को अनावश्यक बनाने में मदद करते हैं
Dim ductWidth As Double
Dim ductHeight As Double
Dim ductArea As Double
ductArea = ductWidth * ductHeight
से बेहतर है
Dim a, w, h
a = w * h
यह विशेष रूप से तब मददगार होता है जब आप डेटा को एक जगह से दूसरी जगह कॉपी कर रहे होते हैं, चाहे वह सेल, रेंज, वर्कशीट या वर्कबुक हो। इन जैसे नामों का उपयोग करके स्वयं सहायता करें:
Dim myWB As Workbook
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim srcData As Range
Dim destData As Range
Set myWB = ActiveWorkbook
Set srcWS = myWB.Sheets("Sheet1")
Set destWS = myWB.Sheets("Sheet2")
Set srcData = srcWS.Range("A1:A10")
Set destData = destWS.Range("B11:B20")
destData = srcData
यदि आप एक पंक्ति में कई चर घोषित करते हैं, तो सुनिश्चित करें कि प्रत्येक चर के लिए एक प्रकार निर्दिष्ट करें:
Dim ductWidth As Double, ductHeight As Double, ductArea As Double
निम्नलिखित केवल अंतिम चर घोषित करेगा और पहले वाले Variant
रहेंगे:
Dim ductWidth, ductHeight, ductArea As Double
गलती संभालना
अच्छी त्रुटि हैंडलिंग अंत उपयोगकर्ताओं को VBA रनटाइम त्रुटियों को देखने से रोकती है और डेवलपर को त्रुटियों का आसानी से निदान और सुधार करने में मदद करता है।
VBA में एरर हैंडलिंग के तीन मुख्य तरीके हैं, जिनमें से दो को वितरित कार्यक्रमों के लिए टाला जाना चाहिए जब तक कि कोड में विशेष रूप से आवश्यक न हो।
On Error GoTo 0 'Avoid using
या
On Error Resume Next 'Avoid using
उपयोग करना पसंद करें:
On Error GoTo <line> 'Prefer using
त्रुटि GoTo 0 पर
यदि आपके कोड में कोई त्रुटि हैंडलिंग सेट नहीं है, तो On Error GoTo 0
डिफ़ॉल्ट त्रुटि हैंडलर है। इस मोड में, कोई भी रनटाइम त्रुटियां विशिष्ट VBA त्रुटि संदेश लॉन्च करेंगी, जिससे आप कोड को समाप्त कर सकते हैं या स्रोत की पहचान करते हुए debug
मोड दर्ज कर सकते हैं। कोड लिखते समय, यह विधि सबसे सरल और सबसे उपयोगी है, लेकिन इसे हमेशा उन कोड के लिए टाला जाना चाहिए जो अंतिम उपयोगकर्ताओं को वितरित किए जाते हैं, क्योंकि यह विधि अंत उपयोगकर्ताओं के लिए समझने के लिए बहुत ही भद्दा और कठिन है।
त्रुटि फिर से शुरू पर
On Error Resume Next
VBA के कारण किसी भी एरर को नजरअंदाज करना पड़ेगा जो एरर कॉल के बाद सभी लाइनों के लिए रनटाइम पर फेंका जाता है। बहुत विशिष्ट उदाहरणों में, यह रेखा उपयोगी हो सकती है, लेकिन इसे इन मामलों से बाहर रखा जाना चाहिए। उदाहरण के लिए, जब एक्सेल मैक्रो से अलग प्रोग्राम लॉन्च किया जाता है, तो On Error Resume Next
कॉल उपयोगी हो सकता है यदि आप इस बात को लेकर अनिश्चित हों कि प्रोग्राम पहले से खुला है या नहीं:
'In this example, we open an instance of Powerpoint using the On Error Resume Next call
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
'Open PPT if not running, otherwise select active instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo ErrHandler
If PPApp Is Nothing Then
'Open PowerPoint
Set PPApp = CreateObject("PowerPoint.Application")
PPApp.Visible = True
End If
अगर हमने On Error Resume Next
कॉल का उपयोग नहीं किया था और पावरपॉइंट एप्लिकेशन पहले से खुला नहीं था, तो GetObject
विधि एक त्रुटि फेंक देगी। इस प्रकार, एप्लिकेशन के दो इंस्टेंसेस बनाने से बचने के लिए On Error Resume Next
आवश्यक था।
नोट: जैसे ही आपको On Error Resume Next
कॉल की आवश्यकता नहीं है, तुरंत त्रुटि हैंडलर को रीसेट करना एक सर्वोत्तम अभ्यास है
त्रुटि GoTo <लाइन> पर
त्रुटि हैंडलिंग की यह विधि सभी उपयोगकर्ताओं के लिए वितरित किए जाने वाले सभी कोड के लिए अनुशंसित है। यह प्रोग्रामर को नियंत्रित करने की अनुमति देता है कि कोड को निर्दिष्ट लाइन पर भेजकर VBA एक त्रुटि को कैसे संभालता है। टैग किसी भी स्ट्रिंग (संख्यात्मक स्ट्रिंग्स सहित) से भरा जा सकता है, और कोड को उसी स्ट्रिंग को भेजेगा जो एक कोलन द्वारा पीछा किया जाता है। एकाधिक त्रुटि हैंडलिंग ब्लॉकों का उपयोग On Error GoTo <line>
विभिन्न कॉल करके किया जा सकता है। नीचे दिया गया सबरूटीन On Error GoTo <line>
कॉल के सिंटैक्स को प्रदर्शित करता है।
नोट: यह आवश्यक है कि Exit Sub
लाइन को पहले त्रुटि हैंडलर के ऊपर रखा जाए और बाद में त्रुटि को बुलाए बिना ब्लॉक में स्वाभाविक रूप से प्रगति करने से रोकने के लिए प्रत्येक बाद वाले त्रुटि हैंडलर से पहले। इस प्रकार, यह एक कोड ब्लॉक के अंत में त्रुटि हैंडलर लगाने के लिए फ़ंक्शन और पठनीयता के लिए सबसे अच्छा अभ्यास है।
Sub YourMethodName()
On Error GoTo errorHandler
' Insert code here
On Error GoTo secondErrorHandler
Exit Sub 'The exit sub line is essential, as the code will otherwise
'continue running into the error handling block, likely causing an error
errorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
Exit Sub
secondErrorHandler:
If Err.Number = 424 Then 'Object not found error (purely for illustration)
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Else
MsgBox "Error " & Err.Number & ": " & Err.Desctription
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End If
Exit Sub
End Sub
यदि आप अपनी त्रुटि हैंडलिंग कोड के साथ अपनी विधि से बाहर निकलते हैं, तो सुनिश्चित करें कि आप सफाई करते हैं:
- कुछ भी जो आंशिक रूप से पूरा हो
- फ़ाइलें बंद करें
- स्क्रीन अपडेट को रीसेट करें
- गणना मोड रीसेट करें
- घटनाओं को रीसेट करें
- माउस पॉइंटर को रीसेट करें
- ऑब्जेक्ट्स के इंस्टेंस पर अनलोड विधि को कॉल करें, जो
End Sub
बाद बनी रहती है - स्थिति पट्टी को रीसेट करें
अपने काम का दस्तावेज
बाद में उपयोग के लिए अपने काम का दस्तावेजीकरण करना अच्छा है, खासकर यदि आप एक गतिशील कार्यभार के लिए कोडिंग कर रहे हैं। अच्छी टिप्पणियों को स्पष्ट करना चाहिए कि कोड कुछ क्यों कर रहा है, न कि कोड क्या कर रहा है।
Function Bonus(EmployeeTitle as String) as Double
If EmployeeTitle = "Sales" Then
Bonus = 0 'Sales representatives receive commission instead of a bonus
Else
Bonus = .10
End If
End Function
यदि आपका कोड इतना अस्पष्ट है कि उसे यह बताने के लिए टिप्पणियों की आवश्यकता है कि वह क्या कर रहा है, तो टिप्पणियों के माध्यम से इसे समझाने के बजाय इसे और अधिक स्पष्ट करने के लिए पुनर्लेखन पर विचार करें। उदाहरण के लिए, इसके बजाय:
Sub CopySalesNumbers
Dim IncludeWeekends as Boolean
'Boolean values can be evaluated as an integer, -1 for True, 0 for False.
'This is used here to adjust the range from 5 to 7 rows if including weekends.
Range("A1:A" & 5 - (IncludeWeekends * 2)).Copy
Range("B1").PasteSpecial
End Sub
पालन करने में आसान होने के लिए कोड स्पष्ट करें, जैसे:
Sub CopySalesNumbers
Dim IncludeWeekends as Boolean
Dim DaysinWeek as Integer
If IncludeWeekends Then
DaysinWeek = 7
Else
DaysinWeek = 5
End If
Range("A1:A" & DaysinWeek).Copy
Range("B1").PasteSpecial
End Sub
मैक्रो निष्पादन के दौरान गुणों को बंद करें
समयपूर्व अनुकूलन से बचने के लिए किसी भी प्रोग्रामिंग भाषा में यह सबसे अच्छा अभ्यास है । हालाँकि, यदि परीक्षण से पता चलता है कि आपका कोड बहुत धीमी गति से चल रहा है, तो आप अनुप्रयोग के कुछ गुणों को चालू करते हुए गति को रोक सकते हैं। इस कोड को एक मानक मॉड्यूल में जोड़ें:
Public Sub SpeedUp( _
SpeedUpOn As Boolean, _
Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
With Application
If SpeedUpOn Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayStatusBar = False 'in case you are not showing any messages
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
Else
.ScreenUpdating = True
.Calculation = xlCalc
.EnableEvents = True
.DisplayStatusBar = True
ActiveSheet.DisplayPageBreaks = True
End If
End With
End Sub
कार्यालय ब्लॉग पर अधिक जानकारी - एक्सेल VBA प्रदर्शन कोडिंग सर्वश्रेष्ठ आचरण
और बस इसे मैक्रोज़ की शुरुआत और अंत में कहें:
Public Sub SomeMacro
'store the initial "calculation" state
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
SpeedUp True
'code here ...
'by giving the second argument the initial "calculation" state is restored
'otherwise it is set to 'xlCalculationAutomatic'
SpeedUp False, xlCalc
End Sub
जबकि इन बड़े पैमाने पर नियमित रूप से करने के लिए "संवर्द्धन" माना जा सकता Public Sub
प्रक्रियाओं, के साथ घटना से निपटने को अक्षम करने Application.EnableEvents = False
के लिए अनिवार्य विचार किया जाना चाहिए Worksheet_Change
और Workbook_SheetChange
निजी घटना मैक्रो जो एक या अधिक कार्यपत्रक पर परिवर्तन मूल्यों। ईवेंट ट्रिगर्स को अक्षम करने में विफलता इवेंट मैक्रो को पुन: अपने आप शीर्ष पर चलाने का कारण बनेगी जब कोई मान बदलता है और "जमे हुए" वर्कबुक को जन्म दे सकता है। इवेंट मैक्रो को छोड़ने से पहले ईवेंट को वापस चालू करना याद रखें, संभवतः "सुरक्षित निकास" त्रुटि हैंडलर के माध्यम से।
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
'code that may change a value on the worksheet goes here
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
एक चेतावनी: इन सेटिंग्स को अक्षम करने के दौरान रन टाइम में सुधार होगा, वे आपके एप्लिकेशन को बहुत कठिन बना सकते हैं। यदि आपका कोड सही ढंग से काम नहीं कर रहा है, तो SpeedUp True
कॉल पर टिप्पणी करें जब तक आप समस्या का पता नहीं लगा लेते हैं।
यह विशेष रूप से महत्वपूर्ण है यदि आप किसी कार्यपत्रक में कक्षों को लिख रहे हैं और तब कार्यपत्रक के कार्यों से परिकलित परिणामों में वापस पढ़ रहे हैं क्योंकि xlCalculationManual
कार्यपुस्तिका की गणना करने से रोकता है। SpeedUp
को अक्षम किए बिना इसे प्राप्त करने के लिए, आप विशिष्ट बिंदुओं पर गणना चलाने के लिए Application.Calculate
को शामिल करना चाह सकते हैं।
ध्यान दें: चूंकि ये Application
गुण हैं, इसलिए आपको यह सुनिश्चित करने की आवश्यकता है कि आपके मैक्रो के बाहर निकलने से पहले उन्हें फिर से सक्षम किया जाए। यह विशेष रूप से त्रुटि हैंडलर का उपयोग करने और कई निकास बिंदुओं (यानी End
या Unload Me
) से बचने के लिए महत्वपूर्ण है।
त्रुटि से निपटने के साथ:
Public Sub SomeMacro()
'store the initial "calculation" state
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
On Error GoTo Handler
SpeedUp True
'code here ...
i = 1 / 0
CleanExit:
SpeedUp False, xlCalc
Exit Sub
Handler:
'handle error
Resume CleanExit
End Sub
एक्सेल में ActiveCell या ActiveSheet का उपयोग करने से बचें
ActiveCell
या ActiveSheet
का उपयोग करना गलतियों का स्रोत हो सकता है यदि (किसी भी कारण से) कोड गलत स्थान पर निष्पादित किया गया हो।
ActiveCell.Value = "Hello"
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello"
'will always place "Hello" in A1 of the currently selected sheet
ActiveSheet.Cells(1, 1).Value = "Hello"
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello"
'will always place "Hello" in A1 of the sheet named "MySheetName"
- यदि आपका उपयोगकर्ता ऊब गया है और किसी अन्य कार्यपत्रक पर क्लिक करता है या कोई अन्य कार्यपुस्तिका खोलता है, तो
Active*
उपयोग से लंबे समय तक चलने वाले मैक्रोज़ में समस्याएँ पैदा हो सकती हैं। - यदि आपका कोड खुलता है या कोई अन्य कार्यपुस्तिका बनाता है तो यह समस्याएं पैदा कर सकता है।
- यह समस्याएँ पैदा कर सकता है यदि आपका कोड
Sheets("MyOtherSheet").Select
का उपयोग करता हैSheets("MyOtherSheet").Select
और इससे पहले कि आप पढ़ना या लिखना शुरू करते हैं, आप भूल गए हैं कि आप किस शीट पर थे।
वर्कशीट कभी नहीं मानें
यहां तक कि जब आपके सभी कार्य एक ही वर्कशीट पर निर्देशित होते हैं, तब भी अपने कोड में वर्कशीट को स्पष्ट रूप से निर्दिष्ट करना एक बहुत अच्छा अभ्यास है। यह आदत बाद में आपके कोड का विस्तार करना, या किसी Sub
या Function
भागों (या सभी) को उठाने के लिए कहीं और फिर से उपयोग किए जाने के लिए बहुत आसान बनाती है। कई डेवलपर्स अपने कोड में एक वर्कशीट के लिए एक ही स्थानीय चर नाम का उपयोग करने की आदत (पुनः) स्थापित करते हैं, जिससे उस कोड का पुनः उपयोग और भी अधिक सरल हो जाता है।
एक उदाहरण के रूप में, निम्न कोड अस्पष्ट है - लेकिन काम करता है! - जब तक डेवलपर एक अलग वर्कशीट में सक्रिय या परिवर्तित नहीं होता है:
Option Explicit
Sub ShowTheTime()
'--- displays the current time and date in cell A1 on the worksheet
Cells(1, 1).Value = Now() ' don't refer to Cells without a sheet reference!
End Sub
यदि Sheet1
सक्रिय है, तो शीट 1 पर सेल A1 वर्तमान तिथि और समय से भरा जाएगा। लेकिन अगर उपयोगकर्ता किसी भी कारण से कार्यपत्रकों को बदलता है, तो कोड उस समय अद्यतन करेगा जो कार्यपत्रक सक्रिय है। गंतव्य कार्यपत्रक अस्पष्ट है।
सबसे अच्छा अभ्यास हमेशा यह पहचानना होता है कि आपका कोड किस वर्कशीट को संदर्भित करता है:
Option Explicit
Sub ShowTheTime()
'--- displays the current time and date in cell A1 on the worksheet
Dim myWB As Workbook
Set myWB = ThisWorkbook
Dim timestampSH As Worksheet
Set timestampSH = myWB.Sheets("Sheet1")
timestampSH.Cells(1, 1).Value = Now()
End Sub
उपरोक्त कोड कार्यपुस्तिका और कार्यपत्रक दोनों की पहचान करने में स्पष्ट है। हालांकि यह ओवरकिल की तरह लग सकता है, लक्ष्य संदर्भों के संबंध में एक अच्छी आदत बनाने से आप भविष्य की समस्याओं से बच जाएंगे।
SELECT या ACTIVATE के उपयोग से बचें
यह बहुत ही दुर्लभ है कि आप कभी भी अपने कोड में Select
या Activate
का उपयोग करना चाहते हैं, लेकिन कुछ एक्सेल विधियों को कार्यपत्रक या कार्यपुस्तिका को सक्रिय करने की आवश्यकता होती है, इससे पहले कि वे अपेक्षा के अनुरूप काम करेंगे।
यदि आप VBA सीखना शुरू कर रहे हैं, तो आपको अक्सर मैक्रो रिकॉर्डर का उपयोग करके अपने कार्यों को रिकॉर्ड करने का सुझाव दिया जाएगा, फिर कोड देखें। उदाहरण के लिए, मैंने शीट 2 पर सेल डी 3 में एक मान दर्ज करने के लिए की गई कार्रवाइयों को दर्ज किया, और मैक्रो कोड इस तरह दिखता है:
Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet2").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "3.1415" '(see **note below)
Range("D4").Select
End Sub
हालांकि याद रखें, मैक्रो रिकॉर्डर आपके (उपयोगकर्ता) कार्यों में से प्रत्येक के लिए कोड की एक पंक्ति बनाता है। इसमें Sheets("Sheet2").Select
( Sheets("Sheet2").Select
का चयन करने के लिए वर्कशीट टैब पर क्लिक करना शामिल है Sheets("Sheet2").Select
मूल्य दर्ज करने से पहले सेल डी 3 पर क्लिक करके ( Range("D3").Select
), और एंटर कुंजी का उपयोग करें (जो प्रभावी रूप से है "वर्तमान में चयनित सेल के नीचे सेल का चयन करें: Range("D4").Select
)।
उपयोग करने के साथ कई समस्याएँ हैं .Select
यहाँ का चयन करें:
- वर्कशीट हमेशा निर्दिष्ट नहीं की जाती है। ऐसा तब होता है जब आप रिकॉर्डिंग करते समय वर्कशीट स्विच नहीं करते हैं, और इसका मतलब है कि कोड अलग-अलग सक्रिय वर्कशीट के लिए अलग-अलग परिणाम देगा।
-
.Select()
धीमा है। यहां तक कि अगरApplication.ScreenUpdating
कोFalse
सेट किया गया है, तो यह संसाधित होने के लिए एक अनावश्यक प्रक्रिया है। -
.Select()
अनियंत्रित है। यदिApplication.ScreenUpdating
True
छोड़ दिया जाता है, तो एक्सेल वास्तव में कोशिकाओं, वर्कशीट, फॉर्म ... का चयन करेगा। यह आंखों के लिए तनावपूर्ण है और वास्तव में देखने के लिए अप्रिय है। -
.Select()
श्रोताओं को गति देगा। यह पहले से ही थोड़ा उन्नत है, लेकिन जब तक चारों ओर काम नहीं किया जाता है, तब तकWorksheet_SelectionChange()
जैसे कार्यों को ट्रिगर किया जाएगा।
जब आप VBA में कोडिंग कर रहे होते हैं, तो सभी "टाइपिंग" क्रियाएं (यानी स्टेटमेंट का Select
) अब आवश्यक नहीं हैं। सेल में वैल्यू डालने के लिए आपका कोड एक स्टेटमेंट में घटाया जा सकता है:
'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415
'--- BETTER
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myCell As Range
Set myWB = ThisWorkbook '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")
myCell.Value = 3.1415
(सेल उदाहरण के अलग-अलग हिस्सों को अलग-अलग हिस्सों का उपयोग करने के लिए ऊपर दिए गए बेहतर उदाहरण से पता चलता है। अच्छा उदाहरण हमेशा ठीक काम करेगा, लेकिन बहुत अधिक कोड मॉड्यूल में बहुत बोझिल हो सकता है और यदि संदर्भों में से एक को गलत माना जाता है तो और अधिक कठिन हो सकता है। )
** नोट: मैक्रो रिकॉर्डर आपके द्वारा दर्ज किए जा रहे डेटा के प्रकार के बारे में कई धारणाएं बनाता है, इस मामले में मूल्य बनाने के सूत्र के रूप में एक स्ट्रिंग मान दर्ज करता है। आपके कोड को ऐसा करने की आवश्यकता नहीं है और बस सीधे सेल को एक संख्यात्मक मान निर्दिष्ट कर सकते हैं जैसा कि ऊपर दिखाया गया है।
** टिप्पणी 2: सिफारिश अभ्यास करने के लिए अपने स्थानीय कार्यपुस्तिका चर सेट करने के लिए है ThisWorkbook
के बजाय ActiveWorkbook
(जब तक आप स्पष्ट इसकी आवश्यकता)। कारण यह है कि आपके मैक्रो को आमतौर पर वीबीए कोड जो भी कार्यपुस्तिका उत्पन्न होती है उसमें संसाधनों का उपयोग / उपयोग करना होगा और उस कार्यपुस्तिका के बाहर नहीं देखेंगे - फिर से, जब तक कि आप स्पष्ट रूप से किसी अन्य कार्यपुस्तिका के साथ काम करने के लिए अपने कोड को निर्देशित नहीं करते हैं। जब आपके पास एक्सेल में कई कार्यपुस्तिकाएं होती हैं, तो ActiveWorkbook
वह फोकस होता है जो आपके VBA संपादक में देखी जा रही कार्यपुस्तिका से भिन्न हो सकता है । तो आपको लगता है कि आप एक कार्यपुस्तिका में निष्पादित कर रहे हैं जब आप वास्तव में एक और संदर्भित कर रहे हैं। ThisWorkbook
युक्त कोड निष्पादित किया जा रहा कार्यपुस्तिका को दर्शाता है।
हमेशा सभी कार्यपुस्तिकाओं और पत्रक के संदर्भ निर्धारित करें और सेट करें
कई खुली कार्यपुस्तिकाओं के साथ काम करते समय, जिनमें से प्रत्येक में कई पत्रक हो सकते हैं, यह सभी कार्यपुस्तिकाओं और पत्रक के संदर्भ को परिभाषित करने और सेट करने के लिए सबसे सुरक्षित है।
ActiveWorkbook
या ActiveSheet
पर भरोसा न करें क्योंकि वे उपयोगकर्ता द्वारा बदले जा सकते हैं।
निम्नलिखित कोड उदाहरण दर्शाता है कि कैसे एक सीमा "Results.xlsx" कार्यपुस्तिका में "Refined_Data" शीट को "Data.xlsx" कार्यपुस्तिका में "Raw_Data" चादर से कॉपी करने के लिए।
प्रक्रिया यह भी प्रदर्शित करती है कि कैसे Select
विधि का उपयोग किए बिना कॉपी और पेस्ट करना है।
Option Explicit
Sub CopyRanges_BetweenShts()
Dim wbSrc As Workbook
Dim wbDest As Workbook
Dim shtCopy As Worksheet
Dim shtPaste As Worksheet
' set reference to all workbooks by name, don't rely on ActiveWorkbook
Set wbSrc = Workbooks("Data.xlsx")
Set wbDest = Workbooks("Results.xlsx")
' set reference to all sheets by name, don't rely on ActiveSheet
Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
' copy range from "Data" workbook to "Results" workbook without using Select
shtCopy.Range("A1:C10").Copy _
Destination:=shtPaste.Range("A1")
End Sub
वर्कशीटफ़ंक्शन ऑब्जेक्ट एक यूडीएफ समकक्ष से अधिक तेज़ी से निष्पादित होता है
VBA को रन-टाइम में संकलित किया जाता है, जिसका इसके प्रदर्शन पर बहुत बड़ा नकारात्मक प्रभाव पड़ता है, सब कुछ अंतर्निहित तेजी से होगा, उनका उपयोग करने का प्रयास करें।
एक उदाहरण के रूप में मैं SUM और COUNTIF फ़ंक्शंस की तुलना कर रहा हूं, लेकिन यदि आप किसी भी चीज़ के लिए आप WorkSheetFunctions के साथ हल कर सकते हैं, तो आप उसका उपयोग कर सकते हैं।
उन लोगों के लिए पहला प्रयास रेंज के माध्यम से लूप करना होगा और सेल द्वारा इसे प्रोसेस करना होगा (एक रेंज का उपयोग करके):
Sub UseRange()
Dim rng as Range
Dim Total As Double
Dim CountLessThan01 As Long
Total = 0
CountLessThan01 = 0
For Each rng in Sheets(1).Range("A1:A100")
Total = Total + rng.Value2
If rng.Value < 0.1 Then
CountLessThan01 = CountLessThan01 + 1
End If
Next rng
Debug.Print Total & ", " & CountLessThan01
End Sub
श्रेणी मानों को सरणी और प्रक्रिया में संग्रहीत करने के लिए एक सुधार हो सकता है:
Sub UseArray()
Dim DataToSummarize As Variant
Dim i As Long
Dim Total As Double
Dim CountLessThan01 As Long
DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
Total = 0
CountLessThan01 = 0
For i = 1 To 100
Total = Total + DataToSummarize(i, 1)
If DataToSummarize(i, 1) < 0.1 Then
CountLessThan01 = CountLessThan01 + 1
End If
Next i
Debug.Print Total & ", " & CountLessThan01
End Sub
लेकिन किसी भी लूप को लिखने के बजाय आप Application.Worksheetfunction
उपयोग कर सकते हैं जो सरल सूत्रों को निष्पादित करने के लिए बहुत उपयोगी है:
Sub UseWorksheetFunction()
Dim Total As Double
Dim CountLessThan01 As Long
With Application.WorksheetFunction
Total = .Sum(Sheets(1).Range("A1:A100"))
CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
End With
Debug.Print Total & ", " & CountLessThan01
End Sub
या, अधिक जटिल गणनाओं के लिए आप Application.Evaluate
उपयोग भी कर सकते हैं।
Sub UseEvaluate()
Dim Total As Double
Dim CountLessThan01 As Long
With Application
Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
external:=True) & ")")
CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
End With
Debug.Print Total & ", " & CountLessThan01
End Sub
और अंत में, प्रत्येक पर 25,000 बार से ऊपर चल रहा है, यहाँ मिलीसेकंड में औसत (5 परीक्षण) समय है (बेशक यह प्रत्येक पीसी पर अलग होगा, लेकिन एक दूसरे की तुलना में वे समान व्यवहार करेंगे):
- UseWorksheetFunction: 2156 एमएस
- UseArray: 2219 एमएस (+ 3%)
- उपयोग: 4693 एमएस (+ 118%)
- UseRange: 6530 एमएस (+ 203%)
गुणों या विधियों के नामों को अपने चर के रूप में पुन: शुद्ध करने से बचें
यह आमतौर पर अपने स्वयं के प्रक्रियाओं और चर के नाम (ओं) के रूप में गुणों या विधियों के आरक्षित नामों को फिर से प्रयोजन के लिए 'सर्वोत्तम अभ्यास' नहीं माना जाता है।
बुरा फॉर्म - जबकि निम्नलिखित (कडाई के साथ) है कानूनी, काम कर कोड का पुनः उद्देश्य तय ढूँढें विधि के साथ ही पंक्ति , कॉलम और पता गुण नाम अस्पष्टता के साथ समस्याओं / संघर्ष हो सकते हैं और सिर्फ सादा सामान्य रूप में भ्रामक है।
Option Explicit
Sub find()
Dim row As Long, column As Long
Dim find As String, address As Range
find = "something"
With ThisWorkbook.Worksheets("Sheet1").Cells
Set address = .SpecialCells(xlCellTypeLastCell)
row = .find(what:=find, after:=address).row '< note .row not capitalized
column = .find(what:=find, after:=address).column '< note .column not capitalized
Debug.Print "The first 'something' is in " & .Cells(row, column).address(0, 0)
End With
End Sub
अच्छा रूप - सभी आरक्षित शब्दों के मूल के अद्वितीय लेकिन अद्वितीय सन्निकटन में बदल दिए गए, किसी भी संभावित नामकरण संघर्ष से बचा गया है।
Option Explicit
Sub myFind()
Dim rw As Long, col As Long
Dim wht As String, lastCell As Range
wht = "something"
With ThisWorkbook.Worksheets("Sheet1").Cells
Set lastCell = .SpecialCells(xlCellTypeLastCell)
rw = .Find(What:=wht, After:=lastCell).Row '◄ note .Find and .Row
col = .Find(What:=wht, After:=lastCell).Column '◄ .Find and .Column
Debug.Print "The first 'something' is in " & .Cells(rw, col).Address(0, 0)
End With
End Sub
हालांकि एक समय ऐसा भी आ सकता है जब आप जानबूझकर किसी मानक विधि या संपत्ति को अपने विनिर्देशों के लिए फिर से लिखना चाहते हैं, वे परिस्थितियाँ कुछ कम और बहुत दूर हैं। अधिकांश भाग के लिए, अपने स्वयं के निर्माण के लिए आरक्षित नामों का पुन: उपयोग करने से दूर रहें।