Thanks to Nick Williams at for this guest post! All content that follows is credited to Nick Williams, and was directly provided to Excel Exposure: This article gives people who are new to a very good overview of the practicalities of how VBA code is written and used in Excel, as well as an introduction to some of the key concepts in VBA programming. In Section 1 we show readers, step-by-step, how to open up the VBA editor, how to create modules into which we then cut and paste our pre-prepared code and finally we show them how to run and use the code. In step 2 we then step through the VBA code that we have just used at a relatively high level to explain what each section does and how it works. The aim of this is not to explain the detail of the code but to give beginners a high level introduction to the program writing process and also the types of things that VBA can be used for. Section 1 — SETTING UP THE VBA EDITOR AND RUNNING OUR CODE There are several different ways to add a chart to a spreadsheet using Excel VBA. In this example we use the Chart Sheet method which is the most efficient and offers the most control over position, size, and worksheet placement. This type of chart offers us the ability to adjust a wide variety of variables use our VBA code. The practical example we will use is a relatively short program that allows you to create a chart from a small number of inputs and then export it once you have created it. First create a new Excel workbook. In the pop-up screen navigate to a location in your computer where you want to save this application and name your file. Before you click save, it is very important that under the File Name section you click on the dropdown menu, and select Excel Macro-Enabled Workbook, as per the illustration. For security reasons, new Excel files are created with this feature off by default, and if you want to run any VBA code you must have a Macro Enabled file. Now we need to create the template spreadsheet. Note that although this article refers to Excel 2013 the steps are the same for all versions. The only difference is that when Excel 2013 creates a new workbook by default your workbook will contain 1 Sheet. In older versions your new workbook will be created with 3 worksheets by default. The Developer tab contains all of the VBA and Macro functionality within Excel. We will need access to this and so we must now set this up. Just click the File tab, go to Options, and in the Excel Options window click the Customize Ribbon on the left side. You should now see the VBA Project Window. Now we need to create Modules, so we can place our codes within. In the popup menu choose Insert, Module. Create a total of 2 modules by repeating the action. Just double click on Module2 to make sure that this is your current window and in the much larger right-hand window paste the code which can be found at the end of this article which is called CreateTemplate. Once you have done this your window should look like this: This code makes sure that our worksheet is appropriately set up and formatted before we then move to run our graphing code. For example, as mentioned above it will remove or add worksheets if necessary, and format the worksheets. Next we need to insert the code which will actually create our chart. To do this Double click on Module1, and paste the code which you can find in Appendix 2 below. This is called CreateChart. To do this go to the previously discovered Developer tab, and in the left upper corner and click on the Macros option. In the pop-up window, you should the name of three macros: AddChart, CreateTemplate, ExportChartAsImage. Select the CreateTemplate macro, and hit the Run button. From here on you not need to touch any other VBA code. Simply enter the appropriate values to set the size, type and other parameters of the chart you would like to create. Once you have your values input, press the ConstructChart button and your chart will be automatically generated. If you would like to your Chart simply press the Export Chart button from the Settings sheet to export your chart as a Bitmap image. By default your chart image will be exported in the same location as your Excel file and so it would make sense to store it in its own folder so that you can easily find your exported charts. SECTION 2 — STEPPING THROUGH THE CODE In this section of the article we actually step through the code that creates the graphs explaining at a high level what it does and why. The code that you can see has a fair number of comments to help explain what it does as well on a line by line basis. Comments in code are sections of text that the computer does not run as it is marked as a comment. Comments are inserted into well written code as they make looking at and amending code written in the past much simpler by explaining what different sections of the code actually does. The VBA Code that creates and exports the chart is broken down into 7 sections as explained below: 1. This means that we tell Excel what we want to call the variables that our code will use and also, very importantly, what type of information our variable will be — so will it be numbers, text, dates etc etc. Objects are a more technical concept but refers to any of the constituent entities that make up Excel so Worksheets, charts, cell ranges and the like. This is a very important part of any program. Although to you as the builder of the program it might be obvious to enter a certain type of data into a certain field, it may not be to some of your users. You should never leave data types unchecked. If the user enters the wrong data type into an input box if may well mean that your program will fail to execute. So you must check the data before you process it. The code below simply checks that all of the variables set to be an Integer data type contain numbers. SET THE DECLARED VARIABLES VALUES Now that we have checked that the user has input the variables correctly we are ready to use these values. To do this we assign the various input values to the variables that we set up in step one. Activate this is the cell that the user needs to make a change in. WE WILL NOW SET UP THE SHEET WHERE THE CHART GETS CREATED Now that we are sure that all the data that we have is within the correct ranges and of the correct type and we have assigned it to variable so that we can use it within our code we are ready to get on and build our chart. Firstly we need to set up the worksheet where our chart will be created. This code ensures that the sheet where the chart is created is nicely formatted and so easy to read. NOW IS TIME TO WRITE TO CODE THAT OUTPUTS THE CHART AND ITS PROPERTIES Finally once the new sheet is set up and well formatted we can create our chart. First we add the chart with the ChartObjects. Add method and assign settings like distance from left leftDistance , distance from top topDistance , width chartWidth , and height chartHeight. The four property values will be replaced by the variables set in Section 2, whose values are set by the user. We then set the chart source data by using the SetSourceData property of the chart object. To allow the user to set the number of data bars we use a variable chartNumBars and concatenate the number passed through the variable within the Range declaration. CREATE THE PIECE OF CODE THAT WILL ALLOW THE USER TO EXPORT THE CHART AS A BITMAP IMAGE First we declare a variable imgPath as string and pass it the workbooks path along with the current time stamp to avoid name collisions in case if the user exports multiple images. We then use the Chart. Export method to export the newly created chart as a bitmap image. We pass the imgPath variable as the path to export the bitmap image to. Count, to be sure the loop executes if the workbook starts with one Application. Thanks again to Nick who works as a tutor on Excel VBA course for putting it together. Hello nickacuity, First off, I would like to say that this site has been an amazing learning experience for me. But I believe that some the cut and paste options are very unclear. Is there any way to fix this?? Hello all, I encountered the problem and found a quick way to resolve the issue. It should correct the problem and the code should appear green.
