Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA: Get Source Data Range from a Chart (with Quick Steps)

In this article, I’ll show you how you can get the source data range from a chart using VBA in Excel.


Excel VBA: Get Source Data Range from a Chart (Quick View)

Sub Get_Source_Data_Range_from_Chart()

Dim SheetName As Worksheet
Dim ChartName As Chart

Set SheetName = Worksheets("Sheet1")
Set ChartName = SheetName.ChartObjects("Chart 1").Chart

MsgBox ChartName.SeriesCollection(1).Formula

End Sub

VBA Code to Get Source Data from a Chart Using Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Quick Steps to Get Source Data from a Chart Using Excel VBA

Here we’ve got a worksheet called Sheet1 that contains a company’s total sales for some years from 2011 to 2020.

Data Set to Get Source Data from a Chart Using Excel VBA

Along with the data set, there is a chart called Chart 1.

Chart to Get Source Data from a Chart Using Excel VBA

Our objective today is to get the source data set from this chart using VBA.


⧪ Step 1: Opening the Visual Basic Window

Press ALT+F11 on your keyboard to open the Visual Basic window.


⧪ Step 2: Inserting a New Module

Go to the Insert > Module option in the toolbar. Click on Module. A new module called Module1 will open.

Inserting Module to Get Source Data from a Chart Using Excel VBA


⧪ Step 3: Putting the VBA Code

This is the most important step. Put the following VBA Code in the module.

⧭ VBA Code:

Sub Get_Source_Data_Range_from_Chart()

Dim SheetName As Worksheet
Dim ChartName As Chart

Set SheetName = Worksheets("Sheet1")
Set ChartName = SheetName.ChartObjects("Chart 1").Chart

MsgBox ChartName.SeriesCollection(1).Formula

End Sub

VBA Code to Get Source Data from a Chart Using Excel VBA

⧭ Notes:

Here Sheet1 is the name of the worksheet, and Chart 1 is the name of the chart. Don’t forget to change these according to your needs.


⧪ Step 4: Saving the Workbook as a XLSM File

Next, return to the workbook and save it as an Excel Macro-Enabled Workbook.


⧪ Step 5: The Final Output

Run the code from the Run Sub / UserForm option in the toolbar.

You’ll find a message box showing you the range of the X-axis data (Sheet1!B3:B12) and Y-axis data (Sheet1!C3:C12) respectively.

Excel VBA to Get Source Data Range from a Chart

Read More: How to Expand Chart Data Range in Excel (5 Suitable Methods)


Things to Remember

Here I have focused only on getting the source data range from a chart. If you want to learn more about a VBA chart object, you can visit this link.


Conclusion

Therefore, this is the process of creating a dynamic chart using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo