PTS Blog

Main menu:

PTS Utilities

Commercial Utilities developed by Peltier Technical Services

  Waterfall Chart

Excel Books

Books that I own and use while developing in Excel

Goods and Services

Excel or charting related products and services which I use or feel are worthwhile additions

Subscribe

Site search


Recent Posts

Recently Commented

October 2008
S M T W T F S
« Sep    
 1234
567891011
12131415161718
19202122232425
262728293031  

Archive


 

Categories


 

Archive for 'VBA'

Update Regular Chart when Pivot Table Updates

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

A reader named Julie has a regular chart that is linked to a pivot table. When the pivot table is pivoted or updated, it may have different numbers of rows and columns, and the chart must be updated manually. Julie asked whether a procedure could be developed to update the chart automatically.
I’ve done this in [...]

Peltier Goes Bar Hopping

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

There has been a lot of discussion about pie charts and bar charts lately. I and many other know-it-alls have clearly stated that pie charts are the red-headed stepchildren in the family of chart types. In Peltier Loves Pie I provided some guidelines to follow if you still insist on using pie charts.
In this post [...]

Dynamic Chart Source Data

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

A dynamic chart can be constructed using dynamic ranges that change in size as data is added or removed. This technique is very powerful: you can define flexible ranges limited only by your ability to write an appropriate Refers To formula.
One imitation of this approach is that it can only account for dynamic numbers of [...]

Get Open or Save-As Filename

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

GetOpenFilename and GetSaveAsFilename are useful dialogs to use within Excel VBA procedures when your procedure needs to know the filename of a file to open or save. I have encapsulated each into functions that add to their functionality, and make then simpler to use in my procedures.
Note that both GetXxxxFilename functions return file names. They [...]

File Name Functions

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

Recently I posted several VBA procedures for exporting charts and other purposes. These make use of a number of procedures that I use when manipulating file names. I inadvertently left these out of the posts, so I am including them here.
The following shows what the functions return when passed a file name and path of [...]

Enhanced Export Chart Procedure

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

In Export Chart as Image File I described the VBA command to export a chart as an image file, and I presented a simple routine that exported the active chart.
I said that one day I would enhance the procedure to use GetSaveAsFileName, which allows the user to browse to any folder to save the image [...]

Installing an Excel Add-In

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

A lot of Excel procedures are packaged in Add-Ins. An Add-In is basically a workbook containing some code, that has some other unique properties. The workbook is invisible. The workbook can be installed, which means it opens when Excel opens, so that its functionality is always available.
Installing an Add-In is really not complicated. If it [...]

Export Chart as Image File

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

Often people want to save their charts as an image file. Excel doesn’t offer this as a native feature in the UI, but you can export a chart using VBA:
ActiveChart.Export “C:\My Charts\SpecialChart.png”

This command allows you to export your chart in any of the bitmap formats that you have the appropriate export filters for. This [...]

Link Chart Text to a Cell

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

A very common question I am asked is, “How do I link text on a chart to text in a cell?” This is really not too difficult, but it’s also not intuitive. Except for axis tick labels, text elements in an Excel chart can be linked to worksheet cells. These text elements include the chart [...]

Connect Two XY Series with Arrows (VBA)

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

In Connect Two XY Series I showed how to use an extra XY series to connect two existing XY series in a chart. This shows how points in the first series may have changed to produce the data in the second series. A reader asked in a comment how to use arrows to more visually [...]

Create Excel dashboards quickly with Plug-N-Play reports.