Speed up processing between VBA and IE

Question

I am looking to speed up the exchange from vba to IE. The sendkeys works, but I was curious if there were a better way to do this?

The site that it routes to is a form, but there is no submit button. The only way to pull the data is to tab to the next box or click somewhere on the screen. I was hoping, however, to have all of this automated through VBA.

Thoughts?

Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long
Function FillInternetForm()
Dim HWNDSrc As Long
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
 'create new instance of IE. use reference to return current open IE if
 'you want to use open IE window. Easiest way I know of is via title bar.
HWNDSrc = ie.HWND
ie.Navigate "http://helppointinfo.farmersinsurance.com/OCR/Labor_Rates/laborrates.asp"
 'go to web page listed inside quotes
ie.Visible = True
While ie.Busy
 DoEvents 'wait until IE is done loading page.
 Wend
ie.Document.getElementById("DirectZip").Value = Sheets("NAT").Range("C2").Value
SetForegroundWindow HWNDSrc
Application.SendKeys "{TAB 11}", True
DoEvents
Application.SendKeys "{NUMLOCK}", True
End Function
Public Sub RunRates()
Call FillInternetForm
End Sub

• Mathieu Guindon: Hi, I've rolled back your edit. Please avoid editing your question to include suggested changes from answers.

Answer 1

Just reviewing what you've got here...

Indentation

The code would read much better with proper indentation:

Function FillInternetForm()
 Dim HWNDSrc As Long
 Dim ie As Object
 Set ie = CreateObject("InternetExplorer.Application")
 HWNDSrc = ie.HWND
 ie.Navigate "http://helppointinfo.farmersinsurance.com/OCR/Labor_Rates/laborrates.asp"
 ie.Visible = True
 While ie.Busy
 DoEvents 'wait until IE is done loading page.
 Wend
 ie.Document.getElementById("DirectZip").Value = Sheets("NAT").Range("C2").Value
 SetForegroundWindow HWNDSrc
 Application.SendKeys "{TAB 11}", True
 DoEvents
 Application.SendKeys "{NUMLOCK}", True
End Function
Public Sub RunRates()
 Call FillInternetForm
End Sub

Call Instruction

As answered in this StackOverflow question, the Call instrucation is a relic from ancient versions of VB, it's not needed and, IMO, only adds clutter.

Public Sub RunRates()
 FillInternetForm
End Sub

Coupling

The FillInternetForm function is needlessly coupled with the Excel object model - Sheets("NAT").Range("C2").Value should be passed as a String parameter to the function:

Function FillInternetForm(ByVal DirectZipValue As String)
 '...
 ie.Document.getElementById("DirectZip").Value = DirectZipValue
 '...
End Function
Public Sub RunRates()
 FillInternetForm Sheets("NAT").Range("C2").Value
End Sub

Function?

VB functions are procedures with a return value. If it's not specified, then it's returning a Variant - here FillInternetForm is never assigned a return value, and whatever it would be returning wouldn't be used. In other words, you have a procedure (Sub), not a function. The signature should be modified like this:

Public Sub FillInternetForm(ByVal DirectZipValue As String)

I like things explicit - if a member is going to be Private, it needs a Private access modifier; if it's going to be Public, I don't like relying on VB's "defaults", mostly because I code in different languages where these defaults differ (C#). Having explicit access modifiers eliminate the possible confusion, but that might be only me.

Lastly, I don't understand why FillInternetForm would have to press NUM LOCK, this looks misplaced, and has a side-effect that could be surprising to whoever is running that code.

• PlainsWind: Thank you for the response - haven't gotten a chance, as of yet, to take your suggestions, but wanted to answer the Num Lock question. I have it in place because when I run the Sendkeys it actually turns the Num Lock off. By having this code it place it will turn it back on.
• Mathieu Guindon: That would make a good reason to put a comment, so that call doesn't get inadvertantly removed by a future maintainer (could be future you!) - feel free to upvote any useful answers you get ;)
• PlainsWind: My overall plan with this code is to return the values from the webpage back into the excel sheet: 1. Wouldn't I need to leave it as a funciton as I am seeking to return a value? 2. Do you know of any articles that will return results from iframe?
• Mathieu Guindon: If FillInternetForm should return a value, then you're missing a line of code that would assign such a return value (FillInternetForm = TheReturnValue). You're not showing the code that's calling FillInternetForm, but if it's a procedure (sub) it will look like FillInternetForm and if it's a function it will look like Value = FillInternetForm. I don't know of any articles that return results from iframe (maybe Google does), but my initial thought was that if the form is on a .asp page you probably could get away with sending a simple HTTP request with your parameters, to the URL.
• PlainsWind: It's not part of the code shown bc it's not created yet. I first wanted to make sure sending the information was quick and accurate, then I was going to work on the return portion of it.