Excel

Function to check for Chart sheet existence

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Zack Barresse

Description:

This function will return either True or False depending if the name specified belongs to a Chart sheet name or another type of sheet name. Can be used in a VBA routine or as a standard worksheet function. 

Discussion:

When running code, sometimes you have the need to deal with Chart sheets. These inherently have different properties associated with them. They can be a little more tricky and involved. Usually it's good to know right away if you are dealing with a chart (for things like looping through all sheets in a workbook). This User Defined Function will give you that ability to test any sheet name and see if it is a Chart sheet or a regular worksheet. 

Code:

instructions for use

			

Option Explicit Public Function IsChart(cName As String) As Boolean Dim tmpChart As Chart On Error Resume Next Set tmpChart = Charts(cName) On Error Goto 0 IsChart = IIf(tmpChart Is Nothing, False, True) End Function

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the Visual Basic Editor (VBE).
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
  9. Call Function as either 1) a worksheet function, or 2) within a VBA routine.
 

Test the code:

  1. From an existing workbook, save first.
  2. Select an empty cell.
  3. Type =IsChart("Chart1")
  4. A FALSE return will be given for everything except a Chart sheet.
 

Sample File:

IsChartEx.zip 9.04KB 

Approved by mdmackillop


This entry has been viewed 160 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express