VBA Kill Function

VBA Kill Function in Excel

VBA Kill function in Excel 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 Path-name argument represents the file path name. If path-name 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 Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

Leave a Reply