Home>

vba - about program error location

Now I'm making a function to copy data in an arbitrary range with VBA.
Below is the program.

Function Copy function ()
Dim input_rg As Range
Dim output_rg As Range
Set input_rg = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8)
Set output_rg = Application.InputBox ("Please select the paste destination", Title: = "Output form", Type: = 8)
If input_rg = False Or output_rg = False Then
    End
End If
input_rg.Copy Destination: = output_rg

End Function


If i do this, you will get a "type mismatch" error.
The location is [Set input_rg = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8)].

Receiving this error message
I did as follows.

1. I checked the cell address and type in the Immediate window.
? input_rg.address
$C $3: $F $3
? typename (input_rg)
Range
? typename (output_rg)
Range
? output_rg.address
$C $10: $F $10
2. I changed the type from range type to Variant type, but it spits out the same error.


Thanks for your response.

Receiving an answer
First of all, thanks for your answer. I'm wondering whether to write in the comments, but I'll write here because it's going to be long.

After getting the answer, it was executed again, without any errors.
However, if cancel is selected in theinputboxdialog box ofapplication, the error [Type does not match] will be spit out again.
After examining it, the return value of inputbox cancellation seems to be False.
I'm sorry to get your answer, but I would be happy if you could answer again based on this point.

vba function copy
  • Answer # 1

    Is there an error, is it really there? I tested it and it passed without error.

    but

    If input_rg = False Or output_rg = False Then 'Error here
    End
End If


    An error occurred.

    Use Is Nothing to determine whether an object exists.
    It was possible to execute without error if corrected below.

    If input_rg Is Nothing Or output_rg Is Nothing Then
        Exit Function
    End If
    Code corresponding to cancellation 
    Function Copy function () As Boolean
    Dim input_rg As Range
    Dim output_rg As Range
    Copy function = False
    On Error Resume Next
    Set input_rg = Application.InputBox ("Select a cell to copy", _
                                        Title: = "Input form", Type: = 8)
    If Err.Number<>0 Then
        MsgBox "Cancelled"
        Exit Function
    End If
    Set output_rg = Application.InputBox ("Please select the paste destination", _
                                         Title: = "Output form", Type: = 8)
    If Err.Number<>0 Then
        MsgBox "Cancelled"
        Exit Function
    End If
    If input_rg Is Nothing Or output_rg Is Nothing Then
        Exit Function
    End If
    input_rg.Copy Destination: = output_rg
    If Err.Number = 0 Then
        Copy function = True
    End If
End Function

    Since it is a Function, I tried to make it a specification that returns whether copying was successful.

    Bonus

    In the question code,Endtries to end the function, butExit Functionusually leaves the function.
    On the other hand,Endwill completely terminate the running macro, butExit Functionwill execute the next code if it exits from the function . You should be aware of the differences and use them properly.

    Reference code

    Sub hoge ()
    Debug.Print "hoge"
    Exit Sub
End Sub
Sub fuga ()
    Debug.Print "fuga"
    End
End Sub
Sub hogefuga ()
    Call hoge
    Debug.Print "next to hage"
    Call fuga
    Debug.Print "next to fuga"
End Sub


    Immediate window

    hoge
next to hage
fuga

  • Answer # 2


    I thought about how to determine cancellation with an expected error.

    Function Copy function ()
    Dim input_rg As Range, output_rg As Range
    On Error GoTo myErr
    Set input_rg = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8)
    Set output_rg = Application.InputBox ("Please select the paste destination", Title: = "Output form", Type: = 8)
    On Error GoTo 0
    Answer from 'hatena19
    If input_rg Is Nothing Or output_rg Is Nothing Then
        Exit Function
    End If
    input_rg.Copy Destination: = output_rg
myErr:
    If Err.Number = 424 Then
        MsgBox "Cancelled"
        Err.Clear
    Else
        Err.Raise Err.Number
    End If
End Function


    Since the return value of Inputbox cancellation is False, an error occurs in the Range type.
    You may not like it, but if you use Variant type, there is a way to judge cancellation.

    Function Copy function ()
'Variant type for cancel decision
Dim input_vt As Variant, output_vt As Variant
Set input_vt = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8)
Set output_vt = Application.InputBox ("Please select the paste destination", Title: = "Output form", Type: = 8)
'Decision of cancellation
If VarType (input_vt) = vbBoolean Or VarType (output_vt) = vbBoolean Then
    Exit Function
End If
Answer from 'hatena19
If input_vt Is Nothing Or output_vt Is Nothing Then
    Exit Function
End If
input_vt.Copy Destination: = output_vt
End Function

Related articles