VBA - Application.SendKeys

Application.SendKeys is a way to simulate your keyboard strokes through VBA. For example: you know that we can press Ctrl+1 to show the Format Cells dialogue box in Excel. Without manually pressing these keys, you can let the macro press them.

Syntax

Application.SendKeys (Keys, Wait)

Keys: Key or key combination you want to simulate. You have to always enclose the keys in double quotes (to represent as text).

Wait: Optional. True to have Microsoft Excel wait for the keys to be processed before returning control to the macro. False (or omitted) to continue running the macro without waiting for the keys to be processed.

Each key on the keyboard is represented by one or more characters. For detailed listing of the keys, see below picture. All other keys are represented by respective alphabets.

SendKeys always works on the active window hence you should absolutely make sure that the application and window you are targeting is active at the time of running the macro.

Example of Application.SendKeys

We will use this method to run a spell check on the active sheet. The key board sequence to perform a spell check Excel 2010 is Alt+r+s. Let’s simulate the same using SendKeys method. Refer to above table for the keys to be used.

Alt is represented by percent sign, and other letters by same letters. Below is the code for our macro.

Sub SpellCheck()
Application.SendKeys ("%rs")
End Sub

There are alternates to use Sendkeys which are better. For example in the above illustrative case, you can use VBA to call up the Spell Check by using a direct command, that is straight forward and better to use. So use the Application.SendKeys sparingly and only when you have no other option. 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!!

VBA - Application.InputBox

We saw about InputBox function in our previous post. We learnt that you cannot restrict user to enter a particular type of values in InputBox. Of course, you can write couple of lines of code to validate user entry, but wouldn’t it be nice if your InputBox itself does that validation for you?

Application.InputBox method is at your rescue. Let’s see what it is and how to use it.

Application.InputBox calls a method of the Application object. A plain entry of InputBox in the code calls a function of InputBox that we discussed in our previous post.

Application.InputBox is a powerful tool to restrict the users to enter particular type of values. It also gives more flexibility to choose from various types of values that normal InputBox cannot. For example: you can let user select a range using Application.Input which is simply not possible with standard InputBox.

Also, returned value form the Application.InputBox method is a variant as opposed to a text string that is returned by plain InputBox. A variant can represent any kind of variable data type we discussed in post on varaibles. For example: it can hold a text string, a formula, a Boolean value, a complete Range reference and an array etc.

Syntax

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

Application.InputBox: This is the key word to call this method.

Prompt: Mandatory. Whatever you enter in this argument will be displayed to the user. If you are entering a string in prompt, you will have to enclose that string with double quotes. You can also pass one of the variable values as an argument for this.

Title: Optional. This is the text shown on top of the message box. If omitted, “Input” will be shown. Notice the difference. In InputBox, if this argument is omitted “Microsoft Excel” is shown.

Default: Optional. This is shown as default value in the InputBox.

Left: Optional. Specifies an x position for the dialog box in relation to the upper-left corner of the screen

Tops: Optional. Specifies a y position for the dialog box in relation to the upper-left corner of the screen

Helpfile: Optional. If you notice the alert produced by Excel will have either a help button or a question mark button on the alert. Those are to display a help file that is saved with the program. If you have a help file created for your message box, you can provide that here. But creating a help file deserves multiple separate posts but that’s not our concerns at this point of time. You can conveniently ignore this.

HelpContextID: Optional. It is the number of the topic to be displayed from helpfile. If this is provided, helpfile is mandatory (quite obvious)

Type: This is where it is way more powerful than plain InputBox function. Type specifies the return data type. If this argument is omitted or number 2 is entered, the dialog box returns text (which replicates plain InputBox function). You have below options to pass on for this argument.

Notes:

• It is mandatory to assign the Application.InputBox function to a variable if you are using more than one argument
• If you want to omit any argument and move to next one, you should include a comma
• You can allow different types of data input by combining the values with plus sign. For example: if you want to allow numbers and text too in a box, your Type argument would be 1+2.

Let’s see this in action by few examples.

Example 1: Display a message to the user asking to enter a number between 1 and 10.

Sub Test()
Application.InputBox("Enter a number between 1 and 10")
End Sub

Running the code displays below box. This is just like InputBox in operation. You are not restricting any user entry.

Notice the prompt “Input” and placement of buttons at the bottom. This is different if you compare with InputBox dialogue (below picture is copied from InputBox post)

Example 2: Display a message to the user asking to enter a number between 1 and 10. With number 10 entered already in the box that will be displayed.

Sub Test()
MyInput = Application.InputBox("Enter a number between 1 and 10", , 10)
End Sub

Notice the code: Since I am using more than one argument, I assigned Application.InputBox to MyInput variable. As third argument, I entered number 10 which is what I wanted in the box.

Executing code above will display below box.

Number 10 pre-entered in the box will help user if that is what commonly used. It reduces few key presses from the user. If user wants to enter a different number, he can delete existing and enter whatever he wants.

Example 3: Let user select a range of cells.

Sub Test()
MyInput = Application.InputBox("Select a range", , , , , , 8)
End Sub

Running the above code would show up a box and you can click on any cell or cells in Excel, address of that cells is reflected in dialogue box like a formula as shown below.

Example 4: With the above settings, restrict user to enter only numbers.

Sub Test()
MyInput = Application.InputBox("Enter a number between 1 and 10", , 10, , , , , 1)
End Sub

Notice the commas; I am skipping the arguments by placing a comma for each argument. For last argument, I entered number 1 which represents the number Type. After executing this code, a dialogue box just like above will be displayed. However, if the user enters anything other than a number, it will show an error. This process continues until user clicks Cancel or enters a number.

Below is the error displayed if I enter some text in this box and click OK.

Values returned by Application.InputBox can be used just like how we used InputBox returned values. For example, you can let user select few cells and apply formatting to those cells only or ask user to enter a number (let’s call it n) and highlight every n^th row in the sheet etc.

We can see Application.InputBox in action while we move ahead in our learning and start writing complex codes to achieve big tasks. Till then, happy learning!!