VBA & Macro Problems

How to Create an Excel VBA Macro to Draw Proportional Circles from Cell Values

Adam Davis Oct 16, 2026 870 views

Question details

The user requires a VBA macro to automatically generate scaled circles in a worksheet based on numeric values in cells A2:A20. They also need to adjust the minimum circle size and assign text labels from column B to each generated shape.

How to Create an Excel VBA Macro to Draw Circles from Cell Values

Product: Excel
Device & OS: not provided
Scenario: Automating dynamic data visualizations by generating and formatting worksheet shapes that correspond directly to spreadsheet data.
Observed behavior: The macro reads values in column A, calculates a scale factor to size the shapes properly, draws ovals in the target column, and pulls label text from column B.

Before you start

Ensure the Developer tab is enabled in your spreadsheet ribbon so you can access the VBA Editor. It is highly recommended to save a backup of your workbook as a Macro-Enabled Workbook (.xlsm) before testing new scripts.

Solution 1Recommended

Write a VBA Macro to Add, Scale, and Label Shapes

Use a VBA loop to iterate through the data range, calculate proportional dimensions, draw the shapes, and assign the adjacent text values.

To accomplish this, you will need to utilize the AddShape method within a loop. By dynamically linking the shape's Width and Height properties to the cell values in column A, you can create a scaled visualization. Applying a multiplier will ensure even the smallest values render visible circles.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications Editor. Go to Insert > Module to create a new script file.

Set Up the Loop

Write a `For` loop that iterates from rows 2 to 20. Read the numeric value from column A for each row and assign it to a variable representing the diameter.

Scale the Shapes

To make the smallest circle significantly larger (e.g., 10x larger), multiply your diameter variable by your desired scale factor (e.g., `Diameter = Cells(i, 1).Value * 10`).

Draw the Circles

Use `Set myShape = ActiveSheet.Shapes.AddShape(msoShapeOval, LeftPos, TopPos, Diameter, Diameter)` to create the circle. Ensure both the Width and Height parameters are equal so the shape remains a perfect circle rather than an ellipse.

Add Text Labels

Reference the adjacent cell in column B and apply it to the shape using `myShape.TextFrame.Characters.Text = Cells(i, 2).Value`.

Write a VBA Macro to Add, Scale, and Label Shapes

Aligning the Circles: You can center the circles nicely inside a target column by calculating the Left and Top positions based on the `TargetCell.Left` and `TargetCell.Top` properties.

Automate with WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to quickly deploy automation scripts, draw custom shapes, and manipulate data just as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the data file where you want to draw the circles.
2. Access the VBA Editor: Navigate to the Tools or Developer tab on the ribbon and click 'Visual Basic', or simply press Alt + F11.
3. Paste and Run the Code: Go to Insert > Module, paste your customized circle-drawing macro code, and press F5 to execute the visualization.

Fully compatible with Microsoft Excel (.xls, .xlsx, .xlsm) macro formats.Built-in robust VBA editor for creating, debugging, and running automation scripts.Easily link dynamic shapes to data cells for powerful reporting.Free and lightweight software suite with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I clear the previously drawn circles before running the macro again?

You can clear existing shapes by adding a loop at the beginning of your code that iterates backwards through the shapes collection: `For Each shp In ActiveSheet.Shapes`, check if it's an auto-shape, and execute `shp.Delete`.

Why is the text not fitting inside the smaller circles?

If the circle's scaled diameter is too small, the text frame cannot contain the label. You can fix this by increasing your minimum scale multiplier or by setting the shape's text frame margins to zero using `myShape.TextFrame.MarginLeft = 0`.

Can I color the circles differently based on their values?

Yes. Inside your loop, you can add conditional `If...Then` statements that evaluate the cell's value and set `myShape.Fill.ForeColor.RGB` to different color codes accordingly.

Excel & Data Problems