Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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

An Excel VBA Macro to Save a Workbook Automatically When Closing

Whenever you try to close an Excel file, Excel application ask you whether you want to save the changes or not.

But for some applications we need to automatically save the changes when we close the file without showing above message. So in this post I will show you how to do that using VBA. First open an Excel file. If it is a .xlsx file then save it as .xlsm file. If this is a new excel workbook, first you need to save it manually as .xlsm file. Then go to the developer tab and click on the Visual Basic icon.

If you don't have developer tab this post will explain you how to show it.

How to show the Developer tab

Also you can use shortcut keys to open the VBA editor. Press Alt+F11

Then double click on ThisWorkbook module.

You will see two drop downs above the coding area. They have default values as (General) and (Declarations).

From the first drop down select “Workbook”. When you select that value second dropdown will be populated with new list. Then select “BeforeClose” from that second dropdown. New subroutine will be created like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Add this code between private sub and end sub

ActiveWorkbook.Save

so the final code should look like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.Save

End Sub

Now do some changes to the excel file and close it. Excel application will silently close the file without showing any message. Next time when you open the file you will notice that the changes are saved.

Create and Format a Table

Advance VBA applications consist of various functions. Report generating is one of them.  Because lots of applications need to generate various kinds of reports for the user.  These reports contains data in various formats such as tables, charts etc. So when we create reports we often need to create tables dynamically. Because in developing Stages we don't know where the table will start and end because they often depend on the data entered by the user. So in this post I will explain you how to develop a subroutine to create and format a table.  So then you can call from anywhere in your main program. We are creating this table on a excel sheet.  I will explain the steps one by one.

This subroutine need 5 parameters.  We need to input sheet name, start column, end column, starting row and end row.

Sub CreateAndFormatTable(WS As Worksheet, StartCol As String, EndCol As String, StartRow As Long, EndRow As Long)

Assume that you call subroutine as follows in main program.

Dim WS_2 As Worksheet

Set WS_2 = Worksheets("Sheet2")

Call CreateAndFormatTable(WS_2, "B", "F", 3, 17)

So it will create a table in range B3:F17.
We can use With WS to make it easier to write and read the code.

With WS

End with

First we need to select the range.

.Range(StartCol & StartRow & ":" & EndCol & EndRow).Select



Next we need to add borders.

'---------------------------------------
'Add borders
'---------------------------------------
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With



Then colour the header row

'---------------------------------------
'Background Color of headers
'---------------------------------------
.Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 12611584
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
 


You can change the colours in above code to suit with your requirements. Now we can color rest of the area. I will color every other row in light blue color. But you can change it to color you prefer.

'-----------------------------------------
'Background color of other area
'-----------------------------------------
For i = StartRow + 2 To EndRow Step 2
    .Range(StartCol & i & ":" & EndCol & i).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
Next i



Next we need to set the font name and font size. I will choose “Arial” and size 11.

'-----------------------------------------
'All table font name and size
'-----------------------------------------
.Range(StartCol & StartRow & ":" & EndCol & EndRow).Select
With Selection.Font
    .Name = "Arial"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

So if you have data in the range, it will look like this now.


Now we can set the header font color and also I will make the font bold. I will choose the white color for header fonts.

'---------------------------------------------
'Set header font color and make the font bold
'---------------------------------------------
.Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
Selection.Font.Bold = True

You will get following result if you have data in the range.




So now our subroutine is complete. We can call the subroutine whenever we need.  To make your life easier I will put the whole code of the subroutine below.

Sub CreateAndFormatTable(WS As Worksheet, StartCol As String, EndCol As String, StartRow As Long, EndRow As Long)

With WS

    Dim i As Long

    .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select
   
    '---------------------------------------
    'Add borders
    '---------------------------------------
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    '---------------------------------------
    'Background Color of headers
    '---------------------------------------
    .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    '-----------------------------------------
    'Background color of other area
    '-----------------------------------------
    For i = StartRow + 2 To EndRow Step 2
        .Range(StartCol & i & ":" & EndCol & i).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    Next i
    '-----------------------------------------
    'All table font name and size
    '-----------------------------------------
    .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    '---------------------------------------------
    'Set header font color and make the font bold
    '---------------------------------------------
    .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
   
    .Range("A2").Select

End With

End Sub



You can call the subroutine as follows in main program.
Dim WS_2 As Worksheet

Set WS_2 = Worksheets("Sheet2")

Call CreateAndFormatTable(WS_2, "B", "F", 3, 17)

Create Lines With VBA

Did you know that you can do graphic related things using vba.  Yes you can develop codes to create or modify shapes available in Microsoft Excel. So let's see how we can create a  straight line. What you need to do is you need to give x and y coordinates of the start and end of the line. Consider that the upper left corner of the document as (0,0) point. And you need to give the locations in points.

Sub DrawLine_Example1()

ActiveSheet.Shapes.AddLine 0, 0, 250, 250

End Sub

If you run above macro you will get following result.



So the first two values are x and y coordinates of the starting point respectively.   and next two values are x and y coordinates of end point. And here is another example

Sub DrawLine_Example2()

ActiveSheet.Shapes.AddLine 20, 100, 100, 45

End Sub
 
It will create line like this.



Now let's look at little advanced scenario. Assume you need to create a line between following two points.




So how we do that because we don't know exact x,y coordinates of those two points. Fortunately we can give start and end points from cell locations as well. So we can create the line using following code.

Sub DrawLine_Example3()

Dim BeginX As Long
Dim BeginY As Long
Dim EndX As Long
Dim EndY As Long

With Range("B5")
   BeginX = .Left + .Width
   BeginY = .Top + .Height / 2
End With

With Range("G9")
   EndX = .Left
   EndY = .Top + .Height / 2
End With

ActiveSheet.Shapes.AddLine BeginX, BeginY, EndX, EndY

End Sub


In the above code starting point is given by following part.

With Range("B5")
   BeginX = .Left + .Width
   BeginY = .Top + .Height / 2
End With

And end point is given by these 4 lines.

With Range("G9")
   EndX = .Left
   EndY = .Top + .Height / 2
End With

Also you can replace

With Range("B5")
   BeginX = .Left + .Width
   BeginY = .Top + .Height / 2
End With

With following code. Then you don’t need width for the calculations.

With Range("C5")
   BeginX = .Left
   BeginY = .Top + .Height / 2
End With

With VBA you can not only create lines but also create any other shapes you need.

Contact Form

Name

Email *

Message *

Popular Posts