I have this Excel VBA function that takes three parameters. It is essentially a translation mechanism with the following parameters:

  1. A sentence which has to be translated.
  2. A range of words to look for and what to translate them to.
  3. Shows in which column the translation is in the range (usually it's 2).

This function works perfect with small 'vocabulary', but when the range gets big and many translations occur, my file can become so difficult it loads in 15 minutes. I have no ideas how to make this code simpler because I'm new to VBA. Could anyone give me advice?

Function FindSubStringAndReplaceRange(sentence As String, range As range, selected As Integer)
    Dim position As Integer
    Dim searchableLength As Integer
    Dim newSentence As String
    Dim intASCII As Integer

    Dim searchable As String
    Dim replaceWith As String

    FindSubStringAndReplaceRange = sentence

    For Each i In range.Rows
        If IsEmpty(i.Cells(1, 1)) = False Then

            searchable = i.Cells(1, 1)

            If IsEmpty(i.Cells(1, selected)) = False Then
                replaceWith = i.Cells(1, selected)
                replaceWith = vbNullString
            End If

            searchableLength = Len(searchable)
            position = InStr(UCase(FindSubStringAndReplaceRange), UCase(searchable))
            If position <> 0 Then
                intASCII = Asc(Right$(Left$(FindSubStringAndReplaceRange, position), 1))

                If intASCII >= 65 And intASCII <= 90 Then
                    replaceWith = UCase(Left$(replaceWith, 1)) + Right$(replaceWith, Len(replaceWith) - 1)
                End If

                FindSubStringAndReplaceRange = Left$(FindSubStringAndReplaceRange, position - 1) + replaceWith + Right$(FindSubStringAndReplaceRange, Len(FindSubStringAndReplaceRange) - (position + searchableLength - 1))

            End If
         End If

End Function

Best approach

If your vocabulary library is very large you need to implement another data structure and sorting algorithm for your vocabulary. I would go with Bucket Sort. You need to partition your strings into separate buckets of characters to optimize. Comparing entire strings is not efficient.

Alternative approach

If you are lazy or prefer a different approach, utilize Microsoft Query (ACE.OLEDB - SQL) which is much more efficient than any VBA code. Dump your vocabulary to a single worksheet and execute an SQL to find your matches like so (for each sentence separately):

  1. Split string to tokens and dump it to another worksheet DumpedTokens

  2. Execute a query like this:

Find all matches in the Vocab worksheet. Keep tokens that don't have translations:

SELECT Iif(Vocab.Word = NULL, Dumped.Token, Vocab.TranslatedWord) FROM [DumpedTokens$] as Dumped 
LEFT OUTER JOIN [VocabularySheet$] as Vocab 
ON UCase(Vocab.Word) = UCase(Dumped.Token)
  1. Join the strings back into sentences

This will probably run a couple of seconds.

If you want you can use my SQL AddIn to Excel which makes it easier to write SQL queries in Excel: http://www.analystcave.com/excel-tools/excel-sql-add-in-free/

| improve this answer | |
  • \$\begingroup\$ the vocabulary is currently 500 lines long and i am repeating the function for 300 times \$\endgroup\$ – Mantas Jun 10 '15 at 8:06
  • \$\begingroup\$ It is unfortunately not possible to split the strings into tokens because the vocabulary is not for single words, it is for sentences (for example, the vocabulary contains a translation for "My dog is brown", not four entries for each word) \$\endgroup\$ – Mantas Jun 10 '15 at 8:36
  • \$\begingroup\$ Then try the Bucket Sort or maybe explore using RegExp with Replace \$\endgroup\$ – AnalystCave.com Jun 10 '15 at 8:41

If you have a very large vocabulary and you are going to perform the translation 1000 of times, then it is good to make a persistent Dictionary of your vocabulary. A dictionary is very easy to maintain and access using Keys; here your key will be the word and value will hold the translated word.

Here is my approach and it's doing what you are trying to achieve. Assuming 2nd column in your Vocabulary Range is always going to hold Translation

  1. Create a Module Menu->Insert->Module
  2. Add reference to Microsoft Scripting Runtime through Menu->Tools->References...
  3. Create a Public Dictionary Variable
  4. Refer to my screenshot below for full code. Only few lines of code is going to do your work.

Translation in VBA


| improve this answer | |
  • 2
    \$\begingroup\$ It makes people's lives easier if you post actual code instead of a screenshot of code. It's easier to read, copy & paste. \$\endgroup\$ – FreeMan Jun 10 '15 at 15:39
  • \$\begingroup\$ I always believes that simple copy & paste makes us lazy; however, when we write the same code, it makes us play around the code. \$\endgroup\$ – Akif Patel - BRMS Jun 11 '15 at 2:13

...but when the range gets big...

One of the simplest ways to drastically improve performance in this function is to minimize the interaction with the cells. Most of the overhead is caused by cell and font formatting, which you don't care about

Use a variant to get all values in memory:

Dim memArr As Variant

memArr = range

now use memArr the same way you use the range (rows and cols concept)

BTW: bad idea to use a variable name ("range") that is exactly the same as an Excel object ("Range") - I'd change the parameter name to something else

The other answers are good but deal with performance from a different aspect, and may need some effort and experiments to determine the benefits. Examples:

  1. String manipulation: I compared performance of native VBA functions with Excel's implementation of RegEx (using the same data) and native functions were faster for str replace, which was surprising because usually regex is supposed to be faster (I had the same revelation about recursion vs loops)

  2. Dictionaries are extremely useful and convenient when coding but are considerably slower than arrays, when loading large number of items. Once loaded though, accessing elements becomes instantaneous (like arrays) but provide other benefits such as filtering for unique values and determining if elements exist in the collection based on indexes, rather than having to iterate over each one

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy