How to find an object's type?

[prabhafriend]

I need to create function in such a way that it has to work for both excel ranges and recordsets.
The problem is how to find what kind of object is getting passed inside the function. Help needed

[Bob Phillips]

Public Function ObjectType(obj As Object)
    If TypeName(obj) = "Range" Then
    ObjectType = "Range"
    Else
    ObjectType = "RS"
    End If
End Function

[prabhafriend]

Thank you xld

[Bob Phillips]

More generic would be to do

Public Function ObjectType(obj As Object)
    ObjectType = TypeName(obj)
End Function

[prabhafriend]

Now i'm asking the reverse of it. I know the object-type using the typename( ) function. Now Inside the function I need to create a reportobject (either worksheet/recordset) according to input type. How to set the type of the vairable dynamically?

Function reconcile(statement1 as object, statement2 as object)
Dim reportobj as object
Set reportobj = typename(Statement) 'How to do this?
End function

[Bob Phillips]

Surely, it is nothing to do with its type per se, you just set the object variable to the object.

[prabhafriend]

But I want to create an entirely new object respectively to user input's object type. For example if the user is reconciling two excel sheets. I need to give the report in an excel sheet. Otherwise in a recordset. But entirely new Excelsheet or a recordset. How to create an object where its type is dynamic? Possible?

[Bob Phillips]

Your code is creating a generic object, so I cannot see the issue.

[Kenneth Hobs]

If you are setting an object, set it to an object, not a string value as returned by TypeName().

XLD already provided an example on how to check the typename using IF. Select is another route one could use if you have more than 2 or 3 cases.

e.g.

Sub t()
  Dim w As Object, wb As Object, ws As Object
  Set w = ThisWorkbook
  MsgBox TypeName(w)
Set w = ActiveSheet
  MsgBox TypeName(w)
Select Case TypeName(w)
    Case "Workbook":
      Set wb = w
    Case "Worksheet":
      Set ws = w
  End Select
MsgBox TypeName(wb), , "Workbook"
  MsgBox TypeName(ws), , "Worksheet"
End Sub

[prabhafriend]

But you are not getting my point. Suppose user is passing an filled worksheet/recordset. But I need a fresh worksheet/recordset to display my result. So I need to create an object(fresh) as the type of object the user inputs. How to do that?
I can do that set myresultobj = argobj
But how to make it fresh (like re-declared)? I believe you are getting it.

[Bob Phillips]

I think Kenneth is saying that you need to create a bunch of variables to cover all circumstances, and then assign the appropriate one once you have determined the object type.

But I ask again, what is the point? Why not just use an object data type and just use that with whatever object is assigned to it.

[prabhafriend]

What I want?
Is there a way to get that application.classname of an object?

Why I want?
I need an empty object of the type of object user deals with. If the user reconciles two recordsets I need a fresh recordset to display my result. If the user reconciles two worksheets then I need a fresh worksheet object.
I believe now the question understandable.

What's the advantage of the question I ask?
If I check the object type using typename and do a case else to create the respective object. I have to give the n number of cases for n number of object type available. Supppose if there is a way to get the application and its class name of an object. We can direct use it in the Createobject( ) function itself.

Where the hell I need to create an object for?
To display my results, I need a fresh recordset or a fresh worksheet. So I need a new object.

[Bob Phillips]

Nothing you have said makes me understand why you cannot use a generice object type. I think we are going round in circles here.

[Kenneth Hobs]

You are being too generic for us to help. Zip and attach simple XLS and MDB files if needed.

As XLD said, using the Object can be generic. CreateObject() gives you a late bound object. If you are wanting an early bound object so that intellisense works while programming, I would just do that at the programming stage and then comment it out and uncomment the late bound object lines for the production version.

In ADO programming, when you set the recordset object, if your SQL results in no records then your object would be empty. Of course any of your objects can be set to Nothing. e.g.

Sub t()
Dim w As Object
Set w = ThisWorkbook
MsgBox TypeName(w), , "ThisWorkbook"
Set w = Nothing
MsgBox TypeName(w), , "Was ThisWorkbook, now nothing."
Set w = ActiveSheet
MsgBox TypeName(w)
End Sub

Most programmers will set objects to Nothing at the end of their code to free memory. It not much of any issue these days.

[Paul_Hossler]

What I want?
Where the hell I need to create an object for?
To display my results, I need a fresh recordset or a fresh worksheet. So I need a new object.

If you want to display results on a fresh worksheet, then you need to .Add a new worksheet to the workbook.

Then you can Set the new worksheet to an object variable

Option Explicit
 
Sub drv()
Dim obj1 As Object, obj2 As Object, obj3 As Object
Set obj1 = ActiveWorkbook.Worksheets("sheet1")
Set obj2 = ActiveWorkbook.Worksheets("sheet2")
If TypeOf obj1 Is Worksheet Then
    ActiveWorkbook.Worksheets.Add
    ActiveSheet.Name = "FreshSheet"
    Set obj3 = ActiveWorkbook.Worksheets("FreshSheet")
    MsgBox TypeName(obj3)
    Else
    'do recordset stuff
End If
End Sub

Paul

[prabhafriend]

'Assume the scenario
inputobj as object            'User Sends a recordset
resultobj as object
set resultobj = inputobj     'Copying the object
set resultobj = Nothing      'Wont give a fresh recordset

I want a fresh recordset(Whatever the user inputs may be a recordset, may be a worksheet) What to do?

How to create a copy of an object(user's selection)? and making it as it has just been declared?

Like Set resultobj = Worksheet; Set resultobj = recordset;

[Bob Phillips]

As has been said many times.

Dim myObBj As Object
Set myObj = New ADODB.Recordset
    ' do something with myObj as a RecordSet
Set myObj = Activesheet
    ' dp something with myObj as a Worksheet

[prabhafriend]

Set myObj = New ADODB.Recordset

How to make the above statement dynamic? (I want to create a fresh object in the type of object the user inputs (dynamically))

[prabhafriend]

'I want like this

Dim userobj as object
Dim resultobj as object
set userobj = user'sobj
set resultobj = new (userobj.objecttype)

How to?