Solved: activesheet.type always 3?

[TheAntiGates]

debug.print activesheet.type always gives 3, which means xlExcel4MacroSheet (the xlSheetType values are xlChart, xlDialogSheet, xlExcel4IntlMacroSheet, xlExcel4MacroSheet, xlWorksheet).

I'm trying to identify whether the current sheet is a regular "Sheet1" worksheet, as opposed to a "Chart1" chart (which you get from hitting the F11 key). What am I doing wrong, or should I do instead?

(P.S. - I thought that this was an unfortunate but logical outcome from being in the code window as I observed this (i.e., ?activesheet.type in the Immediate window). But it also gives 3 when running the macro "full speed" from alt-F8.)

[CreganTur]

Well... I may be missing something here but I bound a macro to Ctrl + Q that runs the code:
[VBA]Debug.Print ActiveSheet.Type[/VBA]

Whenever I'm on a chart it does indeed return '3'. When I am on a regular worksheet it returns '-4167'

So I'm getting two different values. Does this match what you're getting? Am I missing the point?

[TheAntiGates]

Those are exactly the results I'm getting. CORRECTION TO ORIGINAL POST: it gets result 3 on all CHARTS, and worksheets give -4167 (which is xlWorksheet).

It would seem that the correct result for charts should not be 3 but should be xlChart or -4109. I'll assume for now I'll assume that regular worksheets work as expected giving xlWorksheet; and that, like it or not, charts give xlExcel4MacroSheet (3) instead of xlChart. But this is still a mystery to me.

[CreganTur]

Well... if that's the result you want you can always use:

[VBA]Dim result

result = ActiveSheet.Type
Select Case result
Case 3
Debug.Print "xlChart"
Case -4167
Debug.Print "xlWorksheet"
End Select[/VBA]

[Bob Phillips]

This is a known bug. Typename is more reliable

[vba]

Select Case TypeName(ActiveSheet)

Case "Chart": MsgBox "Chartsheet"

Case "Worksheet": MsgBox "Worksheet"
End Select
[/vba]

[TheAntiGates]

Thanks Randy - that's what I've resigned to, excepting further explanation. I still prefer to used the named constants for classic reasons; in this case it means coding xlExcel4MacroSheet which seems absurd.

Either there's something else to this or maybe it's simply a bug.

[TheAntiGates]

Okay xld, just seeing your reply. FYI this is XL 2003. On further research, I've seen others suggest to use Typename. I also saw another way at http://tinyurl.com/54ya6v but it is very awkward, doing

For Each oChart In Charts
If oChart.Name = Sheets(iCount).Name

which I assume is unnecessary if you just use TypeName. Way to go, remembering that and sharing it!

[Bob Phillips]

That does seem a tad convoluted doesn't it. I'll stick to Typename :-)

[TheAntiGates]

The following shows TypeOf to give superior speed over typename on my machine. I ran this once with a chart as activesheet and once without

Sub Test_typename_speed_vs_typeof()
Dim tStart As Single, i As Long, bBool As Boolean
    tStart = Timer()
    For i = 1 To 5000000#: bBool = TypeName$(ActiveSheet) = "Chart": Next
    Debug.Print "typename " & Timer() - tStart
    
    tStart = Timer()
    For i = 1 To 5000000#: bBool = TypeName$(ActiveSheet) = "Worksheet": Next
    Debug.Print "typename2 " & Timer() - tStart
    
    tStart = Timer()
    For i = 1 To 5000000#: bBool = TypeOf ActiveSheet Is Chart: Next
    Debug.Print "typeof " & Timer() - tStart
    
    tStart = Timer()
    For i = 1 To 5000000#: bBool = TypeOf ActiveSheet Is Worksheet: Next
    Debug.Print "typeof2 " & Timer() - tStart
End Sub