Takes in a list of language codes and a phrase to be translated through a google translate URL

translations.xlsm

'WARNING: Will open as many browser tabs as the length of the array, so it could take a while to load

'ShellExecute code from https://stackoverflow.com/questions/3166265/open-an-html-page-in-default-browser-with-vba
'Dynamic Array code from https://www.thespreadsheetguru.com/blog/dynamically-populating-array-vba-variables

Option Explicit

Private Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal Operation As String, _
  ByVal Filename As String, _
  Optional ByVal Parameters As String, _
  Optional ByVal Directory As String, _
  Optional ByVal WindowStyle As Long = vbMinimizedFocus _
  ) As Long

Public Sub OpenUrl()

    Dim myUrl As String
    Dim languageCode As String
    
    'PURPOSE: Dynamically Create Array Variable based on a Given Size
    Dim myArray() As Variant
    Dim DataRange As Range
    Dim cell As Range
    Dim x As Long
    Dim phraseToTranslate As String
    Dim formattedPhrase As String
    
    'Update phraseToTranslate to the phrase you want to translate
    phraseToTranslate = "Translate this phrase"
    formattedPhrase = Replace(phraseToTranslate, " ", "%20")

    'Determine the data you want stored
    Set DataRange = ActiveSheet.UsedRange
    
    'Loop through each cell in Range and store value in Array
    For Each cell In DataRange.Cells
        ReDim Preserve myArray(x)
        myArray(x) = cell.Value
        x = x + 1
    Next cell

    'Create a url for each item in the array and open that url in a new browser tab
    For x = LBound(myArray) To UBound(myArray)
        Debug.Print myArray(x)
    
        languageCode = myArray(x)
        myUrl = "https://translate.google.com/?sl#view=home&op=translate&sl=en&tl=" & languageCode & "&text=" & formattedPhrase
    
        Dim lSuccess As Long
        lSuccess = ShellExecute(0, "Open", myUrl)
    Next x

End Sub