For Next Loop in excel BVA
लूप्स वीबीए में सबसे शक्तिशाली प्रोग्रामिंग टूल में से एक हैं,
और वे उपयोगकर्ताओं
को एक ही कोड ब्लॉक को कई बार दोहराने की अनुमति देते हैं जब तक कि एक विशिष्ट
बिंदु प्राप्त नहीं होता है या किसी दिए गए शर्त को पूरा नहीं किया जाता है।
Loops are one of the most powerful programming tools in VBA,
and they allow users to repeat the same code block multiple times until a
specific point is attained or a given condition is met.
यानि की जब हमको कोई
प्रोसैस बार बार करना होता है तो उस code
को copy, paste करने की बजाय उसके लिए हम code का एक sequence
लिखते हैं ओर वो sequence बार बार रन होता है जब तक की हमारे द्वारा दी हुई condition से मैच होता है। Loop
3 चीज़ों से मिलकर बना है पहला होता
है variable जहां से आप लूप को स्टार्ट करते हैं। इस variable को तब तक बढ़ाया (increment) किया जाता है जब तक दी गई condition False न हो जाए। यानि इस variable को loop की condition
मे include किया जाता है। दूसरे नौम्बर पर आती है condition, ये वो कोंडीटीन है जो loop
को कंट्रोल करती है। जब तक ये condition रहती है loop
Execute होता रहता है। ओर ये condition जैसे ही False
होती है तो loop terminate हो जाता है। ओर तीसरे नंबर पर आता है increment , आप किसी नंबर को या variable
को कहाँ तक increase करना चाहते हो इसके लिए increment
को बाद मे define किया जाता है।
मान लीजिये आपने 1 से 10
तक counting लिखनी है। तो इसके लिए हम code की मदद से नंबर को continue
प्लस करते जाएंगे ओर वो code तब तक चलता रहेगा जब तक की वो 10 तक न पहुँच जाए। उसके बाद वो
काम करना बंद कर देगा।
Types of Loop in Excel VBA
·
For
Next Loop
o Example
1 – Adding the first 10 positive integers
o Example
2 – Adding the first 5 Even Positive Integers
o Example
3 – Entering Serial Number in the Selected Cells
o Example
4 – Protect All Worksheets in the Active Workbook
o Nested ‘For Next’ Loops
o ‘EXIT For’ Statements in
For Next Loops
·
Do
While Loop
o Example
1 – Add First 10 Positive Integers using VBA
o Example
2 – Enter Dates For the Current Month
o Exit Do Statement
·
Do
Until Loop
o Example
1 – Add First 10 Positive Integers using VBA
o Example
2 – Enter Dates For the Current Month
o Exit Do Statement
·
For
Each
o Example
1 – Go through All the Worksheets in a Workbook (and Protect it)
o Example
2 – Go through All the Open Workbooks (and Save All)
o Example
3 – Go through All the Cells in a Selection (Highlight negative values)
o ‘Exit For’ Statment
For Next Loop
तो आज पहले हम पढ़ते हैं फॉर loop
For
Loop ये बताता है की आप एक loop को कितनी बार रन करना चाहते
हैं। 5, 10, 15,
20 या 100 बार।
The ‘For Next’ loop allows you to go through a block of code for
the specified number of times.
For example, if I ask you to add the integers from 1 to 10
manually, you would add the first two numbers, then add the third number to the
result, then add the fourth number to the result, as so on..
Isn’t it?
The same logic is used in the For Next loop in VBA.
You specify how many times you want the loop to run and also
specify what you want the code to do each time the loop is run.
इसके लिए हम एक code लिखते हैं तो सबसे पहले एक variable
declare करेंगे उसके लिए कोई भी variable डिक्लैर
कर सकते हैं। जैसे: मैं यहाँ Integer डिक्लैर करूंगा।
Option Explicit
Sub Fornext ()
Dim M as Integer
For M = 1 To 10
Next M
End Sub
इस
कोड़े मे मैंने जहां पर लिखा है next m तो यहाँ पर आ कर m की
वैल्यू change हो जाएगी। यानि आपने यहाँ पर ये लूप 10 बार
चलाना ओर ये 10 बार चलने के बाद अपने आप बाहर आ जाएगा। इसको चला कर देखने के लिए
आप msg बॉक्स को यूज भी कर सकते हैं ओर उसके लिए next
m से पहले टाइप करें MsgBox m ओर प्रोग्राम को
रन करें।
अप
हम इसको as a
प्रोग्राम रन कर्वना चाहते हैं excel मे तो
मान लो हमने 10 बार 1 नंबर को प्रिंट करना है तो उसके लिए कोड़े लिखेंगे:
Option Explicit
Sub Fornext ()
Dim M as Integer
For M = 1 To 10
अब
मान लीजिये आपने 1 se
10 तक counting लिखनी हैं तो हम code किस प्रकार लिखेंगे।
Option Explicit
Sub Fornext ()
Dim M as Integer
For M = 1 To 10
अब
इसमे आप देखेंगे की हमें m
को 3 जगह इस्तेमाल किया है ओर ज्यादा code भी
नहीं लिखना पड़ा।
Below is the syntax of the For Next loop:
For Counter
= Start To End [Step Value]
[Code Block
to Execute]
Next
[counter]
In the For Next loop, you can use a Counter (or any variable)
that will be used to run the loop. This counter allows you to run this loop for
a required number of times.
For example, if I want to add the first 10 positive integers,
then my Counter value would be from 1 to 10.
Let’s have a look at a few examples to better understand how For
Next loop works.
Example 1 – Adding the first 10 positive integers
Below is the code that will add the first 10 positive integers
using a For Next loop.
It will then display a message box showing the sum of these
numbers.
Sub
AddNumbers()
Dim Total
As Integer
Dim Count
As Integer
Total = 0
For Count =
1 To 10
Total =
Total + Count
Next Count
MsgBox
Total
End Sub
In this code, the value of Total is set to 0 before getting into
the For Next loop.
Once it gets into the loop, it holds the total value after every
loop. So after the first loop, when Counter is 1, ‘Total’ value becomes 1, and
after the second loop it becomes 3 (1+2), and so on.
And finally, when the loop ends, ‘Total’ variable has the sum of
the first 10 positive integers.
A MsgBox then simply displays the result in a message box.
Example 2 – Adding the first 5 Even Positive Integers
To sum the first five even positive integers (i.e, 2,4,6,8, and
10), you need a similar code with a condition to only consider the even numbers
and ignore the odd numbers.
Here is a code that will do it:
Sub
AddEvenNumbers()
Dim Total
As Integer
Dim Count
As Integer
Total = 0
For Count =
2 To 10 Step 2
Total =
Total + Count
Next Count
MsgBox
Total
End Sub
Note that we started
the Count value from 2 and also used ‘Step 2‘.
When you use ‘Step 2’, it tells the code to increment the ‘Count’
value by 2 every time the loop is run.
So the Count value starts from 2 and then becomes 4, 6, 8 and 10
as the looping occurs.
NOTE: Another way of doing this could be to run the loop from 1
to 10 and within the loop check whether the number is even or odd. However,
using Step, in this case, is a more efficient way as it does not require the
loop to run 10 times, but only 5 times.
The Step value can also be negative. In such as case, the
Counter starts at a higher value and keeps getting decremented by the specified
Step value.
Example 3 – Entering Serial Number in the Selected Cells
You can also use the For Next loop to go through a collection of
objects (such as cells or worksheets or workbooks),
Here is an example
that quickly enters serial numbers in all the selected cells.
Sub
EnterSerialNumber()
Dim Rng As
Range
Dim Counter
As Integer
Dim RowCount
As Integer
Set Rng =
Selection
RowCount =
Rng.Rows.Count
For Counter
= 1 To RowCount
ActiveCell.Offset(Counter
- 1, 0).Value = Counter
Next
Counter
End Sub
The above code first counts the number of selected rows and then
assigns this value to the variable RowCount. We then run the loop from ‘1 to
RowCount’.
Also note that since selection can be any number of rows, we
have Set the variable Rng to Selection (with the line ‘Set Rng = Selection’).
Now we can use the ‘Rng’ variable to refer to the selection in our code.
Example 4 – Protect All Worksheets in the Active Workbook
You can use the ‘For Next’ loop to go through all the worksheets
in the active workbook, and protect (or unprotect) each of the worksheets.
Below is the code that will do this:
Sub
ProtectWorksheets()
Dim i As
Integer
For i = 1
To ActiveWorkbook.Worksheets.Count
Worksheets(i).Protect
Next i
End Sub
The above code counts the number of sheets by
using ActiveWorkbook.Worksheets.Count. This tells VBA how many times the
loop needs to be run.
In each instance, it refers to the Ith workbook
(using Worksheets(i)) and protects it.
You can use this same
code to Unprotect worksheets too. Just change the line Worksheets(i).Protect to Worksheets(i).UnProtect.
Nested ‘For Next’ Loops
You can use nested ‘For Next’ loops to get more complex
automation done in Excel. A nested ‘For Next’ loop would mean that there is a
‘For Next’ loop within a ‘For Next’ loop.
Let me show you how to use this using an example.
Suppose I have 5 workbooks open in my system and I want to
protect all the worksheets in all these workbooks.
Below is the code that will do this:
Sub
ProtectWorksheets()
Dim i As
Integer
Dim j As
Integer
For i = 1
To Workbooks.Count
For j = 1
To Workbooks(i).Worksheets.Count
Workbooks(i).Worksheets(j).Protect
Next j
Next i
End Sub
The above is a
nested For Next loop as we have used one For Next loop within
another.
‘EXIT For’ Statements in For Next Loops
‘Exit For’ statement allows you to exit the ‘For Next’ loop
completely.
You can use it in cases where you want the For Next loop to end
when a certain condition is met.
Let’s take an example where you have a set of numbers in Column
A and you want to highlight all the negative numbers in red font. In this case,
we need to analyze each cell for its value and then change the font color
accordingly.
But to make the code more efficient, we can first check if there
are any negative values in the list or not. If there are no negative values, we
can use the Exit For the statement to simply come out of the code.
Below is the code that does this:
Sub
HghlightNegative()
Dim Rng As
Range
Set Rng =
Range("A1", Range("A1").End(xlDown))
Counter =
Rng.Count
For i = 1
To Counter
If
WorksheetFunction.Min(Rng) >= 0 Then Exit For
If
Rng(i).Value < 0 Then Rng(i).Font.Color = vbRed
Next i
End Sub
When you use the ‘Exit For’ statement within a nested ‘For Next’
loop, it will come out of the loop in which it is executed and go on to execute
the next line in the code after the For Next loop.
For example, in the below code, the ‘Exit For’ statement will
get you out of the inner loop, but the outer loop would continue to work.
Sub
SampleCode()
For i = 1
To 10
For j = 1
to 10
Exit For
Next J
Next i
End Sub
Post a Comment