VBA Tutorial Week 3 Nesting branches and loops

VBA: Tutorial Week 3 • • • Nesting: branches and loops Branching, looping and the Inline. Shapes collection Accessing document names (DIR) & opening documents (Documents collection) Option Explicit Using the VBA debugger Official resource for MS-Office products: https: //support. office. com

Microsoft Introduction/Overview Of VBA • https: //docs. microsoft. com/en-us/office/vba/libraryreference/concepts/getting-started-with-vba-in-office VBA tutorial notes by James Tam

Activities In Tutorial • TA demos: – Used for more complex features (typically multiple steps are required). – The tutorial instructor will show on the projector/instructor computer each step for running the feature in Excel. – Unless otherwise specified the tutorial material will take the form of a TA demonstrating the use of features in Excel. – Slides titled “Lecture Review” are covered for the second time and dealing with less complex material. • For this reason they will only be covered briefly in tutorial. • Student exercises: – Used instead of TA demos for simpler features. – You will have already been given a summary of how to invoke the feature and the purpose of the exercise is to give you a chance to try it out and get help if needed. VBA tutorial notes by James Tam

Nesting: What You Know • Nesting: a construct (e. g. IF) is nested inside of another construct (e. g. IF) when the second construct is part of the body of the first construct. • Example: If (country = "Canada") then If (province = "AB") then Msg. Box ("Greatest place on earth ^-*") End If 'Checking province End If 'Checking country – Recall: the check for the Boolean expression for the second IF does not occur unless the first Boolean expression is true. (Don’t bother checking if province is AB if country isn’t Canada). VBA tutorial notes by James Tam

Nesting: What You Will See • Branches and loops can be nested within each other Scenario 1 Do while (Boolean) If (Boolean) then. . . End if Loop VBA tutorial notes by James Tam Scenario 2 If (Boolean) then Do while (Boolean). . . Loop End if

Recognizing When Nesting Is Needed • Scenario 1: If a question answers true then check if a process should be repeated. – Example: If the user entered an odd number then count through a sequence 1 to this number and display each odd number in this sequence. – Type of nesting: a Do-While loop nested inside of an IF-branch If (Boolean) then Do While (Boolean). . . Loop End If VBA tutorial notes by James Tam F Q: Odd #? T Q: Not yet exceeded last #? T Display current # Increment to next odd # Done F

(Key Part: Do-While) Nested Inside An IF • Word document containing the example: 1_nesting_loop_in_branch 'Variable & constant declaration excluded for brevity last. Odd = Input. Box("Enter last odd number in sequence: ") remainder = last. Odd Mod 2 If (remainder = 0) Then Msg. Box (last. Odd & " is even not odd. ") Else If (last. Odd <= MAX_ODD) Then count = 1 Do While (count <= last. Odd) Msg. Box ("Current number = " & count) count = count + 2 Loop End If 'End: checks size of last # End If 'End: checks if # is odd or even VBA tutorial notes by James Tam

Recognizing When Nesting Is Needed • Scenario 2: As long some condition is met a question will be asked. As long as some condition is met a popup will be displayed. – Example: While the last number in a sequence hasn’t been exceeded if the current number is even it will be displayed. – Type of nesting: an IF-branch nested inside of a Do-While loop Do While (Boolean) If (Boolean) then. . . End If Loop VBA tutorial notes by James Tam Q: As last # not exceeded? F T Q: Even? F T Display # Increment to next # Done

(Key Part: IF Nested) Inside A Do-While • Word document containing the example: 2_nesting_branch_in_loop Const MAX_NUMBER As Long = 20 Dim last. Number As Long Dim count As Long Dim remainder As Long last. Number = Input. Box("Enter last number in a sequence: ") If (last. Number <= MAX_NUMBER) Then count = 1 Do While (count <= last. Number) remainder = count Mod 2 If (remainder = 0) Then Msg. Box ("Current even #: " & count) End If count = count + 1 Loop End If VBA tutorial notes by James Tam

Return To Collections • Recall with the collections you have seen: Documents, Inline. Shapes, Tables you can access a particular element or item in the collection by that item’s index. – Example (accesses the first Inline. Shape): Active. Document. Inline. Shapes(1) • Also the number of items in the collection can be accessed through the collection’s count attribute. – Example (the variable num. Tables will contain the current number of tables in the currently active Word document): num. Tables = Active. Document. Tables. count VBA tutorial notes by James Tam

Return To Collections (2) • Now that you have learned how to use looping and branching constructs, you can: – Access each item in a collection (using a loop). – Check if the number of items in the collection is the desired amount (using a branch). VBA tutorial notes by James Tam

Collections: Inline Shapes • Example program: 3_loop_branch_inline_shapes – For documents containing 2 - 4 Inline. Shapes (images) the program will halve the size of odd numbered images. Sub reduce. Odd. Inline. Shapes() Const MIN_SHAPES As Long = 2 Const MAX_SHAPES As Long = 4 Dim count As Long Dim num. Shapes As Long Dim temp. Width As Long num. Shapes = Active. Document. Inline. Shapes. count If ((num. Shapes < MIN_SHAPES) Or (num. Shapes > MAX_SHAPES)) Then Msg. Box ("Number of Inline Shapes not " & MIN_SHAPES & _ "-" & MAX_SHAPES) VBA tutorial notes by James Tam

Collections: Inline Shapes (2) Else count = 1 Do While (count <= num. Shapes) If ((count Mod 2) = 0) Then temp. Width = Active. Document. Inline. Shapes(count). Width / 2 Active. Document. Inline. Shapes(count). Width = temp. Width End If count = count + 1 Loop End If End Sub VBA tutorial notes by James Tam

Student Exercise 1 • Write a program that will prompt the user for a positive integer value (1 or greater). • The program will then double the size of the item # of the Inline Shape in the currently active document. • Name of the document containing the solution: exercise 1 VBA tutorial notes by James Tam

Student Exercise 2 • Modify the solution to the previous exercise so that the program error checks the user’s input. • If the value enter by the user is less than 1 or it exceeds the current number of In line shapes in the document: – The program will display an error message specifying the correct range of values that can be entered (it needs to be based on the actual number of shapes in the currently active document). – The program will repeat the prompt until a value within the correct range has been entered. • Name of the document containing the solution: exercise 2 VBA tutorial notes by James Tam

Counting Occurrences Of A Word • It’s an application of the ‘Find’ method of the Active. Document object combined with looping. • Why count occurrences: – Evaluating resumes by matching skills sought vs. skills listed by the applicant. – Ranking the relevance of a paper vs. a search topic by the number of times that the topic is mentioned. • Word frequency may be one criteria employed when websites rank search results according to relevance VBA tutorial notes by James Tam

Checking Occurrences • Word document containing the macro (actually it checks if word is or isn’t found rather than doing an actual count but a small modification will allow a count to be performed): • 4_determine_if_word_occurs Sub checking. Occurence() Dim occurs As Boolean Dim search. Word As String search. Word = Input. Box("Word to search for") occurs = False With Active. Document. Content. Find Do While. Execute(Find. Text: =search. Word, Forward: =True, _ Match. Whole. Word: =True) = True occurs = True 'Word was found change state Loop End With VBA tutorial notes by James Tam

• • True: search for exact word False: partial match counted e. g. when looking for ‘the’ words like ‘there’ are counted Checking Occurrences Search not started, assume Word not in document • Word document containing the macro (actually it checks if word is or isn’t found rather than doing an actual count but a small modification will allow a count to be performed): • 4_determine_if_word_occurs Word to find Sub checking. Occurence() in document Dim occurs As Boolean Dim search. Word As String search. Word = Input. Box("Word to search for") occurs = False With Active. Document. Content. Find Do While. Execute(Find. Text: =search. Word, Forward: =True, _ Match. Whole. Word: =True) = True occurs = True Body of Do-While entered Loop when a match occurs (in this End With case can set variable to indicate that it’s true that wor was found) VBA tutorial notes by James Tam

Checking Occurrences • Once the search is complete display the results of the search If (occurs = True) Then Msg. Box ("'" & search. Word & "'" & " was found") Else Msg. Box ("'" & search. Word & "'" & " could not be found") End If End Sub VBA tutorial notes by James Tam

Student Exercise 3 • Modify the previous program. Instead of determining if the search word was or was not found have your program count the number of occurrences. – A word should be counted if it’s a partial match e. g. when search for ‘the’ the words ‘the’, ‘their’, ‘they’re’ and ‘there’ should all be counted. • After the search is complete the number of occurrences should be displayed in a popup • Name of the document containing the solution: exercise 3 – Example data used to test the correctness of your solution. – Search for ‘the’, count should be 2 – Search for ‘at’, count should be 2 – Search for ‘t’, count should be 4 VBA tutorial notes by James Tam

General Approach For Automatically Opening/Processing Word Documents In VBA • The DIR function will return the name of files (including Word documents) at a specified location. • Given the name of a Word document the methods of the Documents collection can open that document. • After a document has been opened it becomes the currently active document (when another document is opened then the second document becomes the currently active document). • Methods/attributes of the Active. Document collection can then be used to process the currently active document e. g. word count, find a word, get information about the collections in that document (such as tables, images etc. ) – The document can also be modified (e. g. font effects, writing text to the document, modifying items in a collection etc. ) VBA tutorial notes by James Tam

Document Processing Example • Prompts user for a path (location to folder containing the documents). • Repeatedly prompts so long as the path is empty. • If the path is not empty the program checks if the folder does not contain Word documents, if so an error message appears and program ends. • If folder does contain documents: 1. Display the current name of the document in a popup 2. Open the document 3. If the opened document contains no shapes then write an error message into the document with enhanced font effects 4. If the opened document does contain shapes the successive set the fill color of each shape to red (from first to last) VBA tutorial notes by James Tam

Document Processing Example (2) • (Folder does contain documents continued) 5. Automatically save and close the document. 6. Move onto the next document (get the name) and apply Steps 1 – 6 to it. • Name of the Word document containing the example program: 5_set_fill_color_for_all_documents_in_a_folder VBA tutorial notes by James Tam

Document Processing Example Sub set. Fill. All. Folder. Documents() Const ERROR_MESSAGE As String = "No shapes in document to fill" Dim location As String Repeatedly Dim current. Document. Name As String prompt if the Dim fullname As String user didn't enter any Dim current. Shape As Long location Dim num. Shapes As Long location = "" current. Document. Name = "" Do While (location = "") location = Input. Box("Enter path to Word documents " & _ "(e. g. C: temp): ") If (location = "") Then Msg. Box ("You entered '" & location & _ "', don't enter an empty location") End If Loop VBA tutorial notes by James Tam

Document Processing Example (2) Separator between last containing folder and filename location = location & "" current. Document. Name = Dir(location & "*. doc*") Only consider and open Word (2003 or 2007+) documents If (current. Document. Name = "") Then Msg. Box ("Unable to retrieve any docs in the " & _ "specified location") Check if program is unable to find Word documents in location VBA tutorial notes by James Tam

Document Processing Example (3) Else Do While (current. Document. Name <> "") Msg. Box (current. Document. Name) fullname = location & current. Document. Name Documents. Open (fullname) If no shapes in doc write error message shape in VBA tutorial notes by James Tam Loop executes so long as there is another Word document that hasn't already been accessed Need path and full name to open a document num. Shapes = Active. Document. Shapes. Count If (num. Shapes = 0) Then Selection. Font. Bold = True Selection. Font. Color. Index = wd. Red Selection. Font. Size = 24 Selection. Type. Text (ERROR_MESSAGE) Else 'Starting with first shape so long as there's another Set fill color to red for all shapes, 'document repeat loop current. Shape = 1

There’s 1+ shapes in doc Document Processing Example (4) Else current. Shape = 1 Starting with Do While (current. Shape <= num. Shapes) first shape so Active. Document. Shapes(current. Shape). Fill. Fore. Color long as there's = vb. Red another shape current. Shape = current. Shape + 1 in document repeat loop Loop 'Goes through each shape in current doc End If 'Checks if any shapes in current doc Automatically save Active. Document. Close (wd. Save. Changes) and close document, current. Document. Name = Dir move onto next Loop 'Goes through each Word document End If 'Checks if any Word docs in folder End Sub VBA tutorial notes by James Tam

Student Exercise 4 • The program will prompt the user for a path. • Displays an error message if the path is empty and the program ends. • If the path is not empty then it will successively open each Word document at that location and process it in the following fashion: – Font type will be changed to “Garamond” – The word count will be written to the top of the document using bolded text. – If there’s any tables in the document then they will be sorted (tables have headers). • Name of the document containing the solution: exercise 4 VBA tutorial notes by James Tam

Option Explicit Used • Including ‘Option Explicit’ requires that variables must be created via ‘Dim’ variable declaration – E. g. Dim tam. Money As Long • After creating/declaring the variable the memory location can be used by assigning a value into that location. – E. g. tam. Money = 1 • Advantage: helps catch bugs – If you type in the wrong variable name if you use Option Explicit then VBA may tell you exactly where the error lies. VBA tutorial notes by James Tam tam. Money 1

Example: Option Explicit Used • Example: 6 A_option. Explicit. Used. docm VBA tutorial notes by James Tam VBA will automatically catch the error and point out the location

Example: Option Explicit Not Used • Example: 6 B_option. Explicit. Not. Used. docm The program erroneously Sub lottery. Program 2() set the wrong variable! Dim tam. Money As Long Dim die. Roll As Long tam. Money = 1 die. Roll = CInt((6 * Rnd()) + 1)) If (die. Roll >= 1) And (die. Roll <= 4) Then tam. Mooney = 1000000 End If Msg. Box ("Tam's income $" & tam. Money) End Sub Tam didn’t get the “big bucks” Errors like this can be hard to catch/fix in all but smallest programs VBA tutorial notes by James Tam

The VBA Debugger • Debuggers can be used to help find errors in your program • Setting up breakpoints – Points in the program that will ‘pause’ until you proceed to the next step – Useful in different situations • The program ‘crashes’ but you don’t know where it is occurring – Pause before the crash • An incorrect result is produced but where is the calculation wrong • Set up breakpoints – Click in the left margin VBA tutorial notes by James Tam

The VBA Debugger (2) • Multiple breakpoints • Program pauses when breakpoints are reached – The contents of variables can be displayed at that point in the program VBA tutorial notes by James Tam

The VBA Debugger (3) • Combining breakpoints and viewing variables. VBA tutorial notes by James Tam

An Example To Run With The Debugger • Example: 7 debugger. Example. docm – Sub: Debugging. Example 1 VBA tutorial notes by James Tam Set up a breakpoint and trace through the program step-bystep while viewing the contents of the loop control during each iteration of the loop

An Example To Run With The Debugger (2) • Example (cont’): 7 debugger. Example. docm – Sub: Debugging. Example 2 VBA tutorial notes by James Tam Program randomly assigns value into x, y, z (1 – 100) Set up multiple breakpoints and mouse over variables at the breakpoints to view their contents. • This time x = 71, y = 54, z =1 • Which branches execute • What values will be assigned to the string ‘s’