0
votes

I develop quite complex Excel VBA application (several thousands lines of code spread across modules, class modules and form modules). There is a need to translate it to multiple languages, which means to replace (almost) all of the strings in application and use some kind of hash-table with records for each language.

My question is what is the most efficient and convenient way to gather all strings used in application and replace them with some kind of language variables. Could anybody recommend some kind of best practices for this type of task for VBA project environment? Thanks in advance.

1
The best way to do this is to do it the hard way and manually change all your files by replacing hard-coded strings with resource calls. (Source: own experience). - Uwe Keim
Thanks, but I would disagree with you, Uwe. Manual replacing would take weeks of almost robotic work :). It is easy to get all the source code to plain text and perform replace of all text enclosed in double quotes (") for variables, I have even developed a "grabber" for this. Problem is that I get some strings in too atomic form. E.g. " first part of the text" & flag & "second part of the text" produces two separate variables, which is unwanted and hard to work with during translation process. - bazinac
I would write me a simple string.Format equivalent for VBA to have one string with placeholders instead of multiple fragments. Actually I did this 12 years ago for VBScript. Maybe this helps you further. - Uwe Keim
Thank you, I´ll have look on it. - bazinac
Look into MZ-Tools for VBA. It is free and I think you can convert strings into variables. mztools.com/v3/mztools3.aspx - John Alexiou

1 Answers

2
votes

One option is to use source tools addin to export all your code into source files under one folder. Example below: Source Files

Now you can write scripts to modify the files, and also now you can put them in source control. When done you can re-import them into the VBA project with changes.