Search code examples
ms-accessvbams-access-2007

Annoying vba naming behaviour


I'm using access 2007 and this behaviour can be replicated as follows.

1) Create new access database accdb file.
2) Open database and create new vba module.
3) Create 1st subroutine sub1:

Sub sub1()
    Msgbox Err.Description
End Sub

4) Create 2nd subroutine sub2:

Sub sub2(Description as String)
    Msgbox Description
End Sub

At this point everything is normal.
5) But if I go and change sub2 so that 'Description' reads 'description', i.e. change 'D' to 'd' like so:

Sub sub2(description as String)
    Msgbox description
End Sub

This also has a knock-on effect and changes sub1 too! So that sub1 now reads:

Sub sub1()
    Msgbox Err.description
End Sub

Why has 'Err.Description' changed to 'Err.description' ?

This behaviour seems to have no effect on the actual functionality of the code, so no problem there. The big issue I have is that I'm exporting my vba modules as text files and putting them under SVN control. And just recently a whole load of pointless 'changes' have been committed to the repository because of this.

Any ideas on how to stop this from happening?


Solution

  • Sorry. That is a hard-coded "feature" of VBA. See similar question here: How does one restore default case to a variable in VBA (Excel 2010)?

    The way I've worked around that with source control is to run my repository through a script that does the following:

    1. Revert all modified files with vba code extensions (creating backup .orig files)
    2. Do a case-insensitive compare of the .orig files to their counterparts
    3. If there are no changes (outside of case changes) delete the .orig file
    4. For the remaining .orig files (the ones with actual changes) delete the counterpart file and remove the .orig extension

    What this does is effectively hide files where the only changes are to the case (a constant problem when working with VBA files, as you're experiencing). It does not hide case changes in a file that has had other modifications done to it. It's far from a perfect solution but it's the best I've come up with.