VBA Kill Function

VBA Kill Function

Overview of VBA Kill Function:


VBA Kill function or statement is categorized as File and Directory function. This built-in VBA Kill statement deletes a file or files based on the specified path name in Excel VBA. You can specify wildcard characters either * and ? to delete multiple files.
This function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA Kill Function in any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Kill function, where we can use this Kill Function and real-time exampleS in Excel VBA.

Syntax of VBA Kill Function

The syntax of the Kill Function in VBA is

Kill(PathName)

The Kill Function returns doesn’t return any file. It deletes a file or files. The Pathname argument represents the file path name. If pathname doesn’t include a folder name, the specified file considers current folder.

Parameters or Arguments:

The Kill function has one argument in Excel VBA.
where
PathName:It is a mandatory string type parameter. The PathName argument represents the path of a file, folder, or directory. It helps to delete a file or files. It contains wild card characters either *(multiple characters) and ? (single Character).

Wild Card Character Description
* Allows you to match any string of any length (including zero length)
? Allows you to match on a single character

Where we can apply or use VBA Kill Function?

We can use this Kill Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Delete a File

Here is a simple example of the VBA Kill function. This below example deletes a file and displays message.

'Delete a File
Sub VBA_Kill_Function_Ex1()

    'Variable declaration
    Dim sPath As String
    
    sPath = "C:\Someswari\VBAF1\VBA Functions\VBA Text Functions\Temp.xlsm"
    
    Kill (sPath)
        
    MsgBox "File Deleted Successfully : " & vbCrLf & sPath, vbInformation, "VBA Kill Function"
    
End Sub

Output: Here is the screen shot of the first example output.
VBA Kill Function

Example 2: Delete a File(Returns an Error)

Here is a simple example of the VBA Kill function. This below example returns an error. Because the specified is not available.

'Delete a File(Returns an Error)
Sub VBA_Kill_Function_Ex2()

    'Variable declaration
    Dim sPath As String
    
    sPath = "C:\VBAF1\VBA Functions\VBA Text Functions\Temp.xlsx"
    
    Kill (sPath)
        
    MsgBox "File has deleted : " & vbCrLf & sPath, vbInformation, "VBA Kill Function"
    
End Sub

Output: Here is the screen shot of the second example output.
VBA Kill Function

Example 3: Delete all Files using Wild card Character(* and ?)

Here is a simple example of the VBA Kill function. This below example deleted all .doc files from the ‘Temp’ folder. Here we used wild card characters(* and ?) to delete all .doc files.

'Delete all Files using Wild card Character(* and ?)
Sub VBA_Kill_Function_Ex3()

    'Variable declaration
    Dim sPath As String
    Dim iOutput As Integer
    
    sPath = "C:\Someswari\VBAF1\VBA Functions\VBA Text Functions\Temp\*.doc?"
    
    Kill (sPath)
        
    MsgBox "Deleted all files from the Temp Folder: " & vbCrLf & sPath, vbInformation, "VBA Kill Function"
    
End Sub

Output: Here is the screen shot of the third example output.
VBA Kill Function

Example 4: Delete all .xls Files from the C-Drive

Here is a simple example of the VBA Kill function. This below example deleted all .xls files from the ‘C’ Drive. Here we used wild card character(*) to delete all .xls files from the C-Drive.

'Delete all .xls Files from the C-Drive
Sub VBA_Kill_Function_Ex4()

    'Variable declaration
    Dim sPath As String
    
    sPath = "C:\*.xls"
    
    If Len(Dir(sPath)) > 0 Then
        SetAttr sPath, vbNormal
        Kill sPath
    End If
        
    Kill (sPath)
        
    MsgBox "Deleted all .xls files from the C-Drive: " & vbCrLf & sPath, vbInformation, "VBA Kill Function"
    
End Sub

Output: Here is the screen shot of the fourth example output.
VBA Kill Function

Example 5: Delete all .ppt Files from the Current Directory

Here is a simple example of the VBA Kill function. This below example deleted all .ppt files from the current directory. Here we used wild card character(*) to delete all .ppt files from the current directory.

'Delete all .ppt Files from the Current Directory
Sub VBA_Kill_Function_Ex5()

    'Variable declaration
    Dim sPath As String
    
    sPath = "*.ppt"
    
    If Len(Dir(sPath)) > 0 Then
        SetAttr sPath, vbNormal
        Kill sPath
    End If
    
    Kill (sPath)
        
    MsgBox "Deleted all .ppt files from the current Directory: " & vbCrLf & sPath, vbInformation, "VBA Kill Function"
    
End Sub

Output: Here is the screen shot of the fifth example output.
VBA Kill Function

Instructions to use Macro Codes

Here are the step by step instructions to use the Kill Function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.

VBA File & Directory Functions List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *