Microsoft Excel is a popular spreadsheet program that also creates charts and graphs of all varieties. Part of Microsoft Office, Excel makes viewing and analysing data quick and easy. However, if you need to create a large number of charts, doing so by hand may be time-consuming and error-prone. Instead, consider using Microsoft Visual Basic .NET, Microsoft's new and improved Visual Basic programming language, to automate the task. Its built-in Excel integration makes this a breeze.
- Skill level:
- Moderately Challenging
Things you need
- Microsoft Excel (Developer or Professional edition)
- Microsoft Visual Basic .NET
Open Visual Basic .NET and create a new project.
Right-click your project and choose Add Reference. Go to the COM tab and click Microsoft Excel Object Library. Click OK.
Add code in your project's Main function to create a new instance of the Excel application. It should look like this:
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
Add code to create a new spreadsheet. For example:
Dim wb As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Add()
Add code to retrieve the primary worksheet from the spreadsheet. For example:
Dim sheet As Microsoft.Office.Interop.Excel.Worksheet = wb.ActiveSheet
Add data to the spreadsheet. For example, to set the value in the upper-leftmost cell of the spreadsheet (cell A1) to 5, cell A2 to 8, and A3 to 4, write the following code:
sheet.Cells(1, 1).Value = 5 sheet.Cells(1, 2).Value = 8 sheet.Cells(1, 3).Value = 4
Do this for all the data you'd like to use in your chart.
Add code to create the chart:
Dim chart As Microsoft.Office.Interop.Excel.Chart = sheet.Parent.Charts.Add() chart.ChartWizard(sheet.Range("A1:A3"), Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, , Excel.XlRowCol.xlColumns)
Add code to set up the data series for the chart:
Dim series As Microsoft.Office.Interop.Excel.Series = chart.SeriesCollection(1) series.XValues = sheet.Range("A1", "A3")
Your chart is now complete. You can customise it further using the functions provided by the Microsoft Developers Network's Office Developer Center at http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.chart(office.11).aspx>
Tips and warnings
- If you need to make your Visual Basic macro more flexible, try replacing some of the strings and numbers in the code with variables. This way, you can use other code in the macro to calculate or look up the values needed for those variables, which means your macro can handle a wider range of possibilities.
- Always save a backup of your spreadsheet before writing macros. Because the macro functionality is so powerful, it also has the ability to delete or corrupt a lot of data at once. By backing up your spreadsheet, you'll be able to test your macros without fear of losing any work.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for