I have created a multi-lingual Excel application. It handles UserForm, menu, VBA messages and worksheet cell translations. It retrieves the translations from an Access database.
All works pretty well. However, I am not particularly happy the way I handled the VBA messages to users. Let me explain:

All the user-exposed texts (e.g. messages, error messages, status bar text, etc.) are defined as string Constants:

[vba]
' Public string constant for status bar:
Public Const gsSTATUS_RECALCULATING As String = "Recalculating model"
[/vba]

The following code tries to retrieve a translated value:

[vba]
' Get status bar message from Access database:
gsVBATrans = mclsTranslate.sGetVBAText("gsSTATUS_RECALCULATING")
' If no translation available, use the text value of the string constant :
If gsVBATrans = "" Then gsVBATrans = gsSTATUS_RECALCULATING

With mclsProgBar
.Text = gsVBATrans & gsPOINT_PENDING
.Show
End With
[/vba]

The function "sGetVBAText" is given a KeyID (that is in fact the value of the string constant) and tries to retrieve a translation from the Access database. If the routine returns an empty string it means that for whatever reason no translation is available (e.g. perhaps the link to the Access database could not be made). At that point in time it should use the string value of the constant as value to disclose to the user.

My application uses a lot of these constructions to handle VBA text visible to users. And it really clutters a lot of my routines.


So far a description on how it works. Now the "issue" I have => this would be much more elegant & efficient if I could assign a Constant as a parameter to a function. In that case something like the second line in the code below would suffice in all my calling subroutines if I wanted to retrieve a translated piece of text:

[vba]
With mclsProgBar
.Text = mclsTranslate.sGetVBAText("gsSTATUS_RECALCULATING", gsSTATUS_RECALCULATING)
.Show
End With
[/vba]

It throws up both the Constant and the string KeyID for the translation. If the KeyID is not found, the routine would just return the value of the Constant.

As far as I am aware, you can not use a Constant as a function parameter. Questions I have:
  1. Am I correct in this assumption.
  2. If not, please can you let me know how I would do that?
  3. If I am correct, would you have a suggestion that would deliver what I (tried to) explain(ed)?
Would be very much interested in your opions. I liked a lot of the routines I written, but when I added the multi-lingual feature to all of them, I became compelled to find a more elegant alternative.