VBA Code Change

[tingxing]

Hello,

Is is possible to detect the code change happened to VBA project? Or
something like "save" event of VBA project (not workbook)?

I intend to leave a mark in worksheet cells if VBA code has been
edited, and next time when the book is opened, I can perform some
processing if the mark is on and then turn off it.

Thanks in advance.

Ting

 

[JLGWhiz]

Why not just protect your code to prevent changes. In the VB editor,
Tools>VBAProjectProperties>Protect

You can set a password to prevent unauthorized changes by most users.
Of course, passwords can be overcome by astute users, but it would be
effective against most.

 

[tingxing]

No, I permit source code change, instead of protecting it.

In VBA project, some constants are defined, which serve as
configuration purpose. When configuration changes, I need
to do something like re-formatting in the workbook the next time
it is opened (according to the new configuration).

So the most expected behaviour is that some mark can be done
"automatically" once VBA project is changed.

Ting

 

[JLGWhiz]

I don't believe VBA supports a self editing capability, other than the
compiler, syntax and structure edits. If the changes do not generate errors,
then the built in edits would never detect the changes.

I don't know if the 'LastModified' property would be that useful since it
might not refer to code modifications. You can check that out in VBA help.

 

[JLGWhiz]

The two macros below allow you to check for the date a file was last
modified. Don't know if that will help you with what you are trying to do,
but you can make that evaluation. The short macro is used to activate the
longer one. Just substitute your actual file name for the value of fName.

Sub getdate()
fName = "YourFileName.xls"
ShowFileAccessInfo (fName)
End Sub

Sub ShowFileAccessInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(filespec) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub