Étiquette : excel_vba

The following program inserts the picture from the previous example multiple times at a reduced size of 100 × 75 points. Different parts of the images are cropped. The crop values must relate to the original image size of 288 × 216 points. For example, to display only the left or right half of the image, a crop value of 144 points is required.

Sub CropPictures()
    Dim Sh(1 To 6) As Shape
    Dim X As Shape
    Dim FilePath As String
    Dim i As Integer
    ThisWorkbook.Worksheets("Sheet10").Activate
    FilePath = ThisWorkbook.Path & "C:\Users\POPOLY\Desktop\fleur.jpeg"
    ' Delete all existing shapes
    For Each X In ActiveSheet.Shapes
        X.Delete
    Next X
    ' Insert images and crop different parts
    Set Sh(1) = ActiveSheet.Shapes.AddPicture(FilePath, msoFalse, msoTrue, 10, 10, 100, 75)
    Set Sh(2) = ActiveSheet.Shapes.AddPicture(FilePath, msoFalse, msoTrue, 10, 95, 100, 75)
    Sh(2).PictureFormat.CropLeft = 144 ' Crop left half
    Set Sh(3) = ActiveSheet.Shapes.AddPicture(FilePath, msoFalse, msoTrue, 10, 180, 100, 75)
    Sh(3).PictureFormat.CropRight = 144 ' Crop right half
    Set Sh(4) = ActiveSheet.Shapes.AddPicture(FilePath, msoFalse, msoTrue, 120, 10, 100, 75)
    Sh(4).PictureFormat.CropTop = 108 ' Crop top half
    Set Sh(5) = ActiveSheet.Shapes.AddPicture(FilePath, msoFalse, msoTrue, 230, 10, 100, 75)
    Sh(5).PictureFormat.CropBottom = 108 ' Crop bottom half
    Set Sh(6) = ActiveSheet.Shapes.AddPicture(FilePath, msoFalse, msoTrue, 230, 180, 100, 75)
    Sh(6).PictureFormat.CropRight = 144 ' Crop right half
    Sh(6).PictureFormat.CropBottom = 108 ' Crop bottom half
    ' Output properties for overview
    Cells(1, 8).Value = "Number"
    Cells(1, 9).Value = "Left"
    Cells(1, 10).Value = "Top"
    Cells(1, 11).Value = "Width"
    Cells(1, 12).Value = "Height"
    For i = 1 To 6
        Cells(i + 1, 8).Value = i
        Cells(i + 1, 9).Value = Sh(i).Left
        Cells(i + 1, 10).Value = Sh(i).Top
        Cells(i + 1, 11).Value = Sh(i).Width
        Cells(i + 1, 12).Value = Sh(i).Height
        Set Sh(i) = Nothing
    Next i
End Sub

Explanation:
References to the six picture objects are stored in an array.

The file path is assigned, and all existing shapes on the worksheet are deleted.

The first picture object is inserted at the top-left and is not cropped. It serves as a reference for comparison with the other five pictures.

The PictureFormat property of a Shape object allows manipulation of the picture, including cropping.

The second picture is inserted below the first, with the left half cropped (CropLeft = 144).

The third picture is inserted below the second, cropping the right half (CropRight = 144).

The fourth picture is placed to the right of the first, cropping the top half (CropTop = 108).

The fifth picture is placed right of the fourth, cropping the bottom half (CropBottom = 108).

The sixth picture is placed at the bottom right, cropping both the right half and bottom half (CropRight = 144 and CropBottom = 108), leaving only the upper-left quarter visible.

The following program inserts a picture into the worksheet:

Sub InsertPicture()
    Dim Sh As Shape
    ThisWorkbook.Worksheets("Sheet10").Activate
    ' Delete all existing shapes on the sheet
    For Each Sh In ActiveSheet.Shapes
        Sh.Delete
    Next Sh
    ' Add picture from file
    Set Sh = ActiveSheet.Shapes.AddPicture( _
        ThisWorkbook.Path & "C:\Users\POPOLY\Desktop\fleur.jpeg", _
        msoFalse, msoTrue, 10, 10, -1, -1)
    ' Display position and size of the inserted picture
    MsgBox Sh.Left & "/" & Sh.Top
    MsgBox Sh.Width & "/" & Sh.Height
End Sub

Explanation:
The variable Sh is used as a reference to the picture object.

First, all existing shapes on the worksheet are deleted.

The method AddPicture() of the Shapes collection adds a picture object from an image file.

The first parameter is the file path and name.

The second parameter, set to msoFalse, specifies that the picture is embedded as a copy inside the Excel file. If set to msoTrue, the picture would be linked to the external image file, so changes to the file would update the Excel image.

The third parameter further controls linking behavior. Since the picture is embedded here, this is set to msoTrue.

The fourth and fifth parameters specify the distance in points from the left and top edges of the worksheet to the top-left corner of the image.

The last two parameters set the width and height of the image in points. Here, both are set to -1, which causes the picture to be displayed at its original size. For example, an image sized 400 × 300 pixels corresponds approximately to 288 × 216 points.

The return value of AddPicture() is a reference to the Shape object, through which you can read the position properties (Left and Top) and size properties (Width and Height) in points.

SmartArt graphics allow you to quickly and clearly represent relationships, processes, or hierarchies.It was created via the menu: INSERT • SMARTART • LIST • SIMPLE BLOCK LIST. Afterwards, the text was modified through the text pane, and the graphic was repositioned.

In VBA, a SmartArt graphic is treated as a group of shapes. However, the properties of these shapes can only be read, not modified.

The following program outputs the positions of the individual blocks:

Sub ReadSmartArtPositions()
    Dim i As Integer
    Dim s As String
    Dim Sh As Shape
    ' Select the first SmartArt object
    Set Sh = ThisWorkbook.Worksheets("Sheet9").Shapes(1)
    ' Collect the Top and Left positions of all group items
    For i = 1 To Sh.GroupItems.Count
        s = s & Int(Sh.GroupItems(i).Top) & _
            " " & Int(Sh.GroupItems(i).Left) & vbCrLf
    Next i
    MsgBox s
    Set Sh = Nothing
End Sub

Explanation:
The GroupItems collection contains all elements within the SmartArt group.

Each individual element of the group can be accessed by its index.

The Top and Left properties of each block are gathered and displayed in a message box, as illustrated in Figure.

The following program provides an overview of all available colors for sparklines, both for bars and lines:

Sub DisplaySparklineColors()
    Dim i As Integer
    ThisWorkbook.Worksheets("Sheet8").Activate
    For i = 1 To 11
        Cells(i + 13, 1).SparklineGroups.Add _
            xlSparkColumn, "A1:A10"
        Cells(i + 13, 1).SparklineGroups.Item(1). _
            SeriesColor.ThemeColor = i
    Next i
    ' Set background color for better visibility of white bars
    Cells(14, 1).Interior.Color = vbBlack
End Sub

Explanation:
The colors are numbered from 1 to 11.

For clearer visibility, the cell containing the white bars is given a black background.

Certain values in a sparkline can be highlighted, and colors can be customized. Here is an example:

Sub FormatLineSparkline()
    ThisWorkbook.Worksheets("Sheet8").Activate
    With Range("A11").SparklineGroups
        .Add xlSparkLine, "A1:A10"
        .Item(1).SeriesColor.ThemeColor = 2
        .Item(1).Points.Markers.Visible = True
        .Item(1).Points.Markers.Color.ThemeColor = 10
        .Item(1).Points.Negative.Visible = True
        .Item(1).Points.Negative.Color.ThemeColor = 9
    End With
End Sub

Explanation:
First, the color of the sparkline is changed via the ThemeColor sub-property of the SeriesColor property for the first element in the SparklineGroups collection. The possible theme colors will be demonstrated in a subsequent example.

Data points are given visible markers with a specific color. This is controlled by properties of the Markers object within the Points collection: first, the Visible property is set to True, then the ThemeColor sub-property of the Color property is set to specify the marker color.

Negative points are also highlighted and colored using the Negative property, with visibility enabled and a theme color assigned.

Finally, here is a win-loss indicator represented by bars that distinguish positive and negative values:

Sub CreateWinLossSparkline()
    ThisWorkbook.Worksheets("Sheet8").Activate
    Range("A11").SparklineGroups.Add _
        xlSparkColumnStacked100, "A1:A10"
End Sub

Explanation:
The element xlSparkColumnStacked100 from the xlSparkType enumeration creates a sparkline that visually differentiates between positive and negative values using stacked columns.

A sparkline of the column type is created in a similar manner:

Sub CreateColumnSparkline()
    ThisWorkbook.Worksheets("Sheet8").Activate
    Range("A11").SparklineGroups.Add xlSparkColumn, "A1:A10"
End Sub

Explanation:
This time, the element xlSparkColumn from the xlSparkType enumeration is used, which generates a series of vertical bars representing the data.