excel-vba
एक्सेल-वीबीए ऑप्टिमाइज़ेशन
खोज…
परिचय
एक्सेल-वीबीए ऑप्टिमाइज़ेशन से तात्पर्य दस्तावेज़ीकरण और अतिरिक्त विवरणों द्वारा बेहतर त्रुटि से निपटने में कोडिंग से भी है। यह यहाँ दिखाया गया है।
टिप्पणियों
*) रेखा संख्याएँ पूर्णांक हैं, जो एक हस्ताक्षरित 16 बिट डेटा प्रकार -32,768 से 32,767 की सीमा में है, अन्यथा आप एक अतिप्रवाह का उत्पादन करते हैं। आमतौर पर लाइन नंबरों को कोड के एक हिस्से या एक मॉड्यूल के सभी प्रक्रियाओं के 10 से अधिक चरणों में डाला जाता है।
वर्कशीट अपडेट करना अक्षम करना
वर्कशीट की गणना को अक्षम करने से मैक्रो के रनिंग समय में काफी कमी आ सकती है। इसके अलावा, ईवेंट को अक्षम करना, स्क्रीन अपडेट करना और पेज ब्रेक करना फायदेमंद होगा। इस प्रयोजन के लिए किसी भी मैक्रो में निम्नलिखित Sub
का उपयोग किया जा सकता है।
Sub OptimizeVBA(isOn As Boolean)
Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
Application.EnableEvents = Not(isOn)
Application.ScreenUpdating = Not(isOn)
ActiveSheet.DisplayPageBreaks = Not(isOn)
End Sub
अनुकूलन के लिए नीचे दिए गए छद्म कोड का पालन करें:
Sub MyCode()
OptimizeVBA True
'Your code goes here
OptimizeVBA False
End Sub
निष्पादन का समय जाँच रहा है
विभिन्न प्रक्रियाएं एक ही परिणाम दे सकती हैं, लेकिन वे विभिन्न प्रसंस्करण समय का उपयोग करेंगे। यह जांचने के लिए कि कौन सा तेज है, इस तरह के एक कोड का उपयोग किया जा सकता है:
time1 = Timer
For Each iCell In MyRange
iCell = "text"
Next iCell
time2 = Timer
For i = 1 To 30
MyRange.Cells(i) = "text"
Next i
time3 = Timer
debug.print "Proc1 time: " & cStr(time2-time1)
debug.print "Proc2 time: " & cStr(time3-time2)
Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
getTickCount cyTicks1 'Get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function
ब्लॉक के साथ उपयोग करना
ब्लॉक के साथ उपयोग करने से मैक्रो को चलाने की प्रक्रिया में तेजी आ सकती है। एक सीमा, चार्ट नाम, कार्यपत्रक आदि लिखने के बजाय, आप नीचे दिए गए ब्लॉक के साथ उपयोग कर सकते हैं;
With ActiveChart
.Parent.Width = 400
.Parent.Height = 145
.Parent.Top = 77.5 + 165 * step - replacer * 15
.Parent.Left = 5
End With
जो इससे तेज है:
ActiveChart.Parent.Width = 400
ActiveChart.Parent.Height = 145
ActiveChart.Parent.Top = 77.5 + 165 * step - replacer * 15
ActiveChart.Parent.Left = 5
टिप्पणियाँ:
एक बार ब्लॉक के साथ प्रवेश करने के बाद, ऑब्जेक्ट को बदला नहीं जा सकता है। परिणामस्वरूप, आप विभिन्न वस्तुओं की एक संख्या को प्रभावित करने के लिए एक कथन के साथ एक का उपयोग नहीं कर सकते
के साथ या ब्लॉक के बाहर कूद मत करो । यदि किसी ब्लॉक में दिए गए स्टेटमेंट निष्पादित किए जाते हैं, लेकिन या तो स्टेटमेंट के साथ या एंड के साथ निष्पादित नहीं किया जाता है, तो ऑब्जेक्ट के संदर्भ में एक अस्थायी चर स्मृति में रहता है जब तक आप प्रक्रिया से बाहर नहीं निकल जाते हैं।
बयानों के साथ अंदर लूप न करें, खासकर अगर कैश्ड ऑब्जेक्ट का उपयोग इट्रेटर के रूप में किया जाता है
आप एक दूसरे के भीतर ब्लॉक के साथ बयान देकर घोंसला बना सकते हैं। हालाँकि, क्योंकि बाहरी के साथ ब्लॉकों के सदस्यों को आंतरिक के साथ ब्लॉक के भीतर मास्क किया जाता है, आपको किसी बाहरी के साथ ब्लॉक में किसी ऑब्जेक्ट के किसी भी सदस्य को ब्लॉक के साथ एक आंतरिक में पूरी तरह से योग्य ऑब्जेक्ट संदर्भ प्रदान करना होगा।
घोंसले के शिकार उदाहरण:
यह उदाहरण एक वस्तु पर बयानों की एक श्रृंखला को निष्पादित करने के लिए विथ स्टेटमेंट का उपयोग करता है।
ऑब्जेक्ट और उसके गुण केवल उदाहरण के प्रयोजनों के लिए उपयोग किए जाने वाले सामान्य नाम हैं।
With MyObject
.Height = 100 'Same as MyObject.Height = 100.
.Caption = "Hello World" 'Same as MyObject.Caption = "Hello World".
With .Font
.Color = Red 'Same as MyObject.Font.Color = Red.
.Bold = True 'Same as MyObject.Font.Bold = True.
MyObject.Height = 200 'Inner-most With refers to MyObject.Font (must be qualified
End With
End With
MSDN पर अधिक जानकारी
पंक्ति विलोपन - प्रदर्शन
पंक्तियों को हटाना धीमा है, विशेष रूप से जब कोशिकाओं के माध्यम से लूपिंग और पंक्तियों को हटाने, एक-एक करके
एक अलग दृष्टिकोण हटाए जाने वाली पंक्तियों को छिपाने के लिए एक ऑटोफ़िल्टर का उपयोग कर रहा है
दृश्य सीमा की प्रतिलिपि बनाएँ और इसे एक नई कार्यपत्रक में पेस्ट करें
प्रारंभिक शीट को पूरी तरह से हटा दें
इस विधि के साथ, हटाने के लिए जितनी अधिक पंक्तियाँ होंगी, उतनी ही तेज़ी से होगी
उदाहरण:
Option Explicit
'Deleted rows: 775,153, Total Rows: 1,000,009, Duration: 1.87 sec
Public Sub DeleteRows()
Dim oldWs As Worksheet, newWs As Worksheet, wsName As String, ur As Range
Set oldWs = ThisWorkbook.ActiveSheet
wsName = oldWs.Name
Set ur = oldWs.Range("F2", oldWs.Cells(oldWs.Rows.Count, "F").End(xlUp))
Application.ScreenUpdating = False
Set newWs = Sheets.Add(After:=oldWs) 'Create a new WorkSheet
With ur 'Copy visible range after Autofilter (modify Criteria1 and 2 accordingly)
.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
oldWs.UsedRange.Copy
End With
'Paste all visible data into the new WorkSheet (values and formats)
With newWs.Range(oldWs.UsedRange.Cells(1).Address)
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteAll
newWs.Cells(1, 1).Select: newWs.Cells(1, 1).Copy
End With
With Application
.CutCopyMode = False
.DisplayAlerts = False
oldWs.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
newWs.Name = wsName
End Sub
बड़े मैक्रोज़ को निष्पादित करने से पहले सभी एक्सेल कार्यक्षमता को अक्षम करना
कार्यपुस्तिका कार्यपुस्तिका और कार्यपत्रक स्तर पर सभी Excel सुविधाओं को अस्थायी रूप से अक्षम कर देगी
FastWB () एक टॉगल है जो ऑन या ऑफ झंडे को स्वीकार करता है
FastWS () एक वैकल्पिक वर्कशीट ऑब्जेक्ट या कोई भी स्वीकार नहीं करता है
यदि ws पैरामीटर गायब है, तो यह संग्रह में सभी वर्कशीट के लिए सभी सुविधाओं को चालू और बंद कर देगा
- कस्टम सेटिंग्स को बंद करने से पहले सभी सेटिंग्स को कैप्चर करने के लिए उपयोग किया जा सकता है
- प्रक्रिया के अंत में, प्रारंभिक सेटिंग्स को पुनर्स्थापित किया जा सकता है
Public Sub FastWB(Optional ByVal opt As Boolean = True)
With Application
.Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)
If .DisplayAlerts <> Not opt Then .DisplayAlerts = Not opt
If .DisplayStatusBar <> Not opt Then .DisplayStatusBar = Not opt
If .EnableAnimations <> Not opt Then .EnableAnimations = Not opt
If .EnableEvents <> Not opt Then .EnableEvents = Not opt
If .ScreenUpdating <> Not opt Then .ScreenUpdating = Not opt
End With
FastWS , opt
End Sub
Public Sub FastWS(Optional ByVal ws As Worksheet, Optional ByVal opt As Boolean = True)
If ws Is Nothing Then
For Each ws In Application.ThisWorkbook.Sheets
OptimiseWS ws, opt
Next
Else
OptimiseWS ws, opt
End If
End Sub
Private Sub OptimiseWS(ByVal ws As Worksheet, ByVal opt As Boolean)
With ws
.DisplayPageBreaks = False
.EnableCalculation = Not opt
.EnableFormatConditionsCalculation = Not opt
.EnablePivotTable = Not opt
End With
End Sub
डिफ़ॉल्ट रूप से सभी एक्सेल सेटिंग्स को पुनर्स्थापित करें
Public Sub XlResetSettings() 'default Excel settings
With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.DisplayStatusBar = True
.EnableAnimations = False
.EnableEvents = True
.ScreenUpdating = True
Dim sh As Worksheet
For Each sh In Application.ThisWorkbook.Sheets
With sh
.DisplayPageBreaks = False
.EnableCalculation = True
.EnableFormatConditionsCalculation = True
.EnablePivotTable = True
End With
Next
End With
End Sub
विस्तारित डिबगिंग द्वारा त्रुटि खोज का अनुकूलन
लाइन नंबरों का उपयोग ... और त्रुटि के मामले में उनका दस्तावेजीकरण ("अर्ल को देखने का महत्व")
यह पता लगाना कि कौन सी रेखा एक त्रुटि उठाती है, किसी भी डीबगिंग का एक महत्वपूर्ण हिस्सा है और कारण की खोज को बताती है। एक संक्षिप्त विवरण के साथ पहचान की गई त्रुटि लाइनों को दस्तावेज़ करने के लिए एक सफल त्रुटि ट्रैकिंग पूरी होती है, साथ में मॉड्यूल और प्रक्रिया के नामों के साथ। नीचे दिया गया उदाहरण इन डेटा को लॉग फ़ाइल में सहेजता है।
पृष्ठभूमि
त्रुटि ऑब्जेक्ट त्रुटि संख्या (Err.Number) और त्रुटि विवरण (Err.Description) देता है, लेकिन त्रुटि का पता लगाने के लिए स्पष्ट रूप से सवाल का जवाब नहीं देता है। Erl फ़ंक्शन, हालांकि, करता है, लेकिन इस शर्त पर कि आप कोड में * लाइन नंबर ) जोड़ते हैं (पूर्व मूल समय में कई अन्य रियायतों में से एक BTW)।
यदि कोई त्रुटि रेखाएँ बिल्कुल भी नहीं हैं, तो Erl फ़ंक्शन 0 देता है, यदि नंबरिंग अधूरी है तो आपको प्रक्रिया की अंतिम पूर्ववर्ती पंक्ति संख्या मिल जाएगी।
Option Explicit
Public Sub MyProc1()
Dim i As Integer
Dim j As Integer
On Error GoTo LogErr
10 j = 1 / 0 ' raises an error
okay:
Debug.Print "i=" & i
Exit Sub
LogErr:
MsgBox LogErrors("MyModule", "MyProc1", Err), vbExclamation, "Error " & Err.Number
Stop
Resume Next
End Sub
Public Function LogErrors( _
ByVal sModule As String, _
ByVal sProc As String, _
Err As ErrObject) As String
' Purpose: write error number, description and Erl to log file and return error text
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
Dim sLogTxt As String
Dim lFile As Long
' Create error text
sLogTxt = sModule & "|" & sProc & "|Erl " & Erl & "|Err " & Err.Number & "|" & Err.Description
On Error Resume Next
lFile = FreeFile
Open sLogFile For Append As lFile
Print #lFile, Format$(Now(), "yy.mm.dd hh:mm:ss "); sLogTxt
Print #lFile,
Close lFile
' Return error text
LogErrors = sLogTxt
End Function
' लॉग फाइल दिखाने के लिए अतिरिक्त कोड
Sub ShowLogFile()
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
On Error GoTo LogErr
Shell "notepad.exe " & sLogFile, vbNormalFocus
okay:
On Error Resume Next
Exit Sub
LogErr:
MsgBox LogErrors("MyModule", "ShowLogFile", Err), vbExclamation, "Error No " & Err.Number
Resume okay
End Sub