Declaring As Variant, Changing to Double?

[k]

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=...d834&seekm=OQcrrWjsBHA.1452@tkmsftngp05#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

--

 

[kkknie]

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

 

[Frank Kabel]

Hi
you may use
CDbl
for this

 

[Juan Pablo González]

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)

 

[Bob Phillips]

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)

 

[Ron de Bruin]

Look in the VBA help also

You can use Type 1 if you only want that the user
can enter numeric values

 

[Ivan F Moala]

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.

 

[k]

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

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)

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=...d834&seekm=OQcrrWjsBHA.1452@tkmsftngp05#link7