Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
figuring return type of a field
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
figuring return type of a field
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) wrote in message ups.com... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
figuring return type of a field
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) : -- Regards, Tom Ogilvy " wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
figuring return type of a field
Perfect, thanks a lot
Nasir. On Jan 30, 11:33 am, "Chip Pearson" wrote: 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) wrote in oglegroups.com... 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,- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
figuring return type of a field
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. On Jan 30, 11:40 am, Tom Ogilvy wrote: 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) : -- Regards, Tom Ogilvy " wrote: 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,- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
figuring return type of a field
My solution is essentially the same as Chips, so yes, it should work.
-- regards, Tom Ogilvy " wrote: 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. On Jan 30, 11:40 am, Tom Ogilvy wrote: 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) : -- Regards, Tom Ogilvy " wrote: 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,- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting field size and type | Excel Discussion (Misc queries) | |||
Field Validation for Length & Character Type | Excel Discussion (Misc queries) | |||
ADO get data from Oracle by SQL field type missmatch | Excel Programming | |||
Every time i type a date in a field it turns it into ###### | New Users to Excel | |||
Determine data type for Pivot Field | Excel Programming |