CODEWITHSUNDEEP
excelruntime-errorvba
Rohit Saluja
Rohit Saluja

Reputation: 1517

Type of Run Time Error

The below code give me this error: Class does not support automation or does not support expected interface

Can anyone please explain Why am i getting this error

Sub typeofcheck()
Dim wks As New Worksheet

If TypeOf wks Is Worksheet Then
    MsgBox "This is Worksheet"
Else
    MsgBox "This is not"
End If
End Sub

Upvotes: 0

Views: 46

Answers (3)

Luboš Suk
Luboš Suk

Reputation: 1546

Not sure if can explain, but i think you can fix your code by initializating of variable wks. Something like this

Sub typeofcheck()
Dim wks As New Worksheet
Set wks = Sheets("someFunnySheetName")


If TypeOf wks Is Worksheet Then
    MsgBox "This is Worksheet"
Else
    MsgBox "This is not"
End If
End Sub

IMHO the issue will be that with DIM you only alocate some memory, and with Set you will set datatype. But VBA is a kind of mistery. Once i tried to read something in MSDN documentation, and it was worse than hell...

edit1: i made some further test, and this nice error msg which you mention sounds very pro, but if you debug code, and hover about wks variable, it tells you that wks = <Object variable or With block variable not set> which is much more easier to read. So to future, try to debug, and hover your variables. And if you dont know what to do, try to few Sets :) VBA is about magic

Upvotes: 0

Vegard
Vegard

Reputation: 4907

The error is caused by trying to access an interface that isn't available.

The correct way to do what you want is:

Sub typeofcheck()
Dim wks As Worksheet
Set wks = Worksheets.Add

Debug.Print TypeOf wks Is Worksheet
If TypeOf wks Is Worksheet Then
    MsgBox "This is Worksheet"
Else
    MsgBox "This is not"
End If
End Sub

Upvotes: 0

ssarabando
ssarabando

Reputation: 3517

The problem is in the As New.

AFAIK you can't create a new instance of a worksheet directly; you'll have to call the Add method in the Sheets collection for example:

Public Sub typeofcheck()
    Dim wks As Worksheet
    Set wks = ActiveWorkbook.Sheets.Add

    If TypeOf wks Is Worksheet Then
        MsgBox "This is Worksheet"
    Else
        MsgBox "This is not"
    End If
End Sub

Upvotes: 2

Related Questions