Open In App

What Does the Number in the Addchart2 VBA Macro represent?

Last Updated : 14 Jun, 2023
Improve
Improve
Like Article
Like
Save
Share
Report

A chart or graph can be made in an Excel spreadsheet by using the AddChart2 VBA macro. The desired chart type or style is indicated by the number in the AddChart2 VBA macro. The AddChart2 function uses this number as an input parameter to identify the type of chart or style to be constructed. Each chart type is given a different number. If change the argument 2 value, an area chart is represented by the number 1, a line chart by the number 3, and a pie chart by the number 5, similarly, formatting of charts can also be done if change the argument 1 value, data labels are added on the chart, by the number 202, dashed formatted chart is developed by the number 203. You can design a chart that displays your data in the most useful way for your purposes by providing the correct chart type. 

Data types 

Before moving forward, we need to learn about the various numerical data types, such as integers, decimals, or floating-point numbers, that may be part of the macro. The numbers in Addchart2 can be of any of the below-mentioned data types, hence it is necessary to know about them. 

1. Integer: Using this data format, entire values can be represented between -32,768 and 32,767.

2. Long: The bigger whole numbers between -2,147,483,648 and 2,147,483,647 are represented by this data type.

3. Single: Single-precision, floating-point numbers, which have a precision of around 7 decimal digits, are represented by this data type.

4. Double: Precision till 15 decimal places, double-precision, floating-point numbers are represented by this data type.

5. Decimal: Accuracy of 28 decimal places, this data type is used to represent decimal values.

6. Alternative: Although this data type is more versatile and may represent any sort of data, including numerical data, it is less effective than utilizing a specialized data type.

AddChart2 Function and Numbers

AddChart2 function helps create any type of chart, in Excel, by VBA. It has total 7 arguments, out of which the first two arguments are very important. Their numbering represents the chart type and the type of formatting. 

Syntax: 

ActiveSheet.Shapes.AddChart2(Style, ChartType).Select

where,

Style = It is a number, which provides formatting to your chart,

ChartType = It can be text/number, which tells the type of chart to be shown.

For Example: 

ActiveSheet.Shapes.AddChart2(203.5, 1).Select

where, 

203.5 = Adds, a light gray shade to the background of chart,

1 = Adds, an Area chart in the active worksheet.

Code

Working code for VBA using AddChart2

 

Output 

Output of the Code for AddChart2 using VBA

 

Why Formatting is represented in the Form of Numbers in AddChart2? 

Charts have thousands of formatting types, which could be best represented in the form of numbers. The macro involves formatting numbers to show currency symbols or thousands of separators or rounding numerical data to a specific number of decimal places. In VBA, value describes the type of formatting, including the usage of currency symbols and the number of decimal places. This can be accomplished by either setting the NumberFormat property of a cell or range. Rounding helps better understand the formatting numbers, but rounding needs to be done carefully because it occasionally has unforeseen results or causes computations to be inaccurate.

List of ChartType in AddChart2

The macro might entail making graphs or charts to represent numerical data, including pie charts, bar charts, or line charts. To make a new chart and add it to a worksheet or chart sheet in VBA, use the “Addchart2” technique. To know the ChartType Microsoft has provided us with the list of numbers mapped with the chart type, which can be viewed in the documentation. Here, is the list of the most famous charts, and the number corresponding to them. 

Value Description
1 Area Chart
5 Pie Chart
Line Chart
-4169 Scatter Chart

Note: It is important to note that, no such data has been provided for formatting numbers, because there could infinite of numbers, which could represent different formatting. 

Error Handling and Precautions

The macro might entail dealing with errors that could occur when processing numerical data, like divide-by-zero or overflow issues. For handling errors, VBA includes a number of built-in methods and statements, including On Error GoTo, On Error Resume Next, and Err.Raise. With the aid of these tools, programmers may find and fix problems in their code, which can help avoid crashes and other strange behavior. 

Generally, adding customized numbers, to the charts for formatting, is not a good option. VBA programmers, prefer, recording a macro, to create a chart, and then customizing it accordingly. 


Similar Reads

VBA Subroutine in Excel - How to Call Sub in VBA?
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of
3 min read
How to Create a Macro in Excel?
Macros are a set or sequence of instructions that perform as and when required by the user. Initially, a macro is to be recorded. Then the macro is being executed in order to perform a set of a predefined set of commands in Microsoft Excel. Macros save a huge amount of time in performing a set of repetitive actions. To perform a macro, one needs to
2 min read
Assigning Excel Macro to Objects
In Excel, a recorded macro can be assigned to different objects like a shape, graphic, or control note. Instead of running the macro from the required tool in ribbon, we can create these objects to run them easily. They get very handy when multiple macros are there. Individual objects can be created for different macros. In this article, let's disc
3 min read
Excel Personal Macro Workbook
When we create the macros for automation, they can be used for that particular workbook only. We cannot access them outside that workbook in others. Excel Personal Macro Workbook is a hidden workbook in your local system that pops up whenever you open the Excel application. It is a place where all the macros from any workbook can be stored and one
4 min read
How to Password Protect Your Excel Macro?
It is a best practice, to protect the Macro (VBA Code) from other users to edit or view. In this article, we explain to create a simple macro and protect the same "VBA Project" using a password in Excel from other users. Implementation: Follow the below steps to protect your excel macro with a password: Step 1: Open an excel file, to create a simpl
2 min read
How to Record a Macro in Excel
Excel Macro is simply a record button that records a set of actions performed on Excel that can be run multiple times. For example, if you have to format some raw data on a weekly basis you can use a macro to record yourself formatting the data once and let Excel automate the task in the future. it's used to help users save time and avoid performin
11 min read
How to Enable & Run a Macro in Excel? 6 Ways
We can record a macro in Excel or write from scratch in VB Editor. Excel provides different options to run a macro. In this article, we explore four different approaches to running a macro in an Excel application. Even while executing an Excel macro is a straightforward process for seasoned users, it might not be clear to newbies. You will discover
5 min read
How to Run a Macro Automatically
To run a macro automatically when the workbook opens one must enable the developer's tools in Microsoft Excel. What is a Macro in Excel Macros allows automating repetitive tasks in Microsoft Excel. Some tasks need to be performed multiple times and repetition of all the actions would consume time. In such type of Scenarios, Macros helps in automati
4 min read
Excel VBA | count() functions
Visual Basic for Applications (VBA) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA one can automate many tasks in excel and all other office software. It helps in generating reports, preparing various charts, graphs and moreover, it performs calculation using its var
2 min read
How to Use for Each Loop in Excel VBA?
A For Each loop is used to execute a statement or a set of statements for each element in an array or collection. Syntax: For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] The For...Each...Next statement syntax has the following three parts: PartDescriptionelement Required (Must be mentioned). Variable is used to
3 min read
Article Tags :