Excel 2007 VBA - Using Sheets.Add to create a chart at the end of a workbook

Question

I'm trying to implement a private subroutine to add a Sheet to the active workbook (Sheet being used so that one can choose to add xlForms, xlWorksheet, etc).

But I'm having a bizarre problem with where new charts get created relative to the other sheets in the workbook.

I start off by deleting the sheet of the same name (if it exists) and then use the following code:

ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.count()), _
Type:=sheet_type).Name = sheet_name

Where sheet_type is an optional parameter of the enum XlSheetType, and sheet_name is a string.

It works completely fine for every available argument such as xlWorksheet, xlDialogSheet, and even the xl4MacroSheet -- but for some reason will create the xlChart 1 position before the end, as opposed to the making it the last sheet in the workbook.

So sample I/O (starting off with only 3 sheets):

> Create_Sheet "Test", sheet_type:=xlWorksheet
Sheet 1 | Sheet 2 | Sheet 3 | Test

> Create_Sheet "Test", sheet_type:=xlDialogSheet
Sheet 1 | Sheet 2 | Sheet 3 | Test

> Create_Sheet "Test", sheet_type:=xlChart
Sheet 1 | Sheet 2 | Test | Sheet 3 

Sheets.count() properly returns 3 in the previous examples (because I start with only Sheets 1, 2, and 3), and so it should hypothetically position it after the 3rd sheet, but it doesn't. Attempting to do Sheets.count() + 1 for testing only gives me an array subscript out of range runtime exception (which is to be expected).

I've even tested just the base code of:

ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.count()), _ 
Type:=xlChart).Name = "Test"

and I still wind up getting the same result.

So basically my question is this: am I doing something wrong here? Is there any specific reason as to why a chart can't be added to the end of the Workbook? Or is this perhaps a problem/bug in VB that needs to be fixed?

I'm using Office 2007, so alternatively is it an error that has been fixed in newer versions?

Any input would help.

EDIT: It's worth noting that After:=Sheets(Sheets.count()) produces the same location as After:=Sheets(Sheets.count() - 1), but only in the case that Type:=xlChart

EDIT 2 This is even more interesting. If you create one Chart, place it as the last sheet in the workbook, and use the following code:

  ActiveWorkbook.Charts.Add After:=Charts(1)

Excel will automatically re-orient the charts so that a Worksheet is last.

For example, if you have:

Sheet 1 | Sheet 2 | Sheet 3 | Chart 1

and use the code, you will get

Sheet 1 | Sheet 2 | Chart 1 | Chart 2 | Sheet 3

Answer 1

Ok, I am not sure if this will help you, but yes, it has the same odd behavior here, and could not find any "clean" way to make the sheet last, so my solution was that:

ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count()), _
Type:=xlChart).Name = sheet_name
Sheets(sheet_name).Move After:=Sheets(Sheets.Count())