I can't return a variable from a function and use it as a condition

%3CLINGO-SUB%20id%3D%22lingo-sub-2480003%22%20slang%3D%22en-US%22%3EI%20can't%20return%20a%20variable%20from%20a%20function%20and%20use%20it%20as%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2480003%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%3CBR%20%2F%3EI%20want%20to%20create%20a%20file%20in%20a%20directory%2C%20but%20only%20if%20that%20directory%20already%20exists.%20If%20it%20does%20not%20exist%2C%20it%20returns%20a%20message%20warning%20that%20the%20directory%20does%20not%20exist.%3CBR%20%2F%3EI%20am%20not%20able%20to%20do%20the%20function%20and%20I%20would%20like%20help%20to%20understand%20the%20correct%20way%20to%20do%20it.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Inicio()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20if%20V_Dir(%22C%3A%5CUsers%5C%22%20%26amp%3B%20Environ(%22username%22)%20%26amp%3B%20%22%5COneDrive%5CMatriz%22%2C%22N%C3%A3o%20existe%22)%3D%20%22Existe%22%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20With%20Workbooks.Add%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B.SaveAs%20%22C%3A%5CUsers%5C%22%20%26amp%3B%20Environ(%22username%22)%20%26amp%3B%20%22%5COneDrive%5CMatriz%5CDIARIAS_BDadosAux.xls%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BThisWorkbook.Worksheets(%22BD%20COLAB%22).Range(%22A%3AA%22).Copy%20Destination%3A%3DWorkbooks(%22DIARIAS_BDadosAux.xls%22).Worksheets(%22Folha1%22).Range(%22A%3AA%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%20With%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20MsgBox%20%22A%20Directoria%20para%20ser%20colocado%20o%20ficheiro%20a%20ser%20usado%20pelas%20Di%C3%A1rias%2C%20n%C3%A3o%20existe%20!!!%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFunction%20V_Dir(strFolderName%20As%20String%2C%20strTextoMensagem)%20As%20String%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20strFolderExists%20%3D%20Dir(strFolderName%2C%20vbDirectory)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20strFolderExists%20%3D%20%22%22%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20MsgBox%20strTextoMensagem%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20V_Dir%3D%20%22Existe%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Exit%20Function%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20If%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2480003%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2480066%22%20slang%3D%22en-US%22%3ERe%3A%20I%20can't%20return%20a%20variable%20from%20a%20function%20and%20use%20it%20as%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2480066%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20this%20version%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Inicio()%0A%20%20%20%20Dim%20strFolder%20As%20String%0A%20%20%20%20strFolder%20%3D%20%22C%3A%5CUsers%5C%22%20%26amp%3B%20Environ(%22username%22)%20%26amp%3B%20%22%5COneDrive%5CMatriz%22%0A%20%20%20%20If%20V_Dir(strFolder%2C%20%22N%C3%A3o%20existe%22)%20%3D%20%22Existe%22%20Then%0A%20%20%20%20%20%20%20%20With%20Workbooks.Add%0A%20%20%20%20%20%20%20%20%20%20%20%20ThisWorkbook.Worksheets(%22BD%20COLAB%22).Range(%22A%3AA%22).Copy%20Destination%3A%3D.Worksheets(1).Range(%22A%3AA%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.SaveAs%20strFolder%20%26amp%3B%20%22%5CDIARIAS_BDadosAux.xls%22%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20MsgBox%20%22A%20Directoria%20para%20ser%20colocado%20o%20ficheiro%20a%20ser%20usado%20pelas%20Di%C3%A1rias%2C%20n%C3%A3o%20existe%20!!!%22%0A%20%20%20%20End%20If%0AEnd%20Sub%0A%0AFunction%20V_Dir(strFolderName%20As%20String%2C%20strTextoMensagem)%20As%20String%0A%20%20%20%20If%20Dir(strFolderName%2C%20vbDirectory)%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20strTextoMensagem%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20V_Dir%20%3D%20%22Existe%22%0A%20%20%20%20End%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Contributor

Good Morning
I want to create a file in a directory, but only if that directory already exists. If it does not exist, it returns a message warning that the directory does not exist.
I am not able to do the function and I would like help to understand the correct way to do it.

Thanks.

 

Sub Inicio()
        if V_Dir("C:\Users\" & Environ("username") & "\OneDrive\Matriz","Não existe")= "Existe" Then
              With Workbooks.Add
                     .SaveAs "C:\Users\" & Environ("username") & "\OneDrive\Matriz\DIARIAS_BDadosAux.xls"
                     ThisWorkbook.Worksheets("BD COLAB").Range("A:A").Copy Destination:=Workbooks("DIARIAS_BDadosAux.xls").Worksheets("Folha1").Range("A:A")
             End With
        Else
              MsgBox "A Directoria para ser colocado o ficheiro a ser usado pelas Diárias, não existe !!!"
        End If
End Sub


Function V_Dir(strFolderName As String, strTextoMensagem) As String
      strFolderExists = Dir(strFolderName, vbDirectory)
      If strFolderExists = "" Then
          MsgBox strTextoMensagem
          V_Dir= "Existe"
          Exit Function
      End If
End Function

1 Reply

Try this version:

Sub Inicio()
    Dim strFolder As String
    strFolder = "C:\Users\" & Environ("username") & "\OneDrive\Matriz"
    If V_Dir(strFolder, "Não existe") = "Existe" Then
        With Workbooks.Add
            ThisWorkbook.Worksheets("BD COLAB").Range("A:A").Copy Destination:=.Worksheets(1).Range("A:A")
            .SaveAs strFolder & "\DIARIAS_BDadosAux.xls"
        End With
    Else
        MsgBox "A Directoria para ser colocado o ficheiro a ser usado pelas Diárias, não existe !!!"
    End If
End Sub

Function V_Dir(strFolderName As String, strTextoMensagem) As String
    If Dir(strFolderName, vbDirectory) = "" Then
        MsgBox strTextoMensagem
    Else
        V_Dir = "Existe"
    End If
End Function