I have this Excel VBA function that takes three parameters. It is essentially a translation mechanism with the following parameters:
- A sentence which has to be translated.
- A range of words to look for and what to translate them to.
- 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) Else 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 Next End Function