WHat do I use with IsObject to determine the object type 3

[WantsToLearn]

I am trying to write some generic functions to encapsulate various Excel features I use many times in VBA code. Some things in Excel work on ranges, worksheets, etc so I usually define the argument as Variant and then use IsObject to verify that I got an object.

Sometimes I want to know whether I am dealing with a range or with a worksheet. With scalar variables you can check the variable type with VarType. How do I do the equivalent with objects? Is there an enumeration somewhere I can use?

Thanks!

 

[sfvb]

I'm not sure if this is what your looking for:

Sub FindObjects()
   Dim OleObj As OLEObject

   For Each OleObj In Sheet1.OLEObjects
      MsgBox OleObj.Name & " is a " & Split(OleObj.ProgId, ".")(1)
   Next
End Sub[code][/color]

 

[TonyJollans]

Hi WantsToLearn,

Check out the TypeName function.

Enjoy,
Tony

 

[WantsToLearn]

Thanks to both of you, but that is not quite what I am looking for. How can I test to determine whether my function's argument was a range or worksheet?

Thanks!

 

[sfvb]

WantsToLearn,

Tony gave you the answer. Did you look it up? It gets a star from me.

 

[ordendelfai]

Star from me, didn't know about that function

 

[TonyJollans]

Hi ordendelfai,

It's good to get feedback which shows that the site is more than just a question-and-answer forum; it's a real information repository.

Thanks for the star.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[WantsToLearn]

If anyone is interested, here are the generic functions I wrote using TonyJollans answer above. Sorry, I forgot to post them and I just ran across this thread again the other day.

Function IsApplication(pvar As Variant) As Boolean
    IsApplication = (TypeName(pvar) = "Application")
End Function

Function IsWorkbook(pvar As Variant) As Boolean
    IsWorkbook = (TypeName(pvar) = "Workbook")
End Function

Function IsWorksheet(pvar As Variant) As Boolean
    IsWorksheet = (TypeName(pvar) = "Worksheet")
End Function

Function IsRange(pvar As Variant) As Boolean
    IsRange = (TypeName(pvar) = "Range")
End Function