Search

Useful ready to use VBA Functions. Share these useful functions with your friends

Useful VBA functions. More to be added

To submit useful VBA functions to this blog email VBAa2z.team@gmail.com
  
Option Explicit

Public Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
   'more VBA @ https://www.youtube.com/vbaa2z
    Dim lngAttributes As Long
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory)
    Else
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

Function FolderExists(sFile As Variant) As Boolean
   'more VBA @ https://www.youtube.com/vbaa2z
   On Error Resume Next
   If Len(sFile) > 0 Then
           FolderExists = (Len(Dir$(sFile, vbDirectory)) > 0&)
   End If
End Function

Public Function FileName(strPath As String) As String
   'more VBA @ https://www.youtube.com/vbaa2z
    FileName = Mid$(strPath, InStrRev(strPath, "\") + 1)
End Function

Function FileNameNoExt(strPath As String)
   'more VBA @ https://www.youtube.com/vbaa2z
   FileNameNoExt = Split(Mid(strPath, InStrRev(strPath, "\") + 1), ".")(0)
End Function

Function FilePath(strPath As String) As String
   'more VBA @ https://www.youtube.com/vbaa2z
   FilePath = Left(strPath, InStrRev(strPath, "\"))
End Function


Public Function TrailingSlash(varIn As Variant) As String
   'more VBA @ https://www.youtube.com/vbaa2z
   If Len(varIn) > 0& Then
      If Right(varIn, 1&) = "\" Then
      TrailingSlash = varIn
      Else
      TrailingSlash = varIn & "\"
      End If
   End If
End Function


Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
   'more VBA @ https://www.youtube.com/vbaa2z
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function


Function ListFiles(strPath As String)
   'more VBA @ https://www.youtube.com/vbaa2z
    Dim strFile
    strFile = Dir(strPath)
    Do While strFile <> ""
        Debug.Print strPath & strFile
        strFile = Dir
    Loop
End Function

Function IsWbOpen(x As String) As Boolean
   'can handler addin too;
   'more VBA @ https://www.youtube.com/vbaa2z
    Dim w As Workbook
        IsWbOpen = False
        On Error GoTo ErrHndler
        Set w = Workbooks(x)
        IsWbOpen = True
    Set w = Nothing
Exit Function
ErrHndler:
IsWbOpen = False
End Function

Function IsWbOpenByLOOP(wbName As String) As Boolean
   'more VBA @ https://www.youtube.com/vbaa2z
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
        If Workbooks(i).Name = wbName Then Exit For
    Next
    If i <> 0 Then IsWbOpenByLOOP = True
End Function


Function closewb(x As String) As Boolean
   'can handler addin too;
   'more VBA @ https://www.youtube.com/vbaa2z
    Dim w As Workbook
        closewb = False
        On Error GoTo ErrHndler
        Set w = Workbooks(x)
        w.Close False
        closewb = True
    Set w = Nothing
Exit Function
ErrHndler:
closewb = False
End Function
Labels: