Find and Replace

I am sure that as an Excel user you must have had many occasions when you had to check whether some text, number or format is available in a sheet or the workbook. Sometimes you might have to replace all instances of existing text with new text. Excel’s Find and Replace feature is available to specifically facilitate this. In this post, we will see some details about this feature and few cool tricks .

What is?

This is a feature in Excel to facilitate finding of cells with specific text, formula, format or comment etc. It is also possible to select any, few or all of such cells. Additionally, you can replace any text in the cell with any other text you want.

How to access?

Excel 2007 or later: Home > Editing Group > Find & Select > Find

Excel 2003 or earlier: Edit > Find

In all the versions, key board shortcut Ctrl+F shows up the same dialogue box.

Let’s see in details various options available in this feature.

Find Tab

This tab can be used when you only need to find text or numbers.

Find what box – You can enter text or numbers that you want to search for (this box is also a dropdown list that contains all the recently searched items, so you can even select from this list)

Options >> - You can further refine / expand your search by clicking on this button which on clicking shows few more options to work with. You will notice that Find what box size is reduced and few more options are available.

               

Clicking Format… shows up another dialogue box (similar to Format Cells box but here it is named as Find Format. You can define the criteria based on number style, alignment, font, border, cell colour or protection etc. Upon clicking on OK in this box, Formats you selected will be shown in Find and Replace box to the left of Format button.

Format… button also has a dropdown button at the end which has few more options.

Format… in the dropdown menu will show the Find Format dialogue box

Choose Format From Cell… will let you select a cell so that it’s format will be considered for search

Clear Find Format lets you clear off any format you already selected

Within box: To search for data in a worksheet select Sheet or select  Workbook to search in entire workbook

Search box: To search for data in rows or columns, click By Rows or By Columns.

Look in box: Select appropriate option to search for data in Formulas, Values, or Comments.

Match case check box: To search for case-sensitive data, select it

Match entire cell contents check box: To search for cells that contain just the characters that you typed in the Find what box, select this.

Find All: Lists down all the occurrences of text / format entered in Find what box.

Find Next: Find and selects the cell that contains the next occurrence of text / format entered in Find what box.

Close: Closes the Find and Replace box without any further action.

Replace Tab

This tab can be used when you need to replace text or numbers. All features discussed above will stay with following additional boxes.

Replace with: This box is similar to Find what, and you need to define what you want to replace with.

Format button: See above.

Replace All: Replaces all the occurrence of text / format in Find what with text / format in Replace with.

Replace: Replaces the first occurrence of text / format in Find what with text / format in Replace with. This selects next available occurrence.

That’s the theory. Now let’s get into the cool part to see few practical uses of this.

Tip 1: Find in Only Particular Region

Select the region you want before accessing Find and Replace feature. If more than one cell is selected, Find and Replace checks only in the selected part. Select any one cell if you want Excel to find in entire sheet.

Tip 2: Select All Occurrences

Find and Replace feature can list down all the occurrences of text, number or format you are looking for. However, sometimes you might want to select all the cells that contain a specific format so that you can change that format for all the cells in one shot. You can use Find and Replace to do this for you. Once you selected the format you are looking for, click Find All and Excel will list all the cells in the dialogue box. Press Ctrl+A to select all of referenced cells in the list. Click Close. Now, your cells are selected.

You can also keep pressing Ctrl and click individual entries with mouse to select only few cells of your choice.

Tip 3: Use Wildcards

Find and Replace supports entry of wildcards. You can use ? (question mark) to search for a single character and * (asterisk) for any number of characters. For example: if you type b?ll in Find what then Excel will search for all words that has b in the start and ending with ll like bill, ball, bell etc. This feature is of particular use if you know the number of characters & any character of what you are looking for.

Using asterisk will give you more flexibility. Example: if you search for b*t, Excel will look for all the words that has b in the start and t in the end like bat, best, bolt, bright etc.

Tip 4: Search for Asterisk or Question Mark

In our earlier tip, we saw that you can use ? and * as wildcards. But what if you want to search for them specifically? Every time you use these characters in Find what box, Excel will think you are using them as wildcards. In this case, you should place a ~ (tilde) before ? or *. This will tell Excel that any character that follows tilde will have to be looked for as it is.

For example, if you are searching for a question mark, you will enter ~? in Find what box.

Happy Learning!!

VBA - Select All Unlocked Cells

We saw how to use Excel protection features in one of our earlier post. We could unlock certain cells and lock other cells so that users can only have option to modify unlocked cells. We also noted that Locking and Unlocking cells will have no effect until the sheet is protected. All good till there, however there is no direct way in Excel to quickly see which cells are locked or unlocked.

When the sheet is protected, you can use Tab key to move through all the unlocked cells. But imagine a situation where you are preparing a sheet to distribute to your colleagues with only few unlocked cells spreading across sheet. To make sure that you have locked / unlocked required cells, you will have to go through Format Cells option and check if the cell you selected is locked or not.

Now imagine yourself as a user of such sheet that someone else designed. You cannot know which cell is unlocked and ready to be modified, unless such cells are highlighted by different colour or any such other markings.

In this post, we are going to develop a code to quickly select all the cells that are in unlocked status. Let’s go ahead and do it. Before going to the coding, let’s discuss a bit on the logic we should follow.

1. First, we should run the macro in the sheet from where the code is run. We know that ActiveSheet object refers just that.
2. In the sheet, the code should run in only those cells which are used i.e. modified, locked or unlocked etc. Why to run the code in all cells in entire sheet there by wasting precious processing time, isn’t it? Alright, with bit of research in Excel Help or going through the Properties of ActiveSheet, you will know that there is a property of ActiveSheet object which is UsedRange that is a collection of all cells that are used.
3. Now we have a collection of all used cells, we need to loop through each of these cells and see if that particular cell is locked or not. We know that For Each Loop can do this work for us.
4. How to know if cell is locked? Let’s see what Excel records when we lock / unlock a cell by recording a macro with this action. By default all the cells are locked, I am going to record a macro while unlocking cell A1. Below is the code Excel gave me.

• Sub Macro1()
  '
  ' Macro1 Macro
  '
   '
      Selection.Locked = False
      Selection.FormulaHidden = False
  End Sub
5. Selection.Locked = False is the line we need. Cell A1 which I selected is called through Selection object and its property Locked is set to False, that means cell is now not locked. Excel Help is your friend again; each Cell has a property Locked. If this property is set to True that means that cell is locked otherwise it is unlocked. (Additional info: Notice the line Selection.FormulaHidden = False, this line is not needed for us now, this code is used if you want to hide the formulas from displaying in Formula Bar)
6. Alright, now we know how to see if a cell is locked or not. One last thing that is left out is what to do with this information. This one depends on your need, you may want to select all such cells or get the addresses of all those cells in a sheet etc. For this exercise, I am going to select all such cells.
• I recorded another macro while selecting cell A2. This is the code.
• Sub Macro2()
  '
  ' Macro2 Macro
  '
  
  
  '
      Range("A2").Select
  End Sub
7. Good, now we know that Range object has a method Select which selects the cell address given in Range object.
8. Now the tricky part, in Excel you can only select a cell or a combination of cells in one operation. If you try to select cell A1 and after that try to select A2, A1 selection will be gone (unless you keep pressing Ctrl button while selecting A2). Since our macro will loop through to identify an unlocked cell and select it, in the next looping, already selected cell selection will be gone. Let’s observe how Excel captures selecting various cells when you press down Ctrl. Below is the code recorded by Excel when I select so many random cells.
9. Sub Macro3()
   '
   ' Macro3 Macro
   '
   
   
   '
       Range("E9,C5,C9,B13,A8,A5,B3,D3,F2,H2,H5,J5,J8").Select
       Range("J8").Activate
   End Sub
10. See, Excel just combines all cells addresses with a comma and embed it in one Range object and use Select method to select them. We will also use the same thing, I am going to use a String to capture unlocked cell addresses and select all of them at the end of the code using above logic.

Let’s put all the above information we noted in code format. Here is my code.

Sub SelectUnlockedCells()
Dim Cl As Range
Dim MyStr As String
 For Each Cl In ActiveSheet.UsedRange 'Start looping, refer to point 1, 2 & 3
    If Cl.Locked = False Then MyStr = MyStr & Cl.Address & "," 'Refer point 4 & 5
Next Cl ‘End looping
If MyStr <> "" Then Range(Left(MyStr, Len(MyStr) - 1)).Select 'Point 6, 7 & 8
End Sub

I used MyStr to capture all the cells that are having Locked property False. I also used a comma (,) to combine different cells addresses. In the last line of code, I am checking if MyStr is empty (means no unlocked cells are found), if it is not empty then used Range object Select method to select all those cells.

One thing I have to explain is what Left(MyStr, Len(MyStr) - 1) does in the code. If you notice the MyStr just before executing this line of code, there will be a comma at the end of the string. This would result in failure of the code as Range object needs exact cell address with comma in-between but not at the ends. So I am removing the extra comma at the end of MyStr by using Left function of VBA. (This function is similar to Excel’s built-in Left function).

Just run the code now and all the unlocked cells will be selected. Now colour them, enter values in them or apply borders, it is up to you what do you want to do.

I hope the above post explains clearly on how you proceed to code your requirement in the start. Once you know VBA Code Structure, you don’t even need to record macros to see what Object to use. So remember the mantra, the more you experiment the more you learn. Happy Learning!!

VBA - Work With Gridlines

Do you notice the faint lines around cells in any Excel sheet? These are called gridlines. They are used to distinguish cells on the worksheet i.e. each row and each column is separated by a gridline.

Gridlines are visible by default in Excel and you can hide them if you don’t want at any point of time. However, gridlines are sheet specific. That means you will have to hide them for each sheet or select multiple sheets and hide the gridlines for those sheets.

Also, if you are using Excel 2003 or earlier versions, finding the gridlines hide/unhide option is bit long. You will have to follow Tools > Options > View > Gridlines (Check/uncheck the box). It is relatively easy in Excel 2007 or later as this option is available directly under the View tab.

Today, we will see how to hide/unhide gridlines for all the sheets in a workbook through a macro. We will follow the same logic we followed in Unhide all hidden sheets post.

Record Hiding a Sheet’s gridline

Create a new workbook and save it as macro enabled workbook.

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

• Enter "Macro Name" as HideGrid
• Click OK.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier. Now your macro started recording.

Excel 2003 or earlier versions click on Tools > Options > View > Gridlines (uncheck the box to hide gridlines). Excel 2007 or later, click View tab > Show group > Gridlines (uncheck the box to hide gridlines). Your macro would have recorded your action, let’s go and check it out. Before that stop macro recording by clicking on that blue button.

Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here). In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window. My recorder shows the below code.

Sub HideGrid()
'
' HideGrid Macro
'
 '
    ActiveWindow.DisplayGridlines = False
End Sub

Notice the line ActiveWindow.DisplayGridlines = False. This is what we need to hide the gridlines for the sheets; on the contrary, setting the property to True will show the gridlines. Now all we need to do is apply this setting to each sheet in the workbook. At this point, you might want to take a look at Sheets collection and how we use a For Each statements to loop through all sheets in our earlier post.

Also in the above code, note that ActiveWindow is the sheet that is active at the point of running the macro. So instead of changing the sheet property, macro is actually accessing ActiveWindow property to change the visibility of gridline. That gives an important point to note for this post, you should have a sheet active (selected) to change its gridlines property.

So, logic for our code would be, for every sheet in this workbook, activate (select) the sheet and change the gridlines property to false. Let’s go ahead and do that in code format.

Sub HideAllGrids()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets ‘For all sheets in workbook
    Sht.Select ‘Select sheet
    ActiveWindow.DisplayGridlines = False ‘change gridlines property to false
Next Sht ‘go to next sheet
End Sub

The above code uses a variable Sht to represent the sheet. We are activating each sheet in the workbook and changing the gridline property to False. This process repeats until the entire sheet’s gridlines are changed.

Show All Gridlines

In the above code, change the line ActiveWindow.DisplayGridlines = False to ActiveWindow.DisplayGridlines = True if you want to show the gridlines in all sheets.

Toggle Gridlines

In the above code, change the line ActiveWindow.DisplayGridlines = False to ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines if you want to toggle the gridlines in all sheets.

Hide Gridlines – Take 2

Code above will loop through each sheet to change the property. You can get the same result from the below code without use of a variable.

Sub HideAllGrids()
Sheets.Select
ActiveWindow.DisplayGridlines = True
End Sub

Hide Gridlines – Take 3

Above set of codes work perfectly when all the sheets are ready to be selected. I mean to say, if there is a hidden sheet, that sheet cannot be selected, isn’t it? So, above code will fail if there is at least one sheet that is hidden. What to do in that case?

Use On Error Resume Next

If you just want to ignore the hidden sheet, include the above error trapping in your code.

Sub HideAllGrids()
Dim Sht As Worksheet
On Error Resume Next
For Each Sht In ActiveWorkbook.Sheets ‘For all sheets in workbook
    Sht.Select ‘Select sheet
    ActiveWindow.DisplayGridlines = False ‘change gridlines property to false
Next Sht ‘go to next sheet
End Sub

Check the Visibility of Sheet

You can check the visibility property of the sheet and decide about the next step. For example, you might want to unhide the sheet and apply the property. Let’s see how to do that.

Sub HideAllGrids()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    If Sht.Visible <> True Then Sht.Visible = True ‘Check if sheet is visible, if not make it visible
    Sht.Select
    ActiveWindow.DisplayGridlines = False
Next Sht
End Sub

That’s all. As we did above, you can modify your code to fit to any scenario. Just remember doing a little bit of research every time, doesn’t matter if you fail at the beginning, you will surely master it quickly. Happy Learning!!

VBA - Remove All Shapes In a Sheet

Hi. This post is a result of recent situation I faced. I copied contents of a webpage and pasted into my Excel workbook for further analysis. All was good until I noticed many tiny little checkboxes / shapes that were part of webpage also got pasted into the workbook. I could have used Paste Special command to paste only text but I also need the formats, however that’s not the point. We are trying to delete all shapes in a worksheet with single operation. There are few ways of deleting these shapes in Excel but all involve more than one click. Let’s see how to do this by way of a macro; it will also give us some more learning in to the coding world. Following our standard from earlier posts, we will start by recording a macro while deleting a shape and analyse the code.

Record the code

Open the workbook where the shape / picture (s) are inserted (if you don’t have, you can start by inserting a shape / picture of your choice in the workbook). For easy reference, I have a workbook in which I have various shapes ranging from a rectangle, a clip art, a star & a form button.

Now, let’s record a macro by deleting any one shape and see the code. (How to record a macro & how to access the recorded code)

Below is the code VBA shown me when I deleted the rectangle.

Sub Macro1()
'
' Macro1 Macro
'
 '
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    Selection.Delete
End Sub

By analysing the above code, we could see ActiveSheet (a sheet that is active at the time of macro being recorded) has a property called Shapes and one of the shapes named Rectangle 1 was selected and deleted.

Let’s use this information. If you are looking to delete all the shapes in active sheet you can straight away use ActiveSheet object. However, if you are looking to apply this macro to a different sheet, you already know how to reference different sheet(s). We saw that Shapes object contains the collection of all shapes in the sheet, since we need not worry about a particular shape in our sheet (we are trying to delete all, remember), we just need to delete whatever is there in the Shapes object.

A quick look in VBA help on Shapes object will reveal the members of Shapes object. One of such member is SelectAll method which as the name implies, will select collection of the shapes that are part of Shapes object. Last line of above code will then be used to delete all the selected shapes.

So our first cut code looks something like below.

Sub Test()
ActiveSheet.Shapes.SelectAll ‘Selects all the shapes in active sheet
Selection.Delete ‘Delete the selected shapes
End Sub

Execute this macro and check. All the shapes in your active sheet must be gone. Clear?

You can apply the above code for all sheets in entire workbook as below.

Sub Test2()Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Sht.Shapes.SelectAll
    Selection.Delete
Next Sht
End Sub

There is another way you can delete all shapes in a sheet by using For Each loop. Check out the below code.

Sub Test3()
Dim Shp as Shape
For Each Shp In ActiveSheet.Shapes
    Shp.Delete
Next Shp
End Sub

I am assigning each shape to a variable and looping through all the shapes and deleting each one of them. However, in some versions of Excel, above code was said to remove the comments and AutoFilter as well. If you face such an issue, you can use the below code.

Sub Test4()
On Error Resume Next
ActiveSheet.DrawingObjects.Delete
End Sub

If you are deleting all the shapes, our first code is efficient however the second code will come to your rescue when you have to delete only certain types of shapes or only particular shapes.

Delete Specific Shapes

You have many types of shapes in your sheet and want to delete only certain type(s). For example: You want to delete all of the text boxes in the sheet.

Prerequisite to code above requirement is to know that each Shape in Excel contains a Type property which determines if a particular shape is text box or Image or a button etc.

By looking at the Type property values, you can notice text box type under Shapes is msoTextBox (also represented by value 17).  You can look at all the type object values in Microsoft Developer Network

Now, we just need to loop through the Shapes and see if any Shape Type is msoTextBox. If yes, then delete that Shape otherwise move to next shape. Simple? Let’s code it.

Sub test()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoTextBox Then Shp.Delete
Next Shp
End Sub

Delete All but One Shape

Assume a situation where you have to delete all shapes but leave only one shape named Check Box 1. You cannot obviously use SelectAll method hence the above code with IF Then Else statement would help you. See the below code.

Sub Test5()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Shp.Name <> "Check Box 1" Then Shp.Delete
Next Shp
End Sub

Look at the Shape object members. Read about the methods and properties supported by Shape object. You will master playing with all and all shapes in your workbook. Go on and do experiments with what all you can do using Shapes object. As I mentioned in one of previous post, learning VBA is all about how much you consult VBA help, everything you need to learn in VBA is out there. Happy Learning!!

PS: If you are trying to delete all the objects in an Excel sheet, you can do that without using VBA. Clicking on Go To Special > Objects will select all the objects and you can use Delete button to delete all of them in one go. You might also want to read our post on Working with Shapes.

Unhide All Hidden Sheets

Excel supports hiding multiple sheets in one go but you can unhide only one sheet at a time. As part of our VBA learning, today we are going to code a macro which actually does something useful, unhide all sheets with one action. We are going to use our macro recorder to see what code Excel use to hide / unhide a sheet and expand it to suit our requirement. I hope you remember how to record a macro.

Record Hiding a Sheet

Create a new workbook and save it as macro enabled workbook.

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

• Enter "Macro Name" as HideSheet (any name you want for that matter, just follow rules)
• Click OK.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier. Now your macro started recording.

Right click on a sheet tab and click on Hide. This will hide the tab. Now right click again and click on Unhide > click on sheet name you have hidden in previous step and click OK. Your macro would have recorded both actions, let’s go and check it out. Before that, stop macro recording by clicking on that blue button.

Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here). In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window. My recorder shows the below code in Code Window.

Sub HideSheet()
'
' HideSheet Macro
'
 '
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Sheet2").Select
    Sheets("Sheet1").Visible = True
End Sub

We know that the VBA recorder code is not perfect. Let’s first analyse the code to see what it does.

• Sheets("Sheet1").Select selects the sheet.
• ActiveWindow.SelectedSheets.Visible = False changes the visibility of the selected sheets to false i.e. this is the code to hide the sheet.
• Sheets("Sheet2").Select selects the sheet.
• Sheets("Sheet1").Visible = True changes the visibility of the sheet to true i.e. this is the code to unhide the sheet.

Remember, there is no need to select an object in order to work with it. But Excel does while recording which we can totally avoid. So effectively, point 1 and 2 can be combined into one. Point 3 can be totally removed. Let’s see our modified code.

Sub HideSheet()
    Sheets("Sheet1").Visible = False 'hide the sheet
    Sheets("Sheet1").Visible = True 'unhide the sheet
End Sub

Now that we know which object’s what property we need to alter to hide / unhide a sheet, let’s proceed to find out how to do the same on multiple sheets.

Before we continue, you have to learn a bit about Sheets object. Sheets object is a collection of all sheets in the workbook. You can refer any particular sheet in the workbook by accessing it from the Sheets collection. There are multiple ways to refer a sheet from the Sheets object. One way is to call out using the sheet name as in above code snippet.

Sheet(“Sheet1”)

This calls the sheet named Sheet1 no matter where this sheet is placed in set of sheets. Another method is to refer to number of the sheet.

Sheet(1)

This refers to first sheet in the order, no matter what its name is. You can follow both the ways based on your convenience. I use index number, because most of the times you may not know what will be the sheet name.

Now that we know how to refer sheets from Sheets object, let’s see how we can change visibility property for all sheets.

Method 1: Using a Counter

We will use a variable as counter & For Next statement to repeat the macro for all sheets.

Sub UnhideAllSheets()
Dim A As Integer
For A = 1 To Sheets.Count
    Sheets(A).Visible = True
Next A
End Sub

Copy the above code and place it in code window. Run this macro, you will be able to see all the sheets in your workbook visible.

I am using a variable A as an index number to refer the sheet. Also same variable is used to repeat the macro for those many times as the number of sheets in the workbook. Number of sheets in workbook is retrieved by Sheets.Count method. I used For Next statement to loop through.

Method 2: Using For Each Statement

Another way to get this done is to loop using For Each statement. For this we use a variable to contain each sheet.

Sub UnhideAllSheets2()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Sht.Visible = True
Next Sht
End Sub

In above code snippet, we are declaring a variable Sht to contain all the properties of a sheet. Then using For Each statement, we assigned first sheet to Sht. Changing Sht properties now will change first sheet properties. Then the Sht will be assigned with second sheet and its visible property will be changed. This process continues till all the sheets visibility property is changed.

Above utility will unhide all the hidden sheets (including very hidden). Of course, you can customize the macro to unhide only normal hidden sheets or only very hidden sheets or any other criteria you want. Just explore the possibilities. Happy Learning!!

You may also want to read about:

Very Hidden Sheets

VBA Looping - For Next (3 of 3)

Hi, we saw how to use For Next to loop through any number of times and perform some tasks you code. In the last post that we saw, we wrote a code to check if any cell in the selection has formula using For Each statement. There is a slight efficiency problem in that code. When a particular loop identifies a cell that contains formula, there is no more need to continue looping, right? Since all that you want is to check if any one cell contains formula or not. Why to continue looping after you find one such cell? To avoid looping after a particular situation is encountered, you can use Exit For statement that stops looping. Let’s see how, using the very same example from previous post. Check out the code below.

Sub CheckFormula()                                                                                             
Dim Cl As Range 'Declaring a variable for cell
 For Each Cl In Selection 'Declaring loop for each cell in selected range
    If Cl.HasFormula = True Then 'Check if that cell contains formula
        MsgBox ("There is atleast one cell with formula")
        Exit For
    End If
Next Cl
 End Sub

Compare this code with our code from the previous post. I am using only one variable compared to two earlier. When my Cl.HasFormula statement returns True (that means formula is there), a message box is displayed and Exit For statement is executed. Loop will end at this point of time. This saves you on the precious execution time.

You may also look at: Unhide All Hidden Sheets using For Next & For Each

VBA Looping - For Next (2 of 3)

Hiiii!

We have been discussing about looping in VBA and saw about For Nextlooping in our earlier post. Today, we will see about one more type of For Next loop.

For Each Loop

Our earlier method will work fine when we know exactly how many numbers of times the loop should work. But there might be some cases where your macro should run depending upon the situation. For example: what if your macro should run based on the number of sheets in the workbook? There might be 2/3/4….n sheets in that workbook where macro is being run by user.

In these cases, you can use For Each loop. Let’s see how to code this by an example. I need to write a macro to check that;

• any cell in a range that user selects contains a formula
• If at least one cell is found with formula, a message box should be displayed.

Since how many cells can be selected is in the hands of the user, we can’t hard code the looping number in the code. Let’s see how we can use For Each in this example.

Sub CheckFormula()
Dim Cl As Range 'Declaring a variable for cell
Dim HasF As Boolean 'Declaring a variable to flag if cell has formula
 For Each Cl In Selection 'Declaring loop for each cell in selected range
    If Cl.HasFormula = True Then 'Check if that cell contains formula
        HasF = True 'formula is there, so HasF should be true
    End If
Next Cl
 If HasF = True Then MsgBox ("There is atleast one cell with formula")
End Sub

I am using 2 variables here. Cl holds a range and HasF is a Boolean. Our code will loop through all the cells in a Selection (Selection is an object that is collection of all the cells selected). When you start executing the macro, each cell in the selection will be checked for Cl.HasFormula statement. This will return either True (if formula is there) or False (no formula). HasF will get its value from this result. At the end, we are checking the HasF final status; if it is true (that means formula is found somewhere) then a message box is displayed. Otherwise macro ends.

There is one more example of For Each available in the post: Unhide All Hidden Sheets using For Next & For Each. You may also continue reading about For Each Looping - For Next (3 of 3)

VBA Looping - For Next (1 of 3)

Hello. We have been reading about VBA useful features in series of posts. Understanding these will help you to a great extent when you start serious coding. Do you remember our post on why not record the macro all the time instead of coding? We saw that recording macros does not support looping. That means if you have to perform a task n number of times, you have to either record performing the task n number of times or run the macro n number of times. This is major drawback in recording macros and we saw that VBA coding would help in this aspect. In this post, we are going to see how to use looping in VBA.

For starters, we can use looping to perform a task n number of times where n can be defined by you or get from user by an InputBox or UserForm. n need not be defined as exact number, it can also be decided based on the situation. To make it clear, you can loop a procedure 10 times by hard coding the number 10 or decide it based on the situation. For example: you can loop a macro those many times as the number of cells selected before executing the macro or you can loop those many times as the number of sheets in that workbook etc.

There are two kinds of loops in VBA viz. For Next Loop, Do Loop. We will see about For Next Loop in this post and continue with other kind in a future post. Let’s rock.

For Next Loop

You can use this loop in two ways. First one is by using a variable as a counter to tell VBA how many times the procedure to run.

Syntax is like below:

For VariableName = Start To end
Statements…..
Next VariableName

Let’s see what the syntax means.

For: key word to call the loop.

VariableName: Is any name you give to your variable. It will be used as counter.

Start: Is the value you want to start your variable at. It can be any number.

End: Is the value where you want to end the loop. It can be any number more than Start. Loop will be executed those many number of times as the difference between end and start.

Next: key word to loop. VariableName is not mandatory to be placed next to Next but it looks neat and when you have chain of loops with in loops, it will be clear.

Example:

I want to show a message box 2 times, with a prompt showing how many times the loop is run already. I use a variable named MyCount in this statement. See the sentence construction.

Sub Test()
Dim MyCount As Integer 'Declaring my variable
For MyCount = 1 To 2 'Declaring how many times it should loop
    MsgBox MyCount 'Show message box
Next MyCount 'Looping
End Sub

Copy above code and paste it in your code module. Make Locals window visible on the screen and press F8 repeatedly and observe what happens to the variable value on each press. I will take you through step by step on what’s happening.

When the execution point (yellow highlight) reaches For statement, MyCount value is empty. On press of F8, focus will be moved to MsgBox line and MyCount Value is set at 1 as in the below screenshot.

This 1 is the value I set in For statement at the start. If you set 10 there, you will get 10 as the value in the Locals window. In that case, your code line will be For MyCount = 10 To …..

Pressing F8 again will display a message box with 1.

Once you click OK in the message box, execution point moves to Next statement. Press F8 again and notice the code and locals window. Focus will move to MsgBox line again (hey, that means we successfully looped) and Locals window now changes the value of MyCount to 2.

It means, when VBA executes Next statement, it increases the count of variable automatically by adding 1 to it. Go ahead, press F8 again and VBA will show the message box again.

Click on OK in the message box, execution point moves to Next statement again. So we wanted to loop 2 times and we got message 2 times. So, ideally looping should stop now. Let’s see what happens if you press F8 now.

Press F8 and you will notice that execution point moves to End Sub statement and MyCount now shows a value 3 in Locals window. This is because focus moved to Next statement 3 times so counter increased to 3. That’s normal and you need not worry about it. Pressing F8 again would end the procedure.

Step

We saw that counter will be increased by 1 every time you reach the Next statement. You can change this number to any other number.  VBA adds that number on each execution of Next. This is done by using Step word as show below.

For MyCount = 1 To 10 Step 2

Here, I am trying to loop starting 1 till 10 but at the end of every Next, VBA adds 2 to the variable value instead of 1. You can enter any number next to Step. If I run above line placed in our example code at the start, I will get message box 5 times.

Step In Reverse

If you want to loop in reverse order, you can use the code as below.

For MyCount = 10 To 1 Step -2

This will take MyCount value as 10 at the start and keeps on reducing by 2 when Next statement is executed.

We will use this looping method in various codes going forward. Just know the basics and when we use it in a code later, we will be clearer. Happy Learning!!

There is one more example of For Each available in the post: Unhide All Hidden Sheets using For Next & For Each. You may also continue reading about For Each Looping - For Next (2 of 3)

VBA - Entering Arguments

You use Objects when you are working with codes. Objects have Methods which you can use to perform some tasks for you in Excel. These methods require you to pass arguments to them to work with. We have been seeing many methods in our previous posts like Application.InputBox. Many built in VBA functions like MsgBox & InputBox also uses arguments to do some task.

In this post, let’s see few details about arguments and how to pass them to Methods or Functions

What is an Argument?

No, argument here is not used in the sense of quarrel. Arguments are values that VBA need to perform certain task for you. For example: if you want to show MsgBox, you need to supply a prompt that should be displayed to user, may be a title and what are the buttons you want the MsgBox contain. Here, the prompt, the title and the buttons will be arguments for MsgBox function.

When you enter a function or method that requires arguments and press space, tab or open a parenthesis, VBA will show you list of arguments required for that function / method as a tool tip. See the picture below.

Notes:

• Optional arguments will have square brackets surrounds them. You can ignore them if you don’t want.
• Mandatory arguments will not have any brackets, you must provide these in order to work with the function.
• Argument you are entering currently will be bolded in the tip.
• Optional arguments have default settings which will be used by VBA if you do not enter values for those arguments.

You can type argument values (by placing them in quotes if it is text) or pass on a variable as its value. For example: both the codes below show a message box with a message Hello World. Second code snippet uses a variable to show the message.

Sub Test()
MsgBox (“Hello World”)
End Sub

Sub Test()
MyMsg = “Hello World”
MsgBox (MyMsg)
End Sub

Skip an Argument

Method 1 – Using Comma

When there are multiple arguments to enter like in above picture for MsgBox, when you are done entering the first argument, you can move to the next argument by placing a comma. See the below picture, I entered first argument “Hello” and typed a comma, VBA moved the argument focus to second argument by bolding it.

You may skip any optional arguments if you wish to use VBA default settings for them. For example: for MsgBox, if you skip 2^nd argument, VBA displays OK button only. If you want to skip an argument, you can place a comma to represent the end of the argument without actually entering anything in it as shown below.

As soon as I entered a comma, VBA focus moved to next argument. Note that you should only skip optional arguments. VBA will complain if you try to omit mandatory arguments.

Entering commas are mandatory only if you want to skip one or more arguments in between and enter a later argument. In the above example: you need to enter a comma to skip Buttons argument only if I have to enter Title or HelpFile or Context. If you have no other arguments to provide after entering mandatory arguments, you can simply close the parenthesis.

For example: if you only have a prompt to display to user with OK button and have no other criteria, code as in below is sufficient.

Sub Test()
MsgBox ("Hello")
End Sub

If you have a prompt and a title to be displayed and nothing else, use below code.

Sub Test()
MyMsg = MsgBox("Hello", , "Welcome")
End Sub

Notice that I am assigning the MsgBox to a variable (because I am using more than one argument) and skipping second argument with a comma; third argument is provided and function is closed with a parenthesis. Though there are 2 more arguments for MsgBox, I haven’t skipped them with commas.

This commas method is basic and good enough to work with code. But if you want to keep your code clean without much of those skipping commas and to look clean, there is another method you can use to skip arguments.

Named Arguments

Methods / Functions support what is called as a named argument. Notice the MsgBox arguments below. First argument is shown as Prompt, next is Buttons, third is Title and so on.

You can use these names while you pass values to them. Named arguments can be called by using the name of that argument followed by a colon followed by an equal sign (:=). An example code using named arguments is given below.

Sub Test()
MyMsg = MsgBox(prompt:="Hello", Title:="Welcome")
End Sub

Note that there is no comma in-between to skip arguments. Above code works just like what we saw earlier using commas to skip an argument.

Sub Test()
MyMsg = MsgBox("Hello", , "Welcome")
End Sub

Though both ways of entering arguments work in similar fashion, using named arguments make your code more understandable to users.  Take your pick on which method you want to follow. Happy Learning!!