Musiha Mahfuza Mukta
Sometimes you may need to draw a shape or an object in Excel. So, if you are looking for drawing objects using VBA code in Excel, then you have come to the right place. Today, in this article, I’m going to explain the drawing of objects with VBA code in Excel.
Furthermore, for conducting the session, I will use Microsoft 365 version.
Download Practice Workbook
You can download the practice workbook from here:
2 Examples of Drawing Objects with VBA in Excel
Here, I will describe 2 suitable examples to draw an object with VBA coding in Excel. Also, for your better understanding, I’m going to explain both the simple and complex shapes. Furthermore, I will show you how you can format the shape.
1. Drawing of Objects (Simple Shapes) with VBA Code
In this section, I will describe the drawing of basic shapes only. Here, I will design a rectangle, a cell-sized rectangle, and a cube. Basically, for all the VBA codes, you must follow the steps given below.
Steps:
- Firstly, you need to open your worksheet. Here, you must save the Excel file as Excel Macro-Enabled Workbook (*xlsm).
- Secondly, you have to choose the Developer tab >> then select Visual Basic.
- At this time, from the Insert tab >> you have to select Module.
After that, you have to write the particular code in the Module. So, see the following codes for different shapes.
1.1. Using VBA Code to Sketch a Rectangular Shape
In this section, I will describe the VBA code for drawing a simple rectangle.
- So, write down the Code given below in Module 1.
Sub My_Rec()
Set myRec = Worksheets(1)
myRec.Shapes.AddShape msoShapeRectangle, 40, 60, 100, 50
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named My_Rec.
- Next, I used the variable myRec.
- Here, the AddShape function along with the msoShapeRectangle feature will create the rectangle shape.
- Then, 40, and 60 denote the position of the shape. Additionally, 40 is the column reference and 60 is the row reference.
- Furthermore, 100 denotes the length of the rectangle, and 50 denotes the width of the rectangle.
- Now, Save the code then go back to Excel File.
- Then, from the Developer tab >> select Macros.
- At this time, select Macro (My_Rec) and click on Run.
Finally, you will see the following shape.
Read More: How to Use Drawing Tools in Excel (2 Easy Methods)
1.2. Designing Cell Size Rectangle with a Solid Color
Here, I will show you another example of drawing a rectangle using another VBA code in Excel. Basically, I will fill the rectangle with a solid color. Moreover, the rectangle size will be similar to the cell size of Excel.
Steps:
- Firstly, from the Developer tab >> select Visual Basic >> then from the Insert tab >> you have to select Module.
- Secondly, write down the following Code in Module 2.
Sub My_Cell_Size_Rec()
ActiveSheet.Shapes.AddShape(msoShapeFlowchartProcess, 40, 60, 65, 20).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 16
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named My_Cell_Size_Rec.
- Next, the AddShape function along with the msoShapeFlowchartProcess feature will create the rectangle shape.
- Here, 40, and 60 denote the position of the shape. Additionally, 40 is the column reference and 60 is the row reference.
- Furthermore, 65 denotes the length of the rectangle, and 20 denotes the width of the rectangle.
- Then, Color code 16 is for the fill color of the shape.
- Then, Save the code then go back to Excel File >> then from the Developer tab >> select Macros.
- After that, select Macro (My_Cell_Size_Rec) and click on Run.
As a result, you will get the following rectangle.
1.3. Applying VBA Code to Draw a Cube in Excel
Now, I will show you an example of drawing a cube using the VBA code in Excel. Additionally, I will write text inside the shape.
Steps:
- First, from the Developer tab >> select Visual Basic >> then from the Insert tab >> you have to select Module.
- Now, write down the following Code in Module 3.
Sub my_cube()
Dim my_cube As Shapes
    Set my_cube = ActiveSheet.Shapes
With my_cube
    With .AddShape(msoShapeCube, 40, 60, 100, 90).TextFrame
        .Characters.Text = "Exceldemy"
        .HorizontalAlignment = xlHAlignCenter
        .VerticalAlignment = xlVAlignDistributed
    End With
End With
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named my_cube.
- Then, I declared a variable my_cube as Shapes.
- Next, the AddShape function along with the msoShapeCube feature will create the cube shape.
- Here, 40, and 60 denote the position of the shape. Additionally, 40 is the column reference and 60 is the row reference.
- Furthermore, 100 denotes the length and height of the cube, and 90 denotes the width of the cube.
- Then, the Characters.Text property will consider Exceldemy which will be written on the shape.
- After that, Save the code then go back to Excel File >> then from the Developer tab >> select Macros.
- Lastly, select Macro (my_cube) and click on Run.
Finally, you will see the following cube.
Read More: How to Do Drawing in Excel Using VBA (5 Ideal Examples)
Similar Readings
- Remove Drawing Tools in Excel (3 Easy Methods)
- How to Draw to Scale in Excel (2 Easy Ways)
- Draw Engineering Drawing in Excel (2 Suitable Examples)
- How to Draw a Floor Plan in Excel (2 Easy Methods)
2. Use of a VBA Code to Design a Complex Shape with Texts in Excel
In this section, I will create a complex shape with the help of VBA code in Excel. Furthermore, I will use text on the shape.
Steps:
- Like the previous way, from the Developer tab >> select Visual Basic >> then from the Insert tab >> you have to select Module.
- Then, write down the following Code in Module 4.
Sub Different_shape_with_Exceldemy()
    Dim my_int, my_intgr      As Integer
    Dim my_x, my_y, my_z, my_sng, my_size    As Single
    Dim my_shape     As Shape
    Dim my_difined_text    As String
    Dim start_frm_left, start_cell_top As Single
    start_frm_left = ActiveCell.Left
    start_cell_top = ActiveCell.Top
    my_difined_text = "Hi Exceldemy"
    my_sng = 12
    my_intgr = Len(my_difined_text)
    my_size = Application.InchesToPoints(1)
    For my_int = 1 To my_intgr
        If Mid(my_difined_text, my_int, 1) <> " " Then
            my_x = my_sng * my_int / my_intgr
         my_x = Application.InchesToPoints(my_x)
            my_y = Application.InchesToPoints(my_z)
            If my_int Mod 2 = 1 Then
                Set my_shape = ActiveSheet.Shapes.AddShape(msoShapeUpRibbon, start_frm_left + my_x, start_cell_top + my_y, my_size, my_size)
            Else
                Set my_shape = ActiveSheet.Shapes.AddShape(msoShapeDownRibbon, start_frm_left + my_x, start_cell_top + my_y, my_size, my_size)
            End If
            my_shape.Fill.ForeColor.RGB = RGB(chng_shape_clr(155, 200), chng_shape_clr(200, 255), chng_shape_clr)
            my_shape.Fill.Visible = msoTrue
            my_shape.TextFrame.Characters.Text = UCase(Mid(my_difined_text, my_int, 1))
            my_shape.TextFrame.Characters.Font.Size = 24
            my_shape.TextFrame.Characters.Font.Name = "Calibri"
            my_shape.TextFrame.Characters.Font.Bold = True
            my_shape.TextFrame.Characters.Font.Color = RGB(255, 0, 0)
            my_shape.TextFrame2.VerticalAnchor = msoAnchorMiddle
            my_shape.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
        End If
    Next my_int
End Sub
Function chng_shape_clr(Optional lwr_bnd As Integer = 100, Optional upr_bnd As Integer = 255) As Integer
    chng_shape_clr = Int((upr_bnd - lwr_bnd + 1) * Rnd + lwr_bnd)
End Function
Code Breakdown
- Here, I have created a Sub Procedure named Different_shape_with_Exceldemy.
- Then, I declared some variables my_int, my_intgr as Integers; my_x, my_y, my_z, my_sng, my_size, start_frm_left, start_cell_top as Singles; my_shape as Shape; my_difined_text as Strings.
- Actually, you can insert a new text by changing the my_difined_text variable.
- Basically, you have to identify the total space for the text in the variable my_sng.
- Next, the AddShape function along with the msoShapeUpRibbon, and msoShapeDownRibbon features will create that complex shape.
- Then, I used some properties for the text on the shape.
- Furthermore, I used a function named chng_shape_clr for changing the shape colors.
- After that, Save the code then go back to Excel File >> then from the Developer tab >> select Macros.
- Lastly, select Macro (Different_shape_with_Exceldemy) and click on Run.
Lastly, you will see the following shape with a defined text on it.
Read More: How to Draw a Line Through Text in Excel (6 Easy Ways)
Employing VBA Code for Formatting Objects in Excel
Here, in this section, I will show you the formatting of a given shape by using VBA code in Excel. Furthermore, I will use text on the shape. Now, let’s have the following shape in your sheet.
Steps:
- Now, from the Developer tab >> select Visual Basic >> then from the Insert tab >> you have to select Module.
- Then, write down the following Code in Module 5.
Sub Format_my_Shape()
Dim my_shape As Shape
Worksheets(5).Shapes(1).Fill.ForeColor.RGB = RGB(192, 32, 255)
For Each my_shape In ActiveSheet.Shapes
my_shape.Flip msoFlipHorizontal
Next
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named Format_my_Shape.
- Then, I declared a variable my_shape as Shapes.
- Next, the Worksheets (5) will call the 5-number worksheet. So, you must identify the proper worksheet number where the shape is.
- Here, I used an RGB color code for the filling of the shape.
- Furthermore, the For Each Next loop will call the shape again and flip the shape.
- Then, Save the code then go back to Excel File >> then from the Developer tab >> select Macros.
- After that, select Macro (Format_my_Shape) and click on Run.
As a result, you will get the following formatted shape.
Read More: How to Remove Unwanted Objects in Excel (4 Quick Methods)
Practice Section
Now, you can practice the explained examples by yourself.
Conclusion
I hope you found this article helpful. Here, I have explained 2 easy ways for drawing Objects with VBA in Excel. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
