Étiquette : macro_graphics

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.

The following program creates a sparkline of the line type:

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

Explanation:
The Add() method of the SparklineGroups collection creates a SparklineGroup object in the target cell (here, cell A11).

The first parameter, Type, is set to xlSparkLine from the xlSparkType enumeration, specifying a line sparkline.

The second parameter is a string representing the source data range for the sparkline values (here, cells A1 through A10).

Instead of using data bars or color scales, the relative sizes of a series of numbers can also be visually represented using different symbols, as shown in the following program:

Sub IconSetExample()
    Dim Rg As Range
    ThisWorkbook.Worksheets("Sheet6").Activate
    Set Rg = Range("A2:A11")
    ' Remove existing conditional formats
    Rg.FormatConditions.Delete
    ' Add icon set conditional formatting
    Rg.FormatConditions.AddIconSetCondition
    ' Modify icon set settings
    With Rg.FormatConditions(1)
        .IconSet = ActiveWorkbook.IconSets(1)
        .IconCriteria(2).Value = 45
        .IconCriteria(3).Value = 90
    End With
    Set Rg = Nothing
End Sub

Explanation:
The AddIconSetCondition() method creates an object of the class IconSetCondition, which is a conditional formatting rule using an icon set.

The type of icon set can be changed by assigning an element from the IconSets collection of the active workbook to the IconSet property of the conditional format.

Icon sets can contain three, four, or five different icons. In this example, a three-icon set is used.

New threshold values are set for the second and third icons by modifying the Value property of the second and third elements in the IconCriteria collection.

For example, if a cell contains a value at least 45% of the maximum value in the range, the second icon is displayed; for 90% or more, the third icon is shown. By default, the thresholds are 34% and 67%.

Excel 2007 already includes 17 different icon sets, and Excel 2010 added three more. The following program displays all 20 icon sets neatly:

Sub DisplayAllIconSets()
    Dim Rg As Range
    Dim i As Integer
    ThisWorkbook.Worksheets("Sheet7").Activate
    For i = 1 To 20
        Range("A2:A11").Copy Cells(2, i)
        Cells(1, i).Value = "S " & i
        Set Rg = Range(Cells(2, i), Cells(11, i))
        Rg.FormatConditions.Delete
        Rg.FormatConditions.AddIconSetCondition
        Rg.FormatConditions(1).IconSet = ActiveWorkbook.IconSets(i)
    Next i
    Set Rg = Nothing
End Sub

Explanation:
The values in the first column are copied into the subsequent columns.

For each column, a range of ten cells is selected for conditional formatting.

An icon set conditional format is created for each range, with the icon set type assigned from the 20 available options.

The following example demonstrates a three-color scale:

Sub ThreeColorScale()
    Dim Rg As Range
    ThisWorkbook.Worksheets("Sheet6").Activate
    Set Rg = Range("A2:A11")
    ' Remove any existing conditional formats
    Rg.FormatConditions.Delete
    ' Add a three-color scale conditional formatting
    Rg.FormatConditions.AddColorScale 3
    ' Modify the three-color scale settings
    With Rg.FormatConditions(1)
        .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
        .ColorScaleCriteria(1).FormatColor.Color = vbGreen
        .ColorScaleCriteria(2).Type = xlConditionValuePercentile
        .ColorScaleCriteria(2).FormatColor.Color = vbYellow
        .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
        .ColorScaleCriteria(3).FormatColor.Color = vbRed
    End With
    Set Rg = Nothing
End Sub

Explanation of Differences from Two-Color Scale:
The method AddColorScale() is called with the parameter value 3 to specify a three-color scale.

When modifying the scale, the middle color corresponds to the percentile value (xlConditionValuePercentile), which represents a statistical intermediate value.

This middle color is set to yellow, while the lowest and highest values are colored green and red respectively.