I defined the following type:
Public Type settings
key As String
german As String
french As String
End Type
How does the correct code look like to define an array with ~100 literal entries?
Someting like:
Dim translations as Array() = {
(key="send", german="Senden", french="Enregistrer"),
(key="directory", german="Verzeichnis", french="Liste"),
...
There's no nice syntax for that in VBA. If you really need to init an array of a user-defined type, you can use a couple of helper functions to do it like this:
Public Type settings
key As String
german As String
french As String
End Type
Private Function NewTranslation(key As String, german As String, french As String) As settings
NewTranslation.key = key
NewTranslation.german = german
NewTranslation.french = french
End Function
Private Sub AddTranslation(translations() As settings, value As settings)
Dim u As Integer
u = -1
On Error Resume Next ' ubound raises an error if the array is not dimensioned yet
u = UBound(translations)
On Error GoTo 0
ReDim Preserve translations(0 To u + 1) As settings
translations(u + 1) = value
End Sub
Public Sub Main()
Dim translations() As settings
AddTranslation translations, NewTranslation("send", "Senden", "Enregister")
AddTranslation translations, NewTranslation("directory", "Verzeichnis", "Liste")
' and so on
End Sub
A nicer way to do this particular problem would be a Collection (map) object using the language code and the original text as the key:
Private translations As New Collection
Public Sub Main()
With translations
.Add "Senden", "de:send"
.Add "Enregister", "fr:send"
.Add "Verzeichnis", "de:directory"
.Add "Liste", "fr:directory"
End With
MsgBox GetTranslation("de", "send")
End Sub
Public Function GetTranslation(language As String, s As String)
GetTranslation = s ' default to original text if no translation is available
On Error Resume Next
GetTranslation = translations(language + ":" + s)
End Function
This is not a direct answer to your question:
The way I handle translation text is to maintain it on an excel sheet (which is usually hidden inside the XLA addin), then read it (or just the column required for the current language) into a variant containing an array.
It is much easier to maintain and expand (add new languages or words) if you do it this way.
