Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba insert

How to insert a picture in excel using VBA

Dear Friends,
In this article, I am going to teach you a simple VBA code, which help you in inserting a picture in Excel Sheet. I will also discuss about difference between Inserting a picture in Excel and Embedding a picture in Excel Sheet using Excel VBA.
It is based on request from one of LEM reader who wants to know, How to insert a picture in excel sheet using VBA code It is a very simple one liner code to insert a picture in Excel using vba code.

Insert Picture Using VBA Code

Basically, there are two methods to insert a picture in Excel Sheet
Method 1. ActiveSheet.Pictures.Insert
Method 2. ActiveSheet.Shapes.AddPicture

VBA Code for Inserting Picture in Excel Sheet [Method 1]

Using .Pictures.Insert() method, you can insert a picture in Active sheet. Let see the Syntax of this method:

Syntax of .Pictures.Insert Method

This function requires only one parameter – Full path of the picture to be inserted in Excel Sheet. This is a mandatory parameter here.

For Example:

Above statement will simply insert myPic.jpg picture in Active sheet in its original Size.
If you want to resize and locate the picture according to you, then use the following statement to resize the image and place it where ever you want in the excel sheet.

1. VBA Code to re-size (height and width) the inserted picture

Below code will set the height and width of the selected picture in worksheet which is inserted using VBA code:

Explanation and issues with above Code

Left and Top will be set without any issue.
Later, Width of the image will be set to 123 as specified – Height of the image will be automatically set to a respective height to the width – because AspectRatio of the image is by default set to True
Similarly when control goes to the next statement then it will reset the height to 134 and since, aspect ratio is false, width will be adjusted to new respective value.

Challenge here is that you can NOT set AspectRatio flag of the picture while inserting it. (by above statement)

Therefore, be careful while resizing the picture while inserting it by using the above code

So what is the solution?

Here is the solution…
1. first add the picture in its own size.
2. Store the name of this image (uniquely generated one) in a variable. So that you can refer this picture uniquely later on
3. Using this variable, select that Shape and set the aspect ratio to false
4. Then set the height and width of the picture.

Here is the code now…

2. VBA Code to set the location of the inserted Picture

Here you can either set fixed Left and Top value where you want to place your picture. In this case no matter what is the height and width of the cell in the worksheet, your picture will be always placed at a specific location. But suppose if you want – your picture should always be placed at a specific row and column then you can set the left and top values as follows:

Now your selected picture will always be placed where Column A1 starts from left and Row 1 starts from top. It means even if you change height or width of the Range A1, your picture is always going to be in Range A1 only.

Warning!
This method, simply links the image in to your Excel Sheet. It means, after inserting a picture, using this method, if you send it to another computer, picture will not be displayed and an Error message be displayed.

Therefore, this method is good only when you are going to use this excel sheet always in your own computer.

VBA Code for Embedding Picture in Excel Sheet [Method 2]

Using .Shapes.AddPicture() method, you can insert a picture in Active sheet. This method overcome the challenges of above method. This allows user to Embed the picture with the Excel Workbook itself. It means, even if you share the workbook to other computer… this picture will go with the document and you will be able to see it in other computer as well.

Syntax of .Shapes.AddPicture Method

Where:

Filename : (Mandatory) As the names suggests, this is the complete file path of the picture you want to embed to your Excel Sheet
LinkToFile : (Mandatory) MsoTriState- True or False – To set whether you want to create a link to the file?
SaveWithDocument : (Mandatory) MsoTriState – True or False – This is the flag which needs to be set to TRUE to embed the picture with Excel Sheet.
Left : (Mandatory)The position of the upper-left corner of the picture with respect to the upper-left corner of the document.
Top : (Mandatory) The position (in points) of the upper-left corner of the picture with respect to the top of the document.
Width : (Mandatory) The width of the picture you want to set. To keep the picture in its original width provide -1
Height : (Mandatory) The Height of the picture you want to set. To keep the picture in its original Height provide -1

Example:

Following VBA code will Embed this picture with the Excel file and it will display in any computer you sent it.

Info !
Therefore .Shapes.AddPicture Method can insert a picture with and without links just simply by passing some flags.

For your practice I have created an Excel workbook which you can download and play around.

Insert Blank Rows Between Existing Rows In Excel VBA

Sometimes, you may need to insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This simple macro will help you to do this. If you don’t want to use VBA to do this, see this tip.

Читать еще:  Vba excel поиск

VBA Code

How This Macro Works

This macro performs a reverse loop through the chosen range using a counter. It starts at the last row of the range inserting one blank rows, and then moves to the previous row in the range. It keeps doing that same insert for every loop, each time increment the counter to the previous row.

  1. In first Step, We first declare two variables. The first variable is an object variable called MyRange. This is an object variable that defines the target range. The other variable is a Long Integer variable called iCounter. This variable serves as an incremental counter.
  2. In Step 2, the macro fills the MyRange variable with the target range. In this example, we are using Range(“A2:C8”). If your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
  3. In this step, the macro sets the parameters for the incremental counter to start at the max count for the range (MyRange.Rows.Count) and end at 2 (the second row of the chosen range). Note that we are using the Step -1 qualifier. Because we specify Step -1, Excel knows we are going to increment the counter backwards, moving back one increment on each iteration. In all, Step 3 tells Excel to start at the last row of the chosen range, moving backward until it gets to the second row of the range.
  4. When working with a range, you can explicitly call out a specific row in the range by passing a row index number to the Rows collection of the range. For instance, Range(“A2:C8”).Rows(4) points to the forth row in the range A2:C8. In Step 4, the macro uses the iCounter variable as an index number for the Rows collection of MyRange. This helps pinpoint which exact row the macro is working with in the current loop. The macro then uses the EntireRow.Insert method to insert a new blank row. If you want to insert two blank rows, do this twice.
  5. In Step 5, the macro loops back to increment the counter down.

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

  1. Activate the Visual Basic Editor by pressing ALT F11 .
  2. Right-click the project/workbook name in the Project window.
  3. Choose Insert ->Module.
  4. Type or paste the code in the newly created module.

How to insert an Excel VBA custom function

We can use Excel’s Insert Function dialog box to locate a custom function and insert it in a formula. I have discussed it in this article: How to Create Custom VBA functions and Use them in the Worksheet.

Note: If you define your function procedure with a Private keyword, then you’ll not find it in the Insert Function dialog box. If you create a function only to use in other procedure, not for use as worksheet functions, then it is a good idea to define this function with Private keyword.

Table of Contents

Insert an Excel VBA function: Using Insert Function dialog box

You also can add a description of your custom function in the Insert Function dialog box in the following process:

  • First of all, you have to create a function in a module by using the VB Editor.
  • Now activate the Excel workbook that holds your function.
  • Choose Developer ➪ Code ➪ Macros. The Macro dialog box appears.
  • Type the name of the function in the Macro Name field. Notice that the functions are typically not displayed in this dialog box. So you have to enter the function name to find out the function. If you wrongly type the function name or Excel doesn’t find the function, except Create, no other options will work of the dialog box. I am searching for “NumberSign” function, see what happens when I type “NumberSig” in the following image.

Incomplete or wrongly typed function name will not active all the options of the Macro dialog box.

  • After Excel gets the function, it shows all the controls actionable, except Create control. Click the Options button. The Macro Options dialog box appears. See below.

Enter an overview of the function in the description field of Macro Options dialog box.

  • Enter a description of the function and then click OK. You can enter a shortcut key for a function, but it is irrelevant as you can’t use it.
  • The description that you enter is displayed in the Insert Function dialog box.

Insert Function dialog box shows the short description of a function, you select.

Insert an Excel VBA function: Using MacroOptions method

There is another way how you can add description of a custom function. Excel provides a method “MacroOptions” that you can use to do so.

In our above example, I’ve used Insert Function dialog box to add a function description. I shall use now MacroOptions method to do the same thing, additionally I’ll also add description of the arguments what is not possible using Insert Function dialog box.

My function is NumberSign and it has one argument Number. My function description is: “Check the sign of a number” and Argument description is: “This value will be checked”. I am planning to add this function under “Math & Trig” category (numerically this category is represented as 3). My Subprocedure will like the following one:

You have to run this Subprocedure(CreateArgDescriptions) for at least one time to show the description of the function and argument. After running this procedure, find this function under Math & Trig category in the Insert Function dialog box and click on this to appear Function arguments dialog box.

Use the Function Arguments dialog box to insert argument to a custom function.

Читать еще:  Excel вылетает при открытии файла

How to insert and run VBA code in Excel — tutorial for beginners

by Alexander Frolov | updated on January 20, 2020 180 Comments

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

Most people like me and you are not real Microsoft Office gurus. So, we may not know all specificities of calling this or that option, and we cannot tell the difference between VBA execution speed in Excel 2019, 2016, 2013 and 2010. We use Excel as a tool for processing our applied data.

Suppose you need to change your data in some way. You googled a lot and found a VBA macro that solves your task. However, your knowledge of VBA leaves much to be desired. Feel free to study this step-by-step guide to be able to use the code you found:

Insert VBA code to Excel Workbook

  1. Open your workbook in Excel.
  2. Press Alt + F11 to open Visual Basic Editor (VBE).
  3. Right-click on your workbook name in the «Project-VBAProject» pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
  4. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor («Module1» window).

Tip: Speed up macro execution

If the code of your VBA macro does not contain the following lines in the beginning:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Then add the following lines to get your macro to work faster (see the screenshots above):

  • To the very beginning of the code, after all code lines that start with Dim (if there are no «Dim» lines, then add them right after the Sub line):
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
  • To the very of the code, before End Sub:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

These lines, as their names suggest, turn off screen refresh and recalculating the workbook’s formulas before running the macro.

After the code is executed, everything is turned back on. As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells’ contents).
Save your workbook as «Excel macro-enabled workbook«.

Press Crl + S , then click the «No» button in the «The following features cannot be saved in macro-free workbook» warning dialog.

The «Save as» dialog will open. Choose «Excel macro-enabled workbook» from the «Save as type» drop-down list and click the Save button.

  • Press Alt + Q to close the Editor window and switch back to your workbook.
  • How to run VBA macros in Excel

    When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the «Macro» dialog.

    Then select the wanted macro from the «Macro Name» list and click the «Run» button.

    You may also be interested in:

    Excel: featured articles

    Outlook: featured articles

    Google Sheets: featured articles

    Sign in

    180 Responses to «How to insert and run VBA code in Excel — tutorial for beginners»

    Hi, I added a VBA code for a new Formula (CONCATENATEIFS) and it works greate but the only problem is that its super slow, I tried to use your speed up tip but it ended up slower (probably because I dont know where exactly to put it because the formula doesn´t have a Sub line) Can you help me?
    The formula is this one: Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ‘ Source: EileensLounge.com, August 2014
    Dim i As Long
    Dim c As Long
    Dim n As Long
    Dim f As Boolean
    Dim Separator As String
    Dim strResult As String
    On Error GoTo ErrHandler
    n = UBound(Criteria)
    If n =»
    If Criteria(c).Cells(i).Value »
    If Criteria(c).Cells(i).Value Peggy Wong says:

    Hi, may I have the excel vba code and what is the meaning. so that I can more understanding. try to how to use.

    Hi this is Sohaib. I have learned the VBA for converting numbers into words but I also want to add the word «only» after converting the numbers into words. Please help if you can.

    Function SpellCurr(ByVal MyNumber, _
    Optional MyCurrency As String = «Rupee», _
    Optional MyCurrencyPlace As String = «P», _
    Optional MyCurrencyDecimals As String = «Paisa», _
    Optional MyCurrencyDecimalsPlace As String = «S»)

    ‘*****************************************************************************************************************
    ‘* Based on SpellNumbers UDF by Microsoft, Which handles only Dollars as currency *
    ‘* UDF modfied by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on July 21, 2009 *
    ‘* UDF modified on September 04, 2009 to make currency inputs optional, by default it will use Indian Currency *
    ‘* This modified UDF can be used for any currency in case you provide for currency inputs *
    ‘* User can define the Prefix and Sufix place for Currency and CurrencyDecimals *
    ‘* MyNumber = Numeric Value you need to convert into words *
    ‘* MyCurrency = Name of your Currency — i.e. Dollar for USA *
    ‘* MyCurrencyPlace = Prefix or Suffix the currency, use «P» for Prefix and «S» for Suffix *
    ‘* MyCurrencyDecimals = Name of your Currency Decimals — i.e. Cent for USA *
    ‘* MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use «P» for Prefix and «S» for Suffix *
    ‘*****************************************************************************************************************

    Dim Rupees, Paisa, Temp
    Dim DecimalPlace, Count

    ReDim Place(9) As String
    Place(2) = » Thousand »
    Place(3) = » Million »
    Place(4) = » Billion »
    Place(5) = » Trillion «

    ‘String representation of amount.
    MyNumber = Trim(Str(MyNumber))

    ‘Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, «.»)

    ‘ Convert Paisa and set MyNumber to Rupee amount.
    If DecimalPlace > 0 Then
    Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
    «00», 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace — 1))
    End If

    Do While MyNumber «»
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp «» Then Rupees = Temp & Place(Count) & Rupees
    If Len(MyNumber) > 3 Then
    MyNumber = Left(MyNumber, Len(MyNumber) — 3)
    Else
    MyNumber = «»
    End If
    Count = Count + 1

    Читать еще:  Как посчитать в excel перцентиль

    If MyCurrencyPlace = «P» Then
    Select Case Rupees
    Case «»
    Rupees = MyCurrency & «s» & » Zero»
    Case «One»
    Rupees = MyCurrency & » One»
    Case Else
    Rupees = MyCurrency & «s » & Rupees
    End Select
    Else
    Select Case Rupees
    Case «»
    Rupees = «Zero » & MyCurrency & «s»
    Case «One»
    Rupees = «One » & MyCurrency
    Case Else
    Rupees = Rupees & » » & MyCurrency & «s»
    End Select
    End If

    If MyCurrencyDecimalsPlace = «S» Then
    Select Case Paisa
    Case «»
    Paisa = » Only»
    Case «One»
    Paisa = » and One » & MyCurrencyDecimals & » Only»
    Case Else
    Paisa = » and » & Paisa & » » & MyCurrencyDecimals & «s Only»
    End Select
    Else
    Select Case Paisa
    Case «»
    Paisa = » Only»
    Case «One»
    Paisa = » and » & MyCurrencyDecimals & » One » & » Only»
    Case Else
    Paisa = » and » & MyCurrencyDecimals & «s » & Paisa & » Only»
    End Select
    End If

    SpellCurr = Rupees & Paisa

    Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right(«000» & MyNumber, 3)
    ‘ Convert the hundreds place.
    If Mid(MyNumber, 1, 1) «0» Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & » Hundred »
    End If

    ‘ Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) «0» Then
    Result = Result & GetTens(Mid(MyNumber, 2))
    Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
    End Function

    Dim Result As String
    Result = «» ‘ Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then ‘ If value between 10-19.
    Select Case Val(TensText)
    Case 10: Result = «Ten»
    Case 11: Result = «Eleven»
    Case 12: Result = «Twelve»
    Case 13: Result = «Thirteen»
    Case 14: Result = «Fourteen»
    Case 15: Result = «Fifteen»
    Case 16: Result = «Sixteen»
    Case 17: Result = «Seventeen»
    Case 18: Result = «Eighteen»
    Case 19: Result = «Nineteen»
    Case Else
    End Select
    Else ‘ If value between 20-99.
    Select Case Val(Left(TensText, 1))
    Case 2: Result = «Twenty »
    Case 3: Result = «Thirty »
    Case 4: Result = «Forty »
    Case 5: Result = «Fifty »
    Case 6: Result = «Sixty »
    Case 7: Result = «Seventy »
    Case 8: Result = «Eighty »
    Case 9: Result = «Ninety »
    Case Else
    End Select

    Result = Result & GetDigit _
    (Right(TensText, 1)) ‘ Retrieve ones place.
    End If
    GetTens = Result
    End Function

    Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = «One»
    Case 2: GetDigit = «Two»
    Case 3: GetDigit = «Three»
    Case 4: GetDigit = «Four»
    Case 5: GetDigit = «Five»
    Case 6: GetDigit = «Six»
    Case 7: GetDigit = «Seven»
    Case 8: GetDigit = «Eight»
    Case 9: GetDigit = «Nine»
    Case Else: GetDigit = «»
    End Select
    End Function

    Excel insert file with and without VBA

    This Excel tutorial explains how to insert file (insert object) in Excel using VBA and without using VBA.

    Excel insert file object (non-VBA)

    In Excel, insert file is known as insert object, there are two kinds of insert:

    1. Embedded Object – insert the source file into Excel
    2. Linked Object – insert the link of file into Excel, not the actual source file

    In terms of display of the inserted file, there are also two kinds:

    1. Display file with an icon, double click on the icon to open the file
    2. Display the file contents directly in spreadsheet

    The below screenshots were captured in Excel 2013, but the navigation is more or less the same for other versions.

    Click on INSERT tab > Object

    There are two tabs under Object box: Create New and Create from File

    Since we insert file but not create new file, select Create from File.

    Click on Browse button to select a file

    There are two check boxes on the right hand side:

    If checked, link to the path of the original file. Changes are made in the original file.

    Now the file is inserted. Double click on the object to open the file.

    If you have checked the box “Link to file“, the file label will be displayed as the full file path.

    If you don’t like the icon or caption, right click on the file > Document Object > Convert > Change icon

    Excel insert file object (VBA)

    Suppose you have created a template as below, you want users to click on the Browse button and then the attachment shows up in Cell D3.

    Insert the below Procedure in a Module and link the button to the Procedure.

    As you browse the target file, the file is inserted into Cell D3.

    Note that in the Macro, I created a custom Function to extract the file name from the file path, because I found that sometimes the file label is displayed as full path and sometimes just file name although I set the Link property to False.

    On the other hand, I select Excel icon as the default icon for all files, but when you save the workbook, the icons will automatically change according to the file type.

    Excel insert multiple file objects (VBA)

    Application.GetFileName Method has an argument called MultiSelect, which allows users to select multiple files and return an array of file names. I modify the above procedure a little bit, if you have selected multiple files, each object is inserted on the right hand side of the starting cell.

    Let’s say we want to import the below files of different file types, click on Open button

    Now the files are inserted with cell width and height adjusted

    Again, if you save the workbook, the icons will automatically change according to the file type.

    Excel insert file with hyperlink (VBA)

    Instead of inserting an object, you can also add a hyperlink that links to the file path using VBA hyperlink function.

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×