Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
I'm working on a macro where an Application.Inputbox is used to have the
user enter a numerical value. When this variable is declared as Double, everything works as expected. However, for the error-checking code for the InputBox, it appears I need to use Variant as the value of this variable (if the input is not entered properly) will not be a numerical value in all cases. Multiple tests with varused as Variant return an incorrect calculation. Now, my question is as follows: is there a way to enter varused as a Variant, but to convert it to a Double after I performed my error-check for the Application.InputBox? I've included what I believe is all applicable code, in the hopes it better illustrates my problem. Any insight into why declaring varused As Variant causes my code to malfunction would be an added bonues. ;-) Thanks in advance for any assistance. k -- Dim LoadPerString As Double varused = Application.InputBox("Enter the value.") -- I was looking into some code to protect the macro in case the user entered nothing & clicked OK or clicked Cancel when the InputBox appeared. The code I'd like to use is as follows. -- Dim varused As Variant If varused = False Then MsgBox "You clicked cancel." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub ElseIf varused = "" Then MsgBox "You didn't enter anything." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub Else MsgBox "You entered: " & varused End If -- I got this code from the following post to this group. http://groups.google.com/groups?hl=e...sftngp05#link7 The calculations I am performing (not surprisingly, they originated in this group too) are as follows. -- Dim hi As Long, lo As Long Dim XRates As Range, YRates As Range Dim CountCells As Long ' combining ranges for interpolate function Set XRates = Range(startxrange & ":" & endxrange) Set YRates = Range(startyrange & ":" & endyrange) CountCells = XRates.Cells.Count If CountCells < YRates.Count Then MsgBox "The ranges need to be the same size." & vbCrLf & "This program will self-destruct." Exit Sub End If For hi = 1 To CountCells If XRates(hi) < LoadPerString Then Exit For Next If hi CountCells Then DesignTime = YRates(CountCells) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If If hi = 1 Then DesignTime = YRates(hi) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If lo = hi - 1 DesignTime = YRates(lo) + (varused - XRates(lo)) / _ (XRates(hi) - XRates(lo)) * _ (YRates(hi) - YRates(lo)) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
This is how I do it:
Code ------------------- Dim strTemp as String Dim varused as double strTemp = Application.InputBox("Enter the value.") If Not IsNumeric(strTemp) Then 'Message non numeric and exit End If If Trim(strTemp) = "" Then 'Message blank and exit End If varused = CDbl(strTemp ------------------- -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
Hi
you may use CDbl for this -- Regards Frank Kabel Frankfurt, Germany k wrote: I'm working on a macro where an Application.Inputbox is used to have the user enter a numerical value. When this variable is declared as Double, everything works as expected. However, for the error-checking code for the InputBox, it appears I need to use Variant as the value of this variable (if the input is not entered properly) will not be a numerical value in all cases. Multiple tests with varused as Variant return an incorrect calculation. Now, my question is as follows: is there a way to enter varused as a Variant, but to convert it to a Double after I performed my error-check for the Application.InputBox? I've included what I believe is all applicable code, in the hopes it better illustrates my problem. Any insight into why declaring varused As Variant causes my code to malfunction would be an added bonues. ;-) Thanks in advance for any assistance. k -- Dim LoadPerString As Double varused = Application.InputBox("Enter the value.") -- I was looking into some code to protect the macro in case the user entered nothing & clicked OK or clicked Cancel when the InputBox appeared. The code I'd like to use is as follows. -- Dim varused As Variant If varused = False Then MsgBox "You clicked cancel." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub ElseIf varused = "" Then MsgBox "You didn't enter anything." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub Else MsgBox "You entered: " & varused End If -- I got this code from the following post to this group. http://groups.google.com/groups?hl=e...-8&frame=right &th=5fa1cec038e2d834&seekm=OQcrrWjsBHA.1452%40tkms ftngp05#link7 The calculations I am performing (not surprisingly, they originated in this group too) are as follows. -- Dim hi As Long, lo As Long Dim XRates As Range, YRates As Range Dim CountCells As Long ' combining ranges for interpolate function Set XRates = Range(startxrange & ":" & endxrange) Set YRates = Range(startyrange & ":" & endyrange) CountCells = XRates.Cells.Count If CountCells < YRates.Count Then MsgBox "The ranges need to be the same size." & vbCrLf & "This program will self-destruct." Exit Sub End If For hi = 1 To CountCells If XRates(hi) < LoadPerString Then Exit For Next If hi CountCells Then DesignTime = YRates(CountCells) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If If hi = 1 Then DesignTime = YRates(hi) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If lo = hi - 1 DesignTime = YRates(lo) + (varused - XRates(lo)) / _ (XRates(hi) - XRates(lo)) * _ (YRates(hi) - YRates(lo)) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
k wrote:
I'm working on a macro where an Application.Inputbox is used to have the user enter a numerical value. When this variable is declared as Double, everything works as expected. However, for the error-checking code for the InputBox, it appears I need to use Variant as the value of this variable (if the input is not entered properly) will not be a numerical value in all cases. Multiple tests with varused as Variant return an incorrect calculation. Now, my question is as follows: is there a way to enter varused as a Variant, but to convert it to a Double after I performed my error-check for the Application.InputBox? I've included what I believe is all applicable code, in the hopes it better illustrates my problem. Any insight into why declaring varused As Variant causes my code to malfunction would be an added bonues. ;-) Thanks in advance for any assistance. k -- Have you tried using the CDbl() function ? MyDouble = CDbl(MyVar) -- Regards, Juan Pablo González |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
You can set a variable of type double to this variant variable, but I would
not have expected you to need it. IF a numeric value is input, it should work in the calculation just as well as a variant type. What exactly is going wrong in the calculations? Have you stepped through it in debug mode? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "k" wrote in message ... I'm working on a macro where an Application.Inputbox is used to have the user enter a numerical value. When this variable is declared as Double, everything works as expected. However, for the error-checking code for the InputBox, it appears I need to use Variant as the value of this variable (if the input is not entered properly) will not be a numerical value in all cases. Multiple tests with varused as Variant return an incorrect calculation. Now, my question is as follows: is there a way to enter varused as a Variant, but to convert it to a Double after I performed my error-check for the Application.InputBox? I've included what I believe is all applicable code, in the hopes it better illustrates my problem. Any insight into why declaring varused As Variant causes my code to malfunction would be an added bonues. ;-) Thanks in advance for any assistance. k -- Dim LoadPerString As Double varused = Application.InputBox("Enter the value.") -- I was looking into some code to protect the macro in case the user entered nothing & clicked OK or clicked Cancel when the InputBox appeared. The code I'd like to use is as follows. -- Dim varused As Variant If varused = False Then MsgBox "You clicked cancel." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub ElseIf varused = "" Then MsgBox "You didn't enter anything." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub Else MsgBox "You entered: " & varused End If -- I got this code from the following post to this group. http://groups.google.com/groups?hl=e...sftngp05#link7 The calculations I am performing (not surprisingly, they originated in this group too) are as follows. -- Dim hi As Long, lo As Long Dim XRates As Range, YRates As Range Dim CountCells As Long ' combining ranges for interpolate function Set XRates = Range(startxrange & ":" & endxrange) Set YRates = Range(startyrange & ":" & endyrange) CountCells = XRates.Cells.Count If CountCells < YRates.Count Then MsgBox "The ranges need to be the same size." & vbCrLf & "This program will self-destruct." Exit Sub End If For hi = 1 To CountCells If XRates(hi) < LoadPerString Then Exit For Next If hi CountCells Then DesignTime = YRates(CountCells) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If If hi = 1 Then DesignTime = YRates(hi) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If lo = hi - 1 DesignTime = YRates(lo) + (varused - XRates(lo)) / _ (XRates(hi) - XRates(lo)) * _ (YRates(hi) - YRates(lo)) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
Look in the VBA help also
You can use Type 1 if you only want that the user can enter numeric values -- Regards Ron de Bruin http://www.rondebruin.nl "Frank Kabel" wrote in message ... Hi you may use CDbl for this -- Regards Frank Kabel Frankfurt, Germany k wrote: I'm working on a macro where an Application.Inputbox is used to have the user enter a numerical value. When this variable is declared as Double, everything works as expected. However, for the error-checking code for the InputBox, it appears I need to use Variant as the value of this variable (if the input is not entered properly) will not be a numerical value in all cases. Multiple tests with varused as Variant return an incorrect calculation. Now, my question is as follows: is there a way to enter varused as a Variant, but to convert it to a Double after I performed my error-check for the Application.InputBox? I've included what I believe is all applicable code, in the hopes it better illustrates my problem. Any insight into why declaring varused As Variant causes my code to malfunction would be an added bonues. ;-) Thanks in advance for any assistance. k -- Dim LoadPerString As Double varused = Application.InputBox("Enter the value.") -- I was looking into some code to protect the macro in case the user entered nothing & clicked OK or clicked Cancel when the InputBox appeared. The code I'd like to use is as follows. -- Dim varused As Variant If varused = False Then MsgBox "You clicked cancel." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub ElseIf varused = "" Then MsgBox "You didn't enter anything." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub Else MsgBox "You entered: " & varused End If -- I got this code from the following post to this group. http://groups.google.com/groups?hl=e...-8&frame=right &th=5fa1cec038e2d834&seekm=OQcrrWjsBHA.1452%40tkms ftngp05#link7 The calculations I am performing (not surprisingly, they originated in this group too) are as follows. -- Dim hi As Long, lo As Long Dim XRates As Range, YRates As Range Dim CountCells As Long ' combining ranges for interpolate function Set XRates = Range(startxrange & ":" & endxrange) Set YRates = Range(startyrange & ":" & endyrange) CountCells = XRates.Cells.Count If CountCells < YRates.Count Then MsgBox "The ranges need to be the same size." & vbCrLf & "This program will self-destruct." Exit Sub End If For hi = 1 To CountCells If XRates(hi) < LoadPerString Then Exit For Next If hi CountCells Then DesignTime = YRates(CountCells) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If If hi = 1 Then DesignTime = YRates(hi) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If lo = hi - 1 DesignTime = YRates(lo) + (varused - XRates(lo)) / _ (XRates(hi) - XRates(lo)) * _ (YRates(hi) - YRates(lo)) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
Try Rons suggestion
Dim varused As Variant varused = Application.InputBox("Enter the value.", Type:=1) '// User cancelled If varused = False Then End MsgBox varused You should when ever possible try an intercept know return values as early as possible, using the Type:=1 forces the input to accept numbers only ie you have intercepted / masked out any Non numerics out earlier on and don't need to test for this. kkknie wrote in message ... This is how I do it: Code: -------------------- Dim strTemp as String Dim varused as double strTemp = Application.InputBox("Enter the value.") If Not IsNumeric(strTemp) Then 'Message non numeric and exit End If If Trim(strTemp) = "" Then 'Message blank and exit End If varused = CDbl(strTemp) -------------------- K --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring As Variant, Changing to Double?
Thanks for the suggestion. I wasn't aware of the CDbl function & will have to give it a try. As for Bob's question, I haven't yet been able to determine where in my code the problem is occurring when declaring varused as a Variant. I will have to go through & determine where this is occurring. I will try to post a follow-up on this, but as I've got two more pressing projects on my plate at this point, it may not be for a week or so. Based on my limited knowledge of variant, it wasn't obvious to me that this would cause a problem. Thanks for all the assistance. k "Bob Phillips" wrote in message ... You can set a variable of type double to this variant variable, but I would not have expected you to need it. IF a numeric value is input, it should work in the calculation just as well as a variant type. What exactly is going wrong in the calculations? Have you stepped through it in debug mode? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "k" wrote in message ... I'm working on a macro where an Application.Inputbox is used to have the user enter a numerical value. When this variable is declared as Double, everything works as expected. However, for the error-checking code for the InputBox, it appears I need to use Variant as the value of this variable (if the input is not entered properly) will not be a numerical value in all cases. Multiple tests with varused as Variant return an incorrect calculation. Now, my question is as follows: is there a way to enter varused as a Variant, but to convert it to a Double after I performed my error-check for the Application.InputBox? I've included what I believe is all applicable code, in the hopes it better illustrates my problem. Any insight into why declaring varused As Variant causes my code to malfunction would be an added bonues. ;-) Thanks in advance for any assistance. k -- Dim LoadPerString As Double varused = Application.InputBox("Enter the value.") -- I was looking into some code to protect the macro in case the user entered nothing & clicked OK or clicked Cancel when the InputBox appeared. The code I'd like to use is as follows. -- Dim varused As Variant If varused = False Then MsgBox "You clicked cancel." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub ElseIf varused = "" Then MsgBox "You didn't enter anything." Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub Else MsgBox "You entered: " & varused End If -- I got this code from the following post to this group. http://groups.google.com/groups?hl=e...sftngp05#link7 The calculations I am performing (not surprisingly, they originated in this group too) are as follows. -- Dim hi As Long, lo As Long Dim XRates As Range, YRates As Range Dim CountCells As Long ' combining ranges for interpolate function Set XRates = Range(startxrange & ":" & endxrange) Set YRates = Range(startyrange & ":" & endyrange) CountCells = XRates.Cells.Count If CountCells < YRates.Count Then MsgBox "The ranges need to be the same size." & vbCrLf & "This program will self-destruct." Exit Sub End If For hi = 1 To CountCells If XRates(hi) < LoadPerString Then Exit For Next If hi CountCells Then DesignTime = YRates(CountCells) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If If hi = 1 Then DesignTime = YRates(hi) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True Exit Sub End If lo = hi - 1 DesignTime = YRates(lo) + (varused - XRates(lo)) / _ (XRates(hi) - XRates(lo)) * _ (YRates(hi) - YRates(lo)) ' copying design time to MainPage Worksheets("MainPage").Range("C19").FormulaR1C1 = DesignTime Sheets("MainPage").Activate Application.ScreenUpdating = True -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 and VB declaring number as double | Excel Discussion (Misc queries) | |||
How do I assign range to variant and use | Excel Discussion (Misc queries) | |||
Testing if Variant is Range or Double? | Excel Programming | |||
Variant to String | Excel Programming | |||
DLLs and VBA: Who free's a variant? | Excel Programming |