You can create a chart through Excel’s built-in chart features. While dragging the chart to any of the sides you can change the size and shape of the chart. But when you need to resize the chart to a fixed height and width. VBA macros provided different commands to fix the height and width. You can resize the chart area without changing the plot area. Besides, you can change both the chart and plot area using the VBA code. In this article, we are going to show you the steps to resize the chart plot area by VBA in Excel. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
3 Steps to Resize Chart and Plot Area with VBA in Excel
We have demonstrated 3 easy and straightforward ways to resize chart and plot area with VBA in Excel.
Not to mention, we have used the Microsoft Excel 365 version. You may use any other version at your convenience.
Step 01: Populate Dataset with Necessary Components
- Firstly, you need to populate your dataset with the necessary components. We have taken a dataset of Month-wise Sales for 2 years. With this dataset, we will plot a chart now.
Step 02: Insert a Chart
- Eventually, select the entire range of the dataset.
- Then, navigate to the Insert tab >> choose Insert Column or Bar Chart >> Clustered Column.
Finally, you will get a chart like the image below. Now, we will resize this chart with the VBA code.
- How to Create Polar Area Chart in Excel (2 Easy Ways)
- Create Stacked Area Chart with Negative Values in Excel
- Stacked Area Chart with Line in Excel (2 Practical Examples)
- How to Shade an Area of a Graph in Excel (With Easy Steps)
- Change Order of Excel Stacked Area Chart (with Quick Steps)
Step 03: Apply VBA Code to Resize Chart Plot Area
- At this moment, we will insert a VBA code to resize the chart plot area.
- Initially, select the chart area and hover over the Developer tab >> pick Visual Basic.
Note: you can open the Module by pressing the ALT + F11 key.
- Consequently, choose the Insert tab >> Module>> Module1.
- Sequentially, write up the following code in Module 1.
Sub Resize_Chart_Plot_Area() Dim ch1 As Chart, plot_height As Double, plot_width As Double Set ch1 = ActiveChart plot_height = ch1.PlotArea.Height: plot_width = ch1.PlotArea.Width ch1.Parent.Height = 225: ch1.Parent.Width = 550 ch1.PlotArea.Height = 150: ch1.PlotArea.Width = 500 End Sub
Lastly, run the code with the F5 key and your chart will be automatically resized like ours.
Read More: Excel Area Chart Data Label & Position
How to Move Chart to Specific Cell with VBA in Excel
You can set the chart in a specific cell by using the VBA code also. It means you selected the cell range and the chart will be relocated to that specific cell range.
For doing this, we will open a new module as we mentioned earlier. Then, write up the following code in that Module.
Sub Relocate_Chart() Dim ch1 As Chart On Error Resume Next Set ch1 = ActiveChart On Error GoTo 0 If ch1 Is Nothing Then MsgBox "Select the Chart First" Exit Sub End If Dim r As Range Set r = Application.InputBox("Select the Range", "Range Selection", Type:=8) ch1.Parent.Top = r.Top ch1.Parent.Left = r.Left ch1.Parent.Height = r.Height ch1.Parent.Width = r.Width ch1.Location xlLocationAsObject, r.Parent.Name End Sub
At this moment, You can add a button utilizing the Form Controls in your worksheet. We have named the button Relocate.
- Now, right-click on the button and choose Assign Macro from the Context Menu.
- Sequentially, the Assign Macro window appears. Select the Macro name to Relocate_Chart.
- Lastly, hit OK.
Finally, after clicking the Relocate button, select the range where you want to put the chart and the chart will take place there. See the below GIF for better visualization.
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
That’s all about today’s session. And these are some easy steps to resize the chart and plot area with VBA in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.