Set ws = wb.ActiveSheet = RunTime Error 13: Type mismatch??? - Possible File Corruption? 2

[kjv1611]

In my PERSONAL.XLSB file, I started trying to build some VBA. Because I began getting an error, I thought I'd setup a test procedure just to print the name of the active sheet.

The really really weird thing is that in previously built/saved procedures (right now, in testing) I can use the same functionality where I used it before, and just run it to see, and it seems to work) However in trying to build 2 new modules, the code fails with the error "Type mismatch".

Here's the testing code I built just for testing this error:
[CODE vba]Sub TestMeNow()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
[highlight #FCE94F]Set ws = wb.ActiveSheet[/highlight]
Debug.Print ws.Name
End Sub[/CODE]

That code gives me the error:

Run-time error '13': Type Mismatch

on the [highlight #FCE94F]highlighted[/highlight] line.

Could this be a form of file/data corruption? If so, what's the best way to recover from it? Or is there really an error that I'm overlooking, and just somehow my brain hasn't woken up well enough this morning?


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

The ActiveSheet is in the ActiveWorkbook by definition!

Sub TestMeNow()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    Debug.Print ws.Name
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Thanks. I thought that was the case, but I've always used that setup historically, since it was the way I first learned.

However, even the following code errors on the same line with the same error:

Sub TestJustTheSheet()
    Dim ws As Worksheet
    [highlight #FCE94F]Set ws = ActiveSheet[/highlight]
    Debug.Print ws.Name
End Sub

That's what has me thinking maybe it's some odd file corruption causing this. I've never had that error on such a simple section of code.

I guess I could try an Office repair and see if that fixes it?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Try...

Dim ws As Sheet

Just for grins.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

I thought that worked before as well, but when I tried just now, it's not working

I get this error:
Compile error:

User-defined type not defined

On this line:

Sub TestJustTheSheet()
    [highlight #FCE94F]Dim ws As Sheet[/highlight]
    Set ws = ActiveSheet
    Debug.Print ws.Name
End Sub

Thanks for the attempt, though.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

For the moment, I got to what I needed without VBA, but I'll eventually have to sort it out. If I have time after this Thursday, then I may try and Office Repair to see if that fixes it. And in the meantime, I'll just do my best to work around the issue if I come to needing that functionality. Today, I'm definitely going to focus om some other non-VBA areas for the remainder of the day, regardless.

Thanks in advance for any other suggestions in the meantime.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[combo]

There is no "Sheet" type in excel, only "Sheets" collection, so the error in the latest code.
wb.ActiveSheet returns object, it may be Chart if chart sheet exists in workbook and is active. In this case one gets "Type mismatch".

combo

 

[SkipVought]

I'm already loosing my edge!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

it may be Chart if chart sheet exists in workbook and is active. In this case one gets "Type mismatch".

Okay, so you're saying that if it's a chart sheet (such as could be the case with a pivot chart that is its own sheet? Yeah, that makes sense. So in that case I need to refer only to the chart object, and skip the whole worksheet object altogether?

I'll have to look into that if I can find time Friday. If not, then next week. Right now through tomorrow I won't likely have time to even look at it. Don't need to look at what I was looking at, since I found a workaround, but would be good practice to get back to it. I'll do my best to check and verify by next week. I'm afraid with some other tasks I have to knock out Friday as well, that I'll be tied up most if not all day that day.

Thanks a ton!

And Skip, I wouldn't say you've lost you're edge. You're just not yet used to being free and living on your own time/terms.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[combo]

Chart sheet is not a worksheet, however both are sheets. Other sheets: macro sheet, international macro sheet, dialog sheet etc.

If TypeOf ws Is Chart Then
MsgBox "chart"
ElseIf TypeOf ws Is Worksheet Then
MsgBox "worksheet"
Else
MsgBox "other"
End If


combo

 

[PHV]

Replace this:
Dim ws As Worksheet
with this:
Dim ws As Object

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks for the idea, combo. That sounds like something worth playing around with.

Thanks, PHV, for the suggestion on referring to the sheet as a general object. Probably worth considering.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57