MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Function "Type" does not work properly


Posted by Al on July 05, 1999 10:41 AM

The "Type" function does not return 8 but 1, even in the reference cell I have a formula and not a number. As I know Microsoft has only acknowledged the bug but they do not have any solution. Do you know a workaround. I tried to create a conditional formula like; if in cell A1 there is the character =, return "formula", if not other, but it does not work. I cannot find a way Excel to recognize = sign. Any suggestions? Thank you.


Posted by Ivan Moala on July 06, 1999 5:52 AM

Al copy & paste the following user function
to a VBA module.
Invoke it by typing in any cell
TypeF("mycell") where mycell = the cell you
want to get the type value.

Function TypeF(MyRange As Range) As Integer

If MyRange.HasFormula Then
TypeF = 8
ElseIf MyRange.HasArray Then
TypeF = 64
ElseIf WorksheetFunction.IsNumber(MyRange.Value) Then
TypeF = 1
ElseIf WorksheetFunction.IsError(MyRange.Value) Then
TypeF = 16
ElseIf WorksheetFunction.IsLogical(MyRange.Value) Then
TypeF = 4
Else
TypeF = 2
End If

regards

Ivan
End Function

Posted by Ivan Moala on July 06, 1999 6:10 AM

Re: Function

Sorry Al the End Fuction should be after End If