Open In App

Generating Dynamic Charts With VBA in Excel

Last Updated: 09 Jan, 2023

S

Sruthiprithvi
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

A powerful graph range is an information range that refreshes naturally when you change the information source. This unique reach is then utilized as the source information in an outline. As the information changes, the powerful reach refreshes right away which prompts an update in the outline. 

The following is an illustration of a graph that utilizes a unique outline range.

Sample Data 

Sample Data

 

Follow the further steps in Generating Dynamic Charts with VBA in Excel

Step 1: Opening the Visual Essential Window. Press ALT+F11 on your console to open the Visual Essential Window.

VBA code

 

Step 2: Embedding Another Module. Go to the Addition > Module choice in the toolbar. Click on Module. Another module called Module1 will be embedded.

VBA Module

 

Step 3: Putting the VBA Code

sub create _dynamic_ chart ()

Application . ScreenUpadting  = False

with Activesheet. shapes ( Application .caller ) .Fill.Forecolor

      If  . Brightnesss = 0 Then

          . Brightness   =  -0.150000006

   Else

           . Brightness   = 0

     End If

End With

   

Dim Sequence ( )  As String

Desired__shapes =   Array ( ” Rounded Rectangle 1″  ,  ”  Rounded Rectangle 2  ”  ,  ” Rounded Rectangle 

  For  i  = LBound ( Desired _Shapes ) To UBound ( Desired_Shapes)

         With ActiveSheet.Shapes(Desired _Shapes (i) )

              If .Fill . Forecolor.Brightness = -0.150000006  Then

                  Sequence ( UBound ( Sequence ) ) = . Text Frame2.TextRange . Characters . Text

                 ReDim Preseve Sequence ( UBound ( Series ) + 1 )

         End If

  End With

Next i 

If UBound ( Sequence ) >0 Then ReDim Preserve Sequence ( UBound ( Sequence ) – 1 )

Worksheets (“sheet 1″ ) . ListObjects ( ” Table 1 ” ). Range . AutoFilter Field  :=1

Worksheets ( ” sheet 1″). ListObjects ( “Table 1 ” ) . Range .AutoFilter _

      Field : =1 , Criterial : = Sequence  , Operator : =xl FilterValues

Application.ScreenUpadting = True

End Sub

Step 4: Saving the Workbook in XLSM Format, then return to the workbook and save it as an Excel Macro-Enabled Workbook.

Excel Macro-Enabled Workbook

 

Step 5: Run the code from the Run Sub/UserForm choice in the toolbar.

Run VBA

 

You’ll find a powerful graph made in view of the table in Sheet2 of the worksheet.

Dynamic Chart

 



Article Tags :

S

Sruthiprithvi
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

P

Preyum Kumar

Programming Charts in Excel VBA

article_img
VBA stands for Visual Basic for Applications and it is developed by Microsoft. MS Excel and many other Microsoft applications like word, access, etc have this language integrated into them using which one can do various things. VBA can be used in MS Excel through the code editor in its developer tab through which one can generate various types of charts and user-defined functions, connect to windows APIs, and much more. Enabling the Developer Tab in Excel To use VBA in excel first of all we will need to make the developer tab visible as it is not visible as default when it is installed. We can do this by following the steps below: Step 1: Click on the file option in the top left corner. Step 2: Click on the options tab in the bottom left corner which will take you to excel options. Step 3: Then click on the customize ribbon option and check the developer tab in the options available. Table of Data for Charts The table of data that I used for generating the chart is a small table containing the marks of students in different subjects. The table is shown below: Programming Charts in Excel VBA To produce charts from the data table present in our sheet first we need to create a
Read More

A

Ayush Dey

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
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 codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword whic
Read More

S

SOUNETRA GHOSAL

How to Create Charts in Excel Using Worksheet Data and VBA?

article_img
Excel is an important software provided by Microsoft Corporation. This software belongs to one of the major software suites Office 365. In this software suite, there are other software are present like Word, PowerPoint, etc. They are called Office 365, as this software are mostly used for office purpose. But now the world has changed a lot. After the Corona Pandemic, the world knows the positivity of using digital tools. Office 365 was not different from that. As a part of the software suite, Excel software also gains some importance from the users. They are not only used for official purposes. But they can also be used for school purposes. Excel is software that can able to store data in an effective form. So, searching for the data becomes more manageable in this software. Excel has another great feature. It can be used to derive the charts from the provided data. The charts are helpful for analyzing any growth of the data. If there are thousands of data present, it is a difficult task to extract some analysis from that data. But if those data are converted to charts, then it will be easy to analyze those data. Excel sheet helps to do the same. Charts can be prepared whatever the
Read More

A

Ayush Dey

Trapping Dynamic Ranges in Excel VBA

article_img
Dynamic Ranges give the flexibility to work with more range of cells as compared to a specific range of cells which usually limits us to work with a particular range of cells. As the size of the data may change in an excel sheet so it is also necessary to change the range in the code dynamically for that dynamic ranges are used. We can trap dynamic ranges in different ways: Last Row in ColumnLast Column in Row Special cells Used RangeCurrent RegionNamed Region TablesLast Row in Column This is used to get the number of the last row in the column by using two methods i.e Range() and End(). Range("x1048576") is the syntax where x represents the name of the cell for which we are trapping the last row and 1048576 is the total number of rows in the sheet. End(Direction as xlDirection) where we are searching for the last row we will mention xlUp to go in the last row of the worksheet. The following code is written to get the number of the last row in cell "C" Last Column in Row This is used to get the number of the last column in the row by using two methods i.e Range() and End(). Range("XFDz") is the syntax where XFD is the last column of the worksheet and z represents the number of the
Read More

R

RISHAB NIGAM

How to Create Dynamic Range Based on Cell Value in Excel VBA?

article_img
Dynamic ranges in excel VBA provide us with the flexibility to define ranges beyond the specific values. The dynamic range enables the program to perform transformation and data management steps with ease without the worry of changing the range of the data set. The dynamic range in excel VBA promotes code reusability and does not make the programming logic to be redundant. By utilizing the range object and cells object, the dynamic ranges in Excel VBA can be defined and created. There are many ways in which dynamic ranges can be created. Following are the methods listed below: Variable initialization to the range and cell object.Create a Dynamic range passed as the value in another excel cell. The following examples help in illustrating the above methods, Variable Initialization to Range and Cell Object With the change in the size of the data, it would be useful if the range object that is used to refer to the ranges can be modified. The following code helps in counting the last row and column in an excel as shown below Sub test() Dim EndR As Integer Dim lastCl1 As Integer Sheet2.Activate EndR = Sheet2.Range("A1").End(xlUp).Row lastCl1 = Sheet2.Range("XFD1").End(xlToLeft).Column Ms
Read More

R

Rishabh Chakraborty

How to Use Select Case Statement in Excel VBA?

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to discuss how to use Select Case Statement in Excel VBA. Select Case Statement of Excel VBA The select case in VBA is like a detective that investigates an expression by checking it against different scenarios listed as Case Statements, each with its own conditions. When a scenario matches the expression, it's like solving a piece of the puzzle, and the corresponding code linked to that scenario is activated. Importantly, once a match is found, the detective stops investigating and moves on to executing the discovered code. Yet, if none of the scenarios match, it's like the detective hitting a dead end. In this situation, the code associated with the Case Else statement comes into play, offering a default solution or outcome. Syntax of the Select Case Statement of Excel VBAThe VBA Select Case Statement shares similarities with the Switch Case construct found in programming languages such as Java, C#, and PHP. In Excel VBA, this statement helps
Read More

R

Rishabh Chakraborty

Excel VBA Concatenation Operators

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Concatenation means to join two or more data into a single data. There are various ways we can perform concatenation in Excel using built-in functions, operators, etc. Some helpful links to get more insights about concatenate and using VBA in Excel : Record Macros in ExcelCONCATENATE in ExcelHow to Create a Macro in Excel? In this article, we are going to see about concatenate operators and how to use VBA to concatenate strings as well as numbers. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs at the top of the Excel window.Now select Customize the Ribbon from the pop-down menu.In the Excel Options Box, check the box Developer to enable it and click on OK. Now, the Developer Tab is visible. Now, we need to open the Visual Basic Editor. There are two ways : Go to Developer and directly click on the
Read More

R

Rishabh Chakraborty

Excel VBA Comparison Operators

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Some helpful links to get more insights about Macros, VBA in Excel : 1. Record Macros in Excel. 2. How to Create a Macro in Excel? In this article, we are going to discuss various comparison operators in Excel VBA. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs in the top of the Excel window.Now select Customize the Ribbon from the pop-down menu. The Excel Options Box, check the box Developer to enable it and click on OK. Now, the Developer Tab is visible. Now click on the Visual Basic option in the Developer tab and make a new module to write the program.Developer -> Visual Basic -> Tools -> Macros Now create a Macro and give any suitable name. This will open the Editor window where can write the code. Comparison Operators in Excel:S.No.OperatorsDefinition 1<> Not equal operator is
Read More

A

Akshit

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 iterate through the elements of the collection or array. For collections, the element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, the element can only be a Variant variable.groupRequired(Must be mentioned). Name of an object collection or array (except an array of user-defined types).statementOptional (May or may not be mentioned). One or more statements are executed on each item in the group. There are 4 basic steps to writing a For Each Next Loop in VBA: Declare a variable.Write the For Each Line with the variable and collection references.Add line(s) of code to repeat for each item in the collection.Write the Next line to terminate the loop. The For…Each block is entered if there is at least one element in the group. Upon entering the loop,
Read More

A

Akshit

How to Use Do While Loop in Excel VBA?

article_img
A Do…While loop is used when we want to repeat certain set of statements as long as the condition is true. The condition may be checked at the starting or at the end of the loop Flowchart: Uses of Do-While loop: The Do While loop is used in two ways: Do…while loop which checks the condition at the STARTING of the loop.Do…while loop which checks the condition at the END of the loop. Syntax 1: Do While condition [statements] [Exit Do] [statements] Loop Syntax 2: Do While [statements] [Exit Do] [statements] Loop conditionImplementing a Do While loop: Follow the below steps to implement a Do-While loop: Step 1: Define a Macro Private Sub Demo_Loop() End Sub Step 2: Define variables j=2 i=1 Step 3: Write Do While Loop. You can write condition at the beginning or at the end Do While i < 5 Step 4: Write statements to be executed in loop msgbox "Table of 2 is : " & (j*i) i=i+1 Step 5: End loop. Now let's take a look at some of the examples. Example 1: Do…while loop which checks the condition at the STARTING of the loop. The below example uses Do…while loop to check the condition at the starting of the loop. The statements inside the loop are executed, only if the condition is True
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox