Home>

The property is invalid when calling a function in VBA
Why doesn't it happen when I combine two functions into one?
Above: I get an error
Below: Pass

Sub modify_one (wb As Worksheet)
    On Error Resume Next
    Dim words () As String, dir As String
    Dim objFSO As New FileSystemObject
    With wb
        dir = objFSO.GetFileName (.Range ("B18"). Value)
        words = Split (dir, "_")
        rename words, .name
    End With
End Sub
'Sheet modifier function, argument string decomposes file with _
Sub rename (words () As String, wbName As String)
    'Ignore any error and continue processing
    On Error Resume Next
    With Worksheets (wbName)
        'Sheet name. If there is. \/() or a sheet with the same name, an error will be thrown, but ignore it and proceed
        .name = words (1)&"_"&words (2)&"_"&words (3)&"_"&words (UBound (words) -4)
        'Mode name
        .Range ("D10"). Value = words (1)
        .Range ("D12"). Value = Join (words, "")
        .Range ("D14"). Value = Right (words (UBound (words) -4), 3)
    End With
End Sub
Sub modify_one (wb As Worksheet)
    On Error Resume Next
    Dim words () As String, dir As String
    Dim objFSO As New FileSystemObject
    With wb
        dir = objFSO.GetFileName (.Range ("B18"). Value)
        words = Split (dir, "_")
        Debug.Print (dir)
        Debug.Print (.name)
        'rename words, .name
        .name = words (1)&"_"&words (2)&"_"&words (3)&"_"&words (UBound (words) -4)
        'Mode name
        .Range ("D10"). Value = words (1)
        .Range ("D12"). Value = Join (words, "")
        .Range ("D14"). Value = Right (words (UBound (words) -4), 3)
    End With
End Sub
vba
  • Answer # 1

    The error cannot be reproduced.
    What kind of string is in cell A18?

    Looking through the code, is it like this? I wrote that.
    While reading, I get the impression that it is a very roundabout work procedure.

    Sub test ()
        Worksheets ("Sheet1"). Range ("A18"). Value = "C: \ Users \ hiraigumit \ Documents \"&"aaa_1.xlsx"
        Set_SheetRename Worksheets ("Sheet1"). Range ("A18")
    End Sub
    Function Modify_one (s As String) As String
        i = InStrRev (s, "\")
        Modify_one = Mid (s, i + 1)
    End Function
    Sub Set_SheetRename (c As Range)
        Dim v As Variant
        v = Split (Modify_one (c.Value), "_")
        words (UBound (v)) = words (UBound (v)) -4
        With c.Worksheet
            .Name = Join (v, "_")
            .Range ("D10"). Value = v (LBound (v))
            .Range ("D12"). Value = Join (v, "")
            .Range ("D14"). Value = Right (v (UBound (v)), 3)
        End With
    End Sub

  • Answer # 2

    Please try as follows.

    Call rename (words, .name)


    Call statement

    If the error is still the same, convert the parameter types to be passed so that they match.

    Then at the beginning of the source,Option ExplicitIf you didn't specify, let's do it.
    What is Option Explicit?