Object variable problem

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I am trying to set an object variable. I get an error message when I
try to do the following:

Dim NumberofColumns As Variant
Dim BMax As Integer

Set NumberofColumns = Application.InputBox(prompt:="Enter Number of 2D
Elements", Type:=1)

If NumberofColumns Is Nothing Then
MsgBox ("Operation Canceled")
Exit Sub
Else
BMax = NumberofColumns
End If

When I enter data or click on CANCEL the error says Type Mismatch. Is
this because Variant and Type:= 1 are not compatible?
 
Get rid of the 'Set' keyword in the line of code. InputBox
returns an object type variable only when Type is set to 8.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
ExcelMonkey,

Try something like:

Sub test()
Dim NumberofColumns As Variant
Dim BMax As Integer

NumberofColumns = Application.InputBox(prompt:="Enter Number of 2D
Elements", Type:=1)

If TypeName(NumberofColumns) = "Boolean" Then
If NumberofColumns = False Then
MsgBox ("Operation Canceled")
Exit Sub
End If
End If
BMax = NumberofColumns
End Sub


I use TypeName statement so that you can use 0 as a number. False = 0

Rob
 
A number is not an object. So remove the "set". Then check for
"numberOfColumns = false not nothing
 
Back
Top