I’m making a VBA function which will translate text to another language by pulling the translated text from Google Translate using “getelementsByClassName”. I’m pulling the correct information, and aside from an error popping up, the code works. However, I’m running into
runtime error 91 – Obj var or with block var not set
once the code reaches
x = element.Item(0).innerText in the code below. Once I click debug and continue the script, the code does exactly as I expect, and assigns the correct translation as a string to the temporary variable I’ve used,
Anyone know why this error is popping up and why it doesn’t seem to matter once I continue the script? If there’s a different version of
application.DisplayAlerts that I can throw around this line to make the error not show up that would suffice as well.
I’ve just used the google translate webpage for translating “Hello World” to spanish for simplicity here.
Public Sub Translate() Dim page As String page = "https://translate.google.com/#view=home&op=translate&sl=en&tl=es&text=Hello%20world" Dim IE As New InternetExplorer IE.navigate page 'Wait til webpage is fully loaded Do While IE.readyState <> READYSTATE_COMPLETE Application.StatusBar = "Loading translation..." DoEvents Loop Dim element As IHTMLElementCollection Set element = IE.document.getElementsByClassName("result-shield-container tlid-copy-target") Dim x As String x = element.Item(0).innerText MsgBox x End Sub
Problem ended up being a timing issue where
getElementsByClassName was being run before IE was fully loaded. Tried adding
Or IE.Busy and every combination of
.readyState that I could think of while still coming into the problem. Using a
Sleep routine worked for a while, however successive iterations would run into the same error. Found a working method from one article which used the following method, which uses the first
while to ensure the next
until is not skipped.
Do While .readyState = 4: DoEvents: Loop Do Until .readyState = 4: DoEvents: Loop Do While .Busy: DoEvents: Loop Do Until .Busy: DoEvents: Loop