Sometimes you may need to draw a shape or an object in Excel. So, if you are looking for how to perform drawing of 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 the Microsoft 365 version.
How to Perform Drawing of Objects with VBA in Excel (2 Easy Examples)
Here, I will describe 2 suitable examples of drawing 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
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
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: Drawing Decision Trees in Excel
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 Create Drawing Register in Excel
Practice Section
Now, you can practice the explained examples by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 2 easy ways of 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.
Related Articles
<< Go Back to Drawing in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
