Rephrasing argument for typename and a different result

H

Hari

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 worksheet) 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
 
T

TroyW

Hari,

Did you compile the code and not get any warnings? Looks like the following
line has one too many left parentheses.
If TypeName((Sheets(sheetName).Cells(n, 2)) <> "Date" Then

How about:
If TypeName(Sheets(sheetName).Cells(n, 2)) <> "Date" Then

Troy
 
H

Hari

Hi Troy,

Im sorry, I added a parentheses by mistake when posting the message to the
group.

The original problem still persists.

Regards,
Hari
India
 
T

TroyW

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
 
H

Hari

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
 
T

TroyW

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, worksheet, 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.
 
H

Hari

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
 
Top