Home>

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.

  • 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