Can VBA code be programmed to change itself?

[Kelvin Stott]

Given that VBA code can be programmed to make virtually any changes in Excel, Word, etc., I was wondering if VBA code can be written to change itself, for example, by deleting itself if certain conditions are met, for security purposes?

 

[shg]

One way:

Sub KillMe()</SPAN></SPAN></SPAN>
    With ThisWorkbook</SPAN></SPAN></SPAN></SPAN>
        If Len(Dir(.FullName)) Then</SPAN></SPAN></SPAN></SPAN>
            .Saved = True</SPAN></SPAN></SPAN></SPAN>
            On Error Resume Next</SPAN></SPAN></SPAN></SPAN>
            .ChangeFileAccess Mode:=xlReadOnly</SPAN></SPAN></SPAN></SPAN>
            On Error GoTo 0</SPAN></SPAN></SPAN></SPAN>
            SetAttr .FullName, vbNormal</SPAN></SPAN></SPAN></SPAN>
            Kill .FullName</SPAN></SPAN></SPAN></SPAN>
        End If</SPAN></SPAN></SPAN></SPAN>
        .Close SaveChanges:=False</SPAN></SPAN></SPAN></SPAN>
    End With</SPAN></SPAN></SPAN></SPAN>
End Sub</SPAN></SPAN></SPAN></SPAN>

 

[Kelvin Stott]

Cool, thanks.

What does it do, exactly? Does it affect the Excel spreadsheet, or just the VBA code?

 

[shg]

Deletes the file containing the code.

 

[Kelvin Stott]

The Excel spreadsheet (including any data), or just the VBA code?

If it's the entire Excel file, then I guess it would have to be used as an Addin, to avoid losing any data, right?

 

[shg]

The entire file.

 

[Kelvin Stott]

Any way to kill/delete just the VBA code/project, without deleting the entire Excel file?

 

[shg]

Sub KillMyCode()
    Dim sFile       As String

    With ThisWorkbook
        sFile = .FullName

        If Len(Dir(sFile)) > 0 And Right(sFile, 4) = "xlsm" Then
            Application.DisplayAlerts = False
            .SaveAs Filename:=Left(sFile, InStrRev(sFile, ".")) & "xlsx", _
                    FileFormat:=xlOpenXMLWorkbook
            SetAttr sFile, vbNormal
            Kill sFile
        End If

        .Close SaveChanges:=False
    End With
End Sub

 

[Kelvin Stott]

Wow, thanks. I wasn't sure this would even be possible.

 

[hwkeyser]

Sorry if this is an old thread, but I'm actually ruminating on that same question myself:

Would it be possible for the VBA code, instead of changing itself in a destructive manner, to change itself in a constructive manner? Such as expanding its own ranges or including new sheets in a coded query?

I'm going to start an experiment where a spreadsheet queries, returns results, and develops new queries. And i'm curious how the VBA might develop new sheets and include those sheets in the next query.

Any suggestions?