Re: Rephrasing argument for typename and a different result

From: Hari (excel_hari_at_hotmail.com)
Date: 06/15/04 

Date: Tue, 15 Jun 2004 21:44:20 +0530

Hi Troy,

Thanx a ton.

I am able to get what I wanted with ur method. I have used ".value" at the
end.

Thanx again

Regards,
Hari
India

"TroyW" <nospam@yahoo.com> wrote in message
news:10ctlg58i44l2bc@corp.supernews.com...
> Hari,
>
> The TypeName function is used to inspect objects and variables and return
> the data type. Take a look at the VBA Help.
>
> CellValue ==> TypeName
> hello ==> String
> 1234 ==> Double
> 01/01/2004 ==> Date
> (blank) ==> Empty
>
>
> In your snippet of code, it appears that the INTENT of the code logic is
to
> inspect the CONTENTS of the cell(n,2) and determine what type of VALUE
is
> in the cell. But, the way your code is currently written, the TypeName
> function can only return a value of "Range" no matter what is entered into
> the cell (text, numbers, dates, etc.). This is not a syntax error which
> results in the program stopping, but it is a logic error that won't return
> the desired result.
>
> In order to inspect the contents of the cell correctly you must write the
> code as follows (Note the addition of ".Value" to the end of the
statement):
>
> dates = Sheets(sheetName).Cells(n, 2).Value
> If TypeName(dates) <> "Date" Then
>
> -or-
>
> If TypeName(Sheets(sheetName).Cells(n,2).Value
>
> Please change the code to what I'm suggesting and tell me if you still
have
> errors. Or, help me to understand what the intent or purpose of the code
is.
>
> Troy
>
> P.S. Excel has various object types (workbook, work***, range, chart,
> etc.). You can use TypeName to inspect those objects also and return what
> type they are. Your current code is asking the question what object type
is
> a cell. In Excel, a cell has a type "Range". A "cell" and a "cell value"
are
> two different things.
>
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:OZbIlSoUEHA.2524@TK2MSFTNGP12.phx.gbl...
> > Hi Troy,
> >
> > This is strange for me.
> >
> > What datatype is range ( Im a newbie...)
> >
> > Also, my original question remains. Why does excel be able to work
> correctly
> > when we write
> >
> > dates = Sheets(sheetName).Cells(n, 2)
> > If TypeName(dates) <> "Date" Then
> >
> >
> > But if the statement is written as
> > If TypeName((Sheets(sheetName).Cells(n, 2)) <> "Date" Then
> >
> > the result after running is different in the above 2 cases as originally
> > stated
> >
> > Regards,
> > Hari
> > India
> >
> >
> > "TroyW" <nospam@yahoo.com> wrote in message
> > news:10cskggjjrqh12a@corp.supernews.com...
> > > Hari,
> > >
> > > Looking closer, I don't think either statement is correct. My testing
> > > indicates that you need to qualify that you want the "Value" of the
> cell,
> > > otherwise TypeName will always return a "Range" type. Try testing
> > different
> > > values of the cell with the code below.
> > >
> > > sType1 changes depending upon what value is in the cell. sType2 is
> always
> > > "Range", independent of what the cell value is.
> > >
> > > I think you want to use:
> > > TypeName(Sheets(sheetName).Cells(n, 2).Value)
> > >
> > > Troy
> > >
> > >
> > > Sub TypeName_Example()
> > > Dim sType1 As String
> > > Dim sType2 As String
> > >
> > > '''n and sheetName need to be defined.
> > >
> > > sType1 = TypeName(Sheets(sheetName).Cells(n, 2).Value)
> > > sType2 = TypeName(Sheets(sheetName).Cells(n, 2))
> > > MsgBox "Type1 = " & sType1 & vbCr & _
> > > "Type2 = " & sType2
> > >
> > > End Sub
> > >
> > >
> > > "Hari" <excel_hari@hotmail.com> wrote in message
> > > news:u2vmawmUEHA.2940@TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > >
> > > > I have the following code which executes perfectly.
> > > >
> > > > dates = Sheets(sheetName).Cells(n, 2)
> > > > If TypeName(dates) <> "Date" Then
> > > > Sheets(sheetName).Cells(n, 2).Interior.ColorIndex = 6
> > > > NoOfErrors = NoOfErrors + 1
> > > > Else
> > > >
> > > >
> > > >
> > > > If I rewrite the above code as given below and run it ( without
> changing
> > > any
> > > > data in the work***) then it highlights an error in the cell which
> has
> > > > date only. How is the 2 codes different from each other?
> > > >
> > > >
> > > > If TypeName((Sheets(sheetName).Cells(n, 2)) <> "Date" Then
> > > > Sheets(sheetName).Cells(n, 2).Interior.ColorIndex = 6
> > > > NoOfErrors = NoOfErrors + 1
> > > > Else
> > > >
> > > > Regards,
> > > > Hari
> > > > India
> > > >
> > > >
> > >
> > >
> >
> >
>
>