How to check when a VBA module was modified?

Question

I have written a version control module. The AutoExec macro launches it whenever I, or one of the other maintainers log in. It looks for database objects that have been created or modified since the previous update, and then adds an entry to the Versions table and then opens the table (filtered to the last record) so I can type in a summary of the changes I performed.

It is working great for Tables, Queries, Forms, Macros, etc but I cannot get it to work correctly for modules.

I have found two different properties that suggest a Last Modified date ...

CurrentDB.Containers("Modules").Documents("MyModule").Properties("LastUpdated").Value
CurrentProject.AllModules("MyModule").DateModified

The first one (CurrentDB) always shows "LastUpdated" as the Date it was created, unless you modify the description of the module or something in the interface. This tells me that this property is purely for the container object - not what's in it.

The second one works a lot better. It accurately shows the date when I modify and compile/save the module. The only problem is that when you save or compile a module, it saves / compiles ALL the modules again, and therefore sets the DateModified field to the same date across the board. It kind of defeats the purpose of having the DateModified property on the individual modules doesn't it?

So my next course of action is going to a bit more drastic. I am thinking I will need to maintain a list of all the modules, and count the lines of code in each module using VBA Extensions. Then, if the lines of code differs from what the list has recorded - then I know that the module has been modified - I just won't know when, other than "since the last time I checked"

Does anyone have a better approach? I'd rather not do my next course of action because I can see it noticeably affecting database performance (in the bad kind of way)

Answer 1

Here's a simpler suggestion:

1. Calculate the MD5 hash for each module.
2. Store it in the Versions table.
3. Recalculate it for each module during the AutoExec and compare it to the one in the Versions table. If it's different, you can assume it has been changed (while MD5 is bad for security, it's still solid for integrity).

To get the text from a module using VBE Extensibility, you can do

Dim oMod As CodeModule
Dim strMod As String
Set oMod = VBE.ActiveVBProject.VBComponents(1).CodeModule
strMod = oMod.Lines(1, oMod.CountOfLines)

And then you can use the following modified MD5 hash function from this answer as below, you can take the hash of each module to store it, then compare it in your AutoExec.

Public Function StringToMD5Hex(s As String) As String
    Dim enc
    Dim bytes() As Byte
    Dim outstr As String
    Dim pos As Integer
    Set enc = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    'Convert the string to a byte array and hash it
    bytes = StrConv(s, vbFromUnicode)
    bytes = enc.ComputeHash_2((bytes))
    'Convert the byte array to a hex string
    For pos = 0 To UBound(bytes)
        outstr = outstr & LCase(Right("0" & Hex(bytes(pos)), 2))
    Next
    StringToMD5Hex = outstr
    Set enc = Nothing
End Function

Answer 2

You can't know when a module was modified. The VBIDE API doesn't even tell you whether a module was modified, so you have to figure that out yourself.

---

The VBIDE API makes it excruciatingly painful - as you've noticed.

Rubberduck doesn't deal with host-specific components yet (e.g. tables, queries, etc.), but its parser does a pretty good job at telling whether a module was modified since the last parse.

"Modified since last time I checked" is really all you need to know. You can't rely on line counts though, because this:

Option Explicit

Sub DoSomething
    'todo: implement
End Sub

Would be the same as this:

Option Explicit

Sub DoSomething
    DoSomethingElse 42
End Sub

And obviously you'd want that change to be picked up and tracked. Comparing every character on every single line of code would work, but there's a much faster way.

The general idea is to grab a CodeModule's contents, hash it, and then compare against the previous content hash - if anything was modified, we're looking at a "dirty" module. It's C#, and I don't know if there's a COM library that can readily hash a string from VBA, but worst-case you could compile a little utility DLL in .NET that exposes a COM-visible function that takes a String and returns a hash for it, shouldn't be too complicated.

Here's the relevant code from Rubberduck.VBEditor.SafeComWrappers.VBA.CodeModule, if it's any help:

private string _previousContentHash;
public string ContentHash()
{
    using (var hash = new SHA256Managed())
    using (var stream = Content().ToStream())
    {
        return _previousContentHash = new string(Encoding.Unicode.GetChars(hash.ComputeHash(stream)));
    }
}

public string Content()
{
    return Target.CountOfLines == 0 ? string.Empty : GetLines(1, CountOfLines);
}

public string GetLines(Selection selection)
{
    return GetLines(selection.StartLine, selection.LineCount);
}

public string GetLines(int startLine, int count)
{
    return Target.get_Lines(startLine, count);
}

Here Target is a Microsoft.Vbe.Interop.CodeModule object - if you're in VBA land then that's simply a CodeModule, from the VBA Extensibility library; something like this:

Public Function IsModified(ByVal target As CodeModule, ByVal previousHash As String) As Boolean

    Dim content As String
    If target.CountOfLines = 0 Then
        content = vbNullString
    Else
        content = target.GetLines(1, target.CountOfLines)
    End If

    Dim hash As String
    hash = MyHashingLibrary.MyHashingFunction(content)

    IsModified = (hash <> previousHash)

End Function

So yeah, your "drastic" solution is pretty much the only reliable way to go about it. Few things to keep in mind:

• "Keeping a list of all modules" will work, but if you only store module names, and a module was renamed, your cache is stale and you need a way to invalidate it.
• If you store the ObjPtr of each module object rather than their names, I'm not sure if it's reliable in VBA, but I can tell you that through COM interop, a COM object's hashcode isn't going to be consistently consistent between calls - so you'll have a stale cache and a way to invalidate it, that way too. Possibly not an issue with a 100% VBA solution though.

I'd go with a Dictionary that stores the modules' object pointer as a key, and their content hash as a value.

---

That said as the administrator of the Rubberduck project, I'd much rather see you join us and help us integrate full-featured source control (i.e. with host-specific features) directly into the VBE =)