# How to Perform Drawing of Objects with VBA in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. #### 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()
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. ### 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. ## 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. ## Practice Section

Now, you can practice the explained examples by yourself. ## Related Articles Musiha Mahfuza Mukta

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  