figuring return type of a field

[Nasir.Munir]

I have posted this on excel, didn't get any help. Thought I should try
here.

My goal is to find the return type of a field. I have created a
customized right click menu. A user can right click and then choose
"conversion" option to convert the cell value, either from hours to
decimal or from decimal to hours. It is working fine.
The problem is: the program should be able to find out the difference
between hours and decimal value. To my understanding, the program
takes the value as "double" irrespective of the value in hours or
decimals.
Can I get some help here in figuring out the hour type ? I want to
display the alert message if the user try to convert hours to hour or
decimal to decimal. I can only do that if I can find out the cell
value in hours and decimals independently.
Thanks in advance,

 

[Chip Pearson]

Try a function like


Function IsTimeValue(Rng As Range) As Boolean
IsTimeValue = (InStr(1, Rng.Text, ":") > 0)
End Function

Then call this with

If IsTimeValue(ActiveCell) Then
' convert time to decimal
Else
' else
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

If instr(1,activecell.Text,":",vbTextcompare) then
' formatted in hours?
elseif Instr(1,activecell.text,".",vbTextcompare) then
' formatted in decimal?
else

end if

without knowing more about what you do when you convert and what you mean by
hours and decimal, it would be hard to tell what is the best approach.

By hours, you could mean a whole number. I assumed you meant stored as a
time serial number and formatted as time. In lieu of the hardcoded
Comma/colon, you might want to use the internation function as demo'd from
the immediate window (I have US English settings).

? application.International(xlDecimalSeparator)
..
? application.International(xlTimeSeparator)
:

 

[Nasir.Munir]

Perfect, thanks a lot
Nasir.

Try a function like

Function IsTimeValue(Rng As Range) As Boolean
IsTimeValue = (InStr(1, Rng.Text, ":") > 0)
End Function

Then call this with

If IsTimeValue(ActiveCell) Then
' convert time to decimal
Else
' else
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLCwww.cpearson.com
(email address is on the web site)

 

[Nasir.Munir]

thanks a lot for the help.
It may be helpful for future if I could re-state my problem and
solution again.
The solution provided by Chip Pearson works the way I wanted. I havent
tried Tom's solution, but I am sure it will work too.
Problem:
I have this spread sheet which deals with certain values in hours:min
and some in decimal. I have written some code which uses the values
from the respective fields and do some calculations. The problem was:
the code takes the value as "double" disregarding orignal value in
hours or decimal. That is fine, but when I wrote some code for
customizing the right click menu, there was no way I could alert the
user if the user wrongly selects hours value and try to convert that
into hours, and same goes with decimal to decimal.
I wanted a way to find out the return type of the cell, and then use
if-else block to do the calculation. For example if the value is
"175:12" then if a user tries to use the convert button to get value
in hours, an alert message will tell him that the value is already in
hours, use the other conversion ie hours to decimal.
I hope this will help.
Regards and thanks again,
Nasir.

 

[Guest]

My solution is essentially the same as Chips, so yes, it should work.