4

I have a coding project in MS Access. There are some developers coding and checking in the source-code to a SVN-Server. As a SVN-Server is for managing code, it recognizes changes in the source-code files. In these source-code-files there is a problem. VBA often changes uppercase and lowercase letters and we have no clue why.

When I do a commit it is i.e 

'two examples
Call myfunction(txtNodeID)
rst![Username] = Environ("USERNAME")

Another developer updates his repository and uses my code and it changes to 

'two examples
Call myfunction(txtNodeId)
rst![UserName] = Environ("USERNAME")

SVN recognizes this as a change. So we have many changed files although the logic did not change but Access modified uppercase and lowercase.

Does anyone know why Access is acting like this and how to prevent this?

Thank you.

Erik A
  • 28,352
  • 10
  • 37
  • 55
rodgerwilco
  • 193
  • 12
  • 2
    Yes, there is a slight difference. Uppercase and lowercase are changed txtNodeID -> txtNodeId Username -> UserName – rodgerwilco Jun 04 '18 at 08:35
  • 1
    It's not Access but VBA here that cause this problem. It's the same with Excel or other VBA enabled documents. Take a look here: [VB Naming rules](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/visual-basic-naming-rules). **Note Visual Basic isn't case-sensitive, but it preserves the capitalization in the statement where the name is declared.** Each time you declare a variable (case insensitive), all variables with the same name (whatever the case nor the context) will be renamed to use the same case. – Vincent G Jun 04 '18 at 11:53
  • @VincentG - very good article. And after I checked some of my cases it appears that some of my objectnames / variablenames / columnnames are keywords. In addition to that VBA sometimes mixes up the spelling when in different forms an object has the same name with different capitalisation – rodgerwilco Jun 05 '18 at 08:51

2 Answers2

3

The VBA editor should enforce variable name case to be the same as the declared variable name. This means, you should be unable to write the following:

Dim someID As String
someId = "5"

It does the same with functions. So ENVIRON becomes Environ.

However, this can get weird if you don't enforce variable declaration (no Option Explicit at the top). Then the editor will have a hard time knowing which is the correct case, and tend to change everything to the first occurrence.

The solution is to get all devs to use Option Explicit, so each variable is explicitly declared and the VBA editor knows which is the correct case (and there are many additional advantages). To avoid quirks, they should also make the edited line loses focus before saving (that's when the check happens, so you can do this wrong if you really want to. If you do, it can change to the correct case when touched).

Erik A
  • 28,352
  • 10
  • 37
  • 55
  • This doesn't help with object names (e.g. table field or control names), though. You can't `Dim` them. – Andre Jun 04 '18 at 10:06
  • 2
    @Andre Those should capitalize too, if you're using them as properties (like using `Me.FieldName` or just `FieldName`) if you're using them in the context of forms or reports. They're declared by adding them on the form, and use the capitalization set there. If you're using the bang notation (e.g. `Me!FieldName`), you're implicitly doing `Me.Controls("FieldName")` (where `Controls` is the default collection of the object), and that's a string and doesn't auto-capitalize so should not change automatically. – Erik A Jun 04 '18 at 10:17
2

The txtNodeID is changed to txtNodeId, because the latter is declared in the program somehow. Try this piece:

Sub TestMe()
    someVariable = 5
End Sub

Then write on another module only Public SOMEVARIABLE. The TestMe sub would look like this now:

Sub TestMe()
    SOMEVARIABLE = 5
End Sub
Vityata
  • 39,812
  • 7
  • 40
  • 77
  • Well, I didn't Dim the "Username" nowhere in the whole project. But i am surprised that VBA is performing a cross-modul-check... – rodgerwilco Jun 04 '18 at 10:44
  • @rodgerwilco - but I guess it is the name of a control element/ object/Form/Field from the interface. Thus, VBA reads it from there. – Vityata Jun 04 '18 at 10:46
  • this maybe, but I don't get why the letters change when another developer imports my sources from above. – rodgerwilco Jun 04 '18 at 10:51
  • @rodgerwilco - what happens if you select `txtNodeID` and you press `Ctrl + Space`? Is it automatically changed to `txtNodeId`? Plus, as you are using source control for VBA (Congrats for this!), unfortunately the dev is not uploading his own "Access", but only the code. Thus everyone may have different form inside access, named differently - `txtNodeID` or `txtNodeId`. Go and check! :) – Vityata Jun 04 '18 at 10:52