Introduction to Excel Macro Structures and Loops
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
