How to Read, Write, Delete and Move excel comments

In this post let’s look at how to automate Excel cell comments using VBA. If you want to manually enter a cell comment, then you can do it as follows. First right-click inside a cell. Then click on “Insert Comment”.

Then Excel will create a box like this.

Now you can enter your comment.

So if you have a spreadsheet with lots of data, how do you identify the cells which have comments? It is easy. Cells with comments will have red color triangle shape in top right hand corner of the cell. In this example there is a comment in D3 cell.

If you want to read the comment you can take your cursor on top of that cell. Then it will show the comment.

Now let’s look at how to read this comment using VBA. If the comment is in cell D3 then you can read it as follows.

Sub ReadComment() Dim StrComment As String StrComment = Range("D3").Comment.Text Debug.Print StrComment End Sub

If you run above macro, at the beginning comment will be assigned to string variable call “StrComment”. Then it will be printed in immediate window.

Next let’s learn how to write a comment to a specific cell. Below is the code to write a comment to a cell. This will add comment “This is sample comment” to cell G5.

Sub WriteComment() Range("G5").AddComment "This is sample comment" End Sub

Also note that you can replace “Range” keyword with “Cells”. So instead of Range(“D3”) you can write Cells(3,4) and instead of Range(“G5”) you can write Cells(5,7)

So if you want to move comment from B2 cell to C2 cell then you can do it as follows.

Sub MoveComment() Dim StrComment As String StrComment = Cells(2, 2).Comment.Text Cells(2, 3).AddComment StrComment End Sub

Also if you are developing a dynamic application, it is important to check whether there is a comment in the cell before try to read the comment. Because otherwise excel will produce runtime error if program try to read a comment from a cell where there is no comment. You can check whether there is a comment in cell A1 using following code.

Sub CheckForComment() If Cells(1, 1).Comment Is Nothing Then      MsgBox "No comment" Else      MsgBox Cells(1, 1).Comment.Text End If End Sub

If there is no comment it will give you “No comment” message. If there is a comment then macro will show the comment in a message box.

Create new workbooks from each and every worksheet in your workbook | Excel VBA

In an earlier post I explained how to create a workbook from a particular sheet in Excel VBA. Today I will teach you how to create new workbooks from each and every worksheet of your workbook and how to save them to desired location. So here is the workbook I have.

Name of this workbook is “Original file.xlsm”. As you can see this workbook has three worksheets. So I’m going to create 3 separate workbooks from them and save them in folder where this “Original file.xlsm” is in. This is how the folder look like before run the macro. It has only one file.

So below is the full code to separate all sheet to different workbooks and save them.

Sub CreateWorkbooks() Dim WB As Workbook Dim WS As Worksheet For Each WS In Worksheets    WS.Copy    Set WB = ActiveWorkbook    WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52    WB.Close Next WS End Sub

Here is the result after running above macro. As you can see three files have created with original sheet names.

Next I will explain about this code in detail. First we define our variables

Dim WB As Workbook Dim WS As Worksheet

Next we need to loop through all the sheets of our workbook.

For Each WS In Worksheets Next WS

As you can see there are few other lines between above two lines. So those commands will be executed for each and every worksheet. Following commands will create a new workbook from each sheet.

WS.Copy Set WB = ActiveWorkbook

Now let's take a closer look at the following line.

WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52

What this line does is, it save each workbook in .xlsx format in the folder where we have our original file. ThisWorkbook.Path gives the location of the folder. And WS.Name set the name of the workbook. So here each file is saved with it’s original sheet name. And if you want to save the files in a different location you can replace the ThisWorkbook.Path with desired folder path. Here is an example.

WB.SaveAs "C:\Users\EVS\Documents\" & WS.Name, FileFormat:=52

Also FileFormat number specifies the format when saving the file. Here is the list of excel file formats.

XlFileFormat Enumeration (Excel)

And following line is used to close each newly created workbook.

WB.Close

How to Create a New Workbook from Existing Excel Sheet in Excel VBA

In this post I will share a quick tip with you. Sometimes we need to create a new workbook from existing worksheet in Excel VBA. This existing worksheet can be active worksheet or any other worksheet. Assume we have a workbook like this.

This workbook contains 3 sheets. Think we need to create a new workbook from “Sheet3”. We can do that using following code.

Sub CreateNewWorkbook() Dim WB As Workbook Dim WS As Worksheet Set WS = Worksheets("Sheet3") WS.Copy Set WB = ActiveWorkbook End Sub

If you run above macro you will notice a new workbook created from Sheet3 like this.

Now we can see two workbooks in taskbar. Our original workbook and the newly created one.

And if you want to create a new workbook from active sheet you can use below code.

Sub CreateNewWorkbookFromActiveSheet() Dim WB As Workbook Dim WS As Worksheet Set WS = ActiveSheet WS.Copy Set WB = ActiveWorkbook End Sub

If you want to learn how to create workbooks from each and every sheet of the workbook and save them to a desired folder, then check this post.

Create new workbooks from each and every worksheet in your workbook

How to Sum Values in Excel

There are many ways to sum values in Excel. If you want you can directly enter the values you want to sum like this.

Also you can select a cell and then type in the formula bar too. Because if the cell is small then you can’t see all the values you entered to the cell. But formula bar has lot more space. So you don’t need to resize cells when enter long formulas.

And if you want you can enter the cell addresses of the values you want to sum instead of directly entering the values. Using this method you can change the values in the cells and get the sum instantly without doing any change to the formula.

Actually you don’t need to type the cell addresses manually. You can select the cells using mouse while pressing + key from the keyboard.

Also you can use inbuilt function in Excel to sum values in ranges. If this is a continuous range you can use something like this.

Here again, you don’t need to type the ranges manually. You can first type =Sum(
Then you can select the range using mouse. So the range will be inserted automatically to the formula. Finally you can close the bracket and press enter.

And if it is not a continuous range you can use a formula like this.

Paste Clipboard Content to Excel Sheet Using VBA

Have you ever needed to paste clipboard content to an excel sheet. Content in the clipboard may have copied from a web page, software, word file or text file etc. And content may be in any form.  It may be a text, table, image and so forth. So in this post I will teach you how to do this using VBA.

Method 1

In this method, first you need to add reference to Microsoft Forms 2.0 Object Library. To do that go to VBA editor and click Tools menu and then click on References.

Then put a tick to Microsoft Forms 2.0 Object Library. However you will notice that Excel application automatically add reference to that library if you add form to your project.

Then add this code to a module. In this code, clipboard content is assigned to the SText variable. So at the end you can paste it to the place where you need. In this example content is pasted to the B2 cell of the activesheet.

Sub PasteToExcelFromClipboard() Dim DataObj As MSForms.DataObject Set DataObj = New MSForms.DataObject DataObj.GetFromClipboard SText = DataObj.GetText(1) ActiveSheet.Range("B2").Value = SText End Sub

However this method will not work if you have an image in the clipboard. So if you want to deal with images then you can use this second method.

Method 2

Actually in this method we are using a very simple theory. We know that Ctrl + v is the shortcut keys to paste anything to the Excel sheet. So instead of doing this manually we can give that command through VBA like this.

Sub PasteToExcelFromClipboard_SendKeyMethod() activesheet. Range("B2").Select SendKeys "^v" End Sub

First we select the B2 cell of the active sheet. Then we give the paste command using SendKeys method.  Using this method you can even paste images, shapes and tables from the clipboard too.

How to Set the Location, Width and Height of an Inserted Image in Excel VBA

In this post I will explain how to set location, height and width of an inserted image. So this is the image I’m going to insert to the Excel sheet.

And this is the “Details” tab of the “Properties” window of that image. As you can see height and width of the image are 1000 and 1500 pixels respectively.

So if we need we can easily insert the image using following code.

Sub InsertPicture_Example1() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath) End Sub

Then picture will be inserted at active cell like this.

But sometimes we need to insert the image to a specific location of the sheet. And we may need to change the width and height to suit with the available space in the Excel sheet. So we can use following properties to change the location and size of the image to suit with our requirements.

LockAspectRatio
Height
Width
Top
Left

LockAspectRatio Controls the width: height ratio of the inserted image.  So if it is true, width: height ratio of the inserted image will be equals to the width: height ratio of the original image. If it is false, ratio of the inserted image will be different.  Height and Width defines the height and width of the inserted image respectively.  Top define vertical location of the top left corner of the image. And Left defines horizontal location of the top left corner of the image. We can give the location using row numbers and column numbers.

So following code will insert the image to D2 cell.  And height will be 200 pixels.  As LockAspectRatio set to true, excel will automatically calculate the width to comply with original image.

Sub InsertPicture_Example2() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 200     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

Image will be inserted like this

And here is our next example.  In this example, image will be inserted to D2 cell of the active sheet.  But width: height ratio will not equal to the ratio of the original image because we have set LockAspectRatio to false. And we have given specific height and width.

Sub InsertPicture_Example3() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = 200     .Width = 450     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So the image will be inserted like this

Here is another example.  Actually there's a mistake in this code. But I’m putting it here to show you how Excel application works if we use properties incorrectly. In this code we have set LockAspectRatio to true but after that we have given both height and width values.

Sub InsertPicture_Example4() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 200     .Width = 450     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So when the code is executed image size will be altered keeping the original width: height ratio. You will see how it works if you use debug -> step into method.

At the end image will be inserted with width of 450 pixels. And height will be altered to comply with original ratio. So the image will inserted like this.

In above examples we gave height and width in pixels.  But sometimes we need to assign the width and height in centimeters. We can use Application.CentimetersToPoints to do that. So the following code will insert the image to D2 cell of the active sheet. Height of the image will be 5cm. And width of the image will be 7.1cm

Sub InsertPicture_Example5() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = Application.CentimetersToPoints(5)     .Width = Application.CentimetersToPoints(7.1)     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So if you print the Excel sheet you will notice that image will printed in given size.

How to Pause for Specific Amount of Time in Excel VBA

In this post I will teach you how to pause or delay macro for specific amount of time.  This will be very useful specially when you developing web scraping applications. So I will explain you how to use this technique using a sample web scraping application.

So when you click the button in above application, program will update the share price of each and every company and then it will wait 10 minutes before refresh the share prices again. So the process will continue until the user save the file and close it. However if you need you can put a stop button to stop the process as well. Now let’s see how to do this pausing/waiting part.  So this code should be added just before you go to the next loop. So the structure of the whole code should look like this

Loop start    Code to extract data from web    Code to pause the program  Loop end

So now I will explain you how to do this pausing part step by step. In this example I’m going to pause the program for 10 minutes.

First we need two variables to Store time.

Dim CurrentTime As Date Dim EndingTime As Date

Then assign current time to the first variable.

CurrentTime = Now

Then we assign time we want to resume the program to the second variable.

EndingTime = Now + TimeValue("00:10:00")

Here you can change the time to control the gap between two loops. Format is hh:mm:ss
in this example I have set 10 minutes delay between two loops. Next you can use do until loop to pause the program until the time we need to resume it.

Do Until CurrentTime >= EndingTime     Loop

Inside that Do Until Loop you need to use following line to give the control to the user and for other programs.

DoEvents

And also within the loop you need to update first variable to current time. Then only program can track whether current time is similar or higher than resume time.

CurrentTime = Now()

So here is the full code to pause the program for 10 minutes

'------------------------------- 'Wait for 10 minutes '------------------------------- Dim CurrentTime As Date Dim EndingTime As Date CurrentTime = Now EndingTime = Now + TimeValue("00:10:00") Do Until CurrentTime >= EndingTime     DoEvents     CurrentTime = Now() Loop

There are various methods to pause VBA programs. But there is an advantage of using this method.  Because in this method during the delay time user gets the control to do any changes to the excel file. Because in some other methods user can't do any work in excel while it is waiting.  This method specially useful for web scraping applications. Because user can analyse the results during delay time.  But if you don't want to give control to the user while program is paused then you can use techniques described in this post. They are quite straightforward.

Sleep and Wait Functions

Want to know how to extract data from websites automatically? Read this.

Web Scraping