Introduction to Excel Macro Structures and Loops
EXCEL MACRO (PART I
)
1. DO-WHILE
2.DO-UNTIL
3.USING EXCEL FUNCTIONS
1
DO-UNTIL LOOP
•
The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested
condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the
program executes the loop until the condition is true.
•
Like as:
2
DO-WHILE LOOP
•
VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues
until a specified condition is met.
•
Like as: The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down
the column, multiplying each cell’s value by 2. The loop continues until the routine encounters an empty cell.
3
EXAMPLE 1
•
Write a program to represent a Fibonacci series from 1-1000th member, using Do-while
loop.
4
Sub Fibonacci()
Dim i As Integer
Dim iFib As Integer
Dim iFib_Next As Integer
Dim iStep As Integer
i = 1
iFib_Next = 0
Do While iFib_Next < 1000
If i = 1 Then
iStep = 1
iFib = 0
Else
iStep = iFib
iFib = iFib_Next
End If
Cells(i, 1).Value = iFib
iFib_Next = iFib + iStep
i = i + 1
Loop
End Sub
EXAMPLE 2
•
Write a program to provide regarding to following conditions (using Do-While loop).
•
•
Start to counter from 0 with considering a constant number i=5.
•
•
Each time i should be reduced by 1 until i>5.
•
•
Show that the output of counter
5
Sub doWhile ()
Dim i As Integer
Dim iTotal As Integer
i = 5
iTotal = 0
Do
iTotal = i + iTotal
i = i - 1
Loop While i > 5
MsgBox iTotal
End Sub
DO-UNTIL LOOP
•
The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested
condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the
program executes the loop until the condition is true.
•
Like as:
6
EACH-NEXT LOOPS
•
VBA supports yet another type of looping — looping through each object in a collection of objects. Recall
that a collection consists of a number of objects of the same type. For example, Excel has a collection of all
open workbooks (the Workbooks collection), and each workbook has a collection of worksheets (the
Worksheets collection).
7
EACH-NEXT LOOPS
•
When you need to loop through each object in a collection, use the For Each- Next structure. The following
example loops through each worksheet in the active workbook and deletes the worksheet if it’s empty:
8
EACH-NEXT LOOPS
•
In this example, the variable WkSht is an object variable that represents each worksheet in the workbook.
Nothing is special about the variable name WkSht you can use any variable name that you like.
•
Here’s another For Each-Next example. This procedure uses a loop to hide all worksheets in the active
workbook, except the active sheet.
9
USING EXCEL FUNCTIONS
10
Except for a few people who think Excel is a word processor, all Excel users incorporate worksheet functions in
their formulas. The most common worksheet function is the SUM function, and you have hundreds of others at
your disposal.
VBA provides numerous built-in functions. Some of these functions take arguments, and some do not.
Like as:
USING EXCEL FUNCTIONS
11
Finding a string length
The following procedure uses the VBA Len function, which returns the length of a
text string. The Len function takes one argument: the string. When you execute this
procedure, the message box displays your name, and the number of characters in
your name.
USING EXCEL FUNCTIONS
12
Displaying the name of a month
The following procedure uses the Month Name function, which returns the name of a month. Month Name uses one
argument, an integer between 1 and 12.
USING EXCEL FUNCTIONS
13
Discovering VBA functions
How do you find out which functions VBA provides? Good question. The best source is the Excel Visual
Basic Help system. Another way is to type VBA, followed by a period.
USING EXCEL FUNCTIONS
14
VBA’s Most Useful Built-In Functions
USING EXCEL FUNCTIONS
15
VBA’s Most Useful Built-In Functions continue…
USING EXCEL FUNCTIONS
16
VBA’s Most Useful Built-In Functions continue…
USING EXCEL FUNCTIONS
17
VBA’s Most Useful Built-In Functions continue…
EXAMPLE
18
Write a program to find out the maximum, minimum, and average of a specific range of worksheets.
Sub ShowMax()
Dim TheMax As Double
TheMax = WorksheetFunction.Max(Range("A:A"))MsgBox TheMax
End Sub
Explore different looping structures in Excel macros such as Do-While, Do-Until, and Each-Next loops. Learn how to use these loops with examples to create efficient programs. Dive into the world of VBA programming to automate tasks and improve productivity.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
EXCEL MACRO (PART I) 1. DO-WHILE 2.DO-UNTIL 3.USING EXCEL FUNCTIONS 1
DO-UNTIL LOOP The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true. Like as: 2
DO-WHILE LOOP VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified condition is met. Like as: The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell s value by 2. The loop continues until the routine encounters an empty cell. 3
EXAMPLE 1 Write a program to represent a Fibonacci series from 1-1000th member, using Do-while loop. Sub Fibonacci() Else Dim i As Integer iStep = iFib Dim iFib As Integer iFib = iFib_Next Dim iFib_Next As Integer End If Dim iStep As Integer Cells(i, 1).Value = iFib i = 1 iFib_Next = iFib + iStep iFib_Next = 0 i = i + 1 Do While iFib_Next < 1000 Loop If i = 1 Then End Sub iStep = 1 iFib = 0 4
EXAMPLE 2 Write a program to provide regarding to following conditions (using Do-While loop). Start to counter from 0 with considering a constant number i=5. Sub doWhile () Each time i should be reduced by 1 until i>5. Dim i As Integer Dim iTotal As Integer Show that the output of counter i = 5 iTotal = 0 Do iTotal = i + iTotal i = i - 1 Loop While i > 5 MsgBox iTotal End Sub 5
DO-UNTIL LOOP The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true. Like as: 6
EACH-NEXT LOOPS VBA supports yet another type of looping looping through each object in a collection of objects. Recall that a collection consists of a number of objects of the same type. For example, Excel has a collection of all open workbooks (the Workbooks collection), and each workbook has a collection of worksheets (the Worksheets collection). 7
EACH-NEXT LOOPS When you need to loop through each object in a collection, use the For Each- Next structure. The following example loops through each worksheet in the active workbook and deletes the worksheet if it s empty: 8
EACH-NEXT LOOPS In this example, the variable WkSht is an object variable that represents each worksheet in the workbook. Nothing is special about the variable name WkSht you can use any variable name that you like. Here s another For Each-Next example. This procedure uses a loop to hide all worksheets in the active workbook, except the active sheet. 9
USING EXCEL FUNCTIONS Except for a few people who think Excel is a word processor, all Excel users incorporate worksheet functions in their formulas. The most common worksheet function is the SUM function, and you have hundreds of others at your disposal. VBA provides numerous built-in functions. Some of these functions take arguments, and some do not. Like as: 10
USING EXCEL FUNCTIONS Finding a string length The following procedure uses the VBA Len function, which returns the length of a text string. The Len function takes one argument: the string. When you execute this procedure, the message box displays your name, and the number of characters in your name. 11
USING EXCEL FUNCTIONS Displaying the name of a month The following procedure uses the Month Name function, which returns the name of a month. Month Name uses one argument, an integer between 1 and 12. 12
USING EXCEL FUNCTIONS Discovering VBA functions How do you find out which functions VBA provides? Good question. The best source is the Excel Visual Basic Help system. Another way is to type VBA, followed by a period. 13
USING EXCEL FUNCTIONS VBA s Most Useful Built-In Functions 14
USING EXCEL FUNCTIONS VBA s Most Useful Built-In Functions continue 15
USING EXCEL FUNCTIONS VBA s Most Useful Built-In Functions continue 16
USING EXCEL FUNCTIONS VBA s Most Useful Built-In Functions continue 17
EXAMPLE Write a program to find out the maximum, minimum, and average of a specific range of worksheets. Sub ShowMax() Dim TheMax As Double TheMax = WorksheetFunction.Max(Range("A:A")) MsgBox TheMax End Sub 18
